Consulting

Results 1 to 9 of 9

Thread: Very Very Very Very Complex excel formula required...

  1. #1

    Very Very Very Very Complex excel formula required...

    Pls see attachment with detailed.I think its is very complex formula.can it possible? I hope some one excel expert help & guide me on this topic.
    Attached Files Attached Files

  2. #2
    Why is HDFC Bank Ltd in table with BBB?

  3. #3
    Because HDFC BANK LTD found and part of table BBB.

  4. #4
    Actually I want such way formula to extract each stock categaory in all table and its track junior and senior analyst

  5. #5
    Try like this:
    Excel 2016 (Windows) 32 bit
    J
    K
    L
    6
    =TEXTJOIN(",",1,IF($C$6:$C$31=I6,$B$6:$B$31,"")) =VLOOKUP(I6,C: D,2,0) =SUBSTITUTE(TEXTJOIN("/",1,IF(($C$6:$C$31=I6),$D$6:$G$31,"")),"//","")
    Sheet: Data

    J & L are array formulas so accept with Ctrl+Shift+Enter and drag it down.

    Excel 2016 (Windows) 32 bit
    D
    E
    F
    G
    8
    =INDEX(Data!C:C,SMALL(IF(Data!$B$6:$B$31='Final Output'!$B$4,ROW(Data!$C$6:$C$31),10^9),ROW()-7)) =VLOOKUP($D8,Data!$I:$L,(COLUMN()-3),0) =VLOOKUP($D8,Data!$I:$L,(COLUMN()-3),0) =VLOOKUP($D8,Data!$I:$L,(COLUMN()-3),0)
    Sheet: Final Output

    Formula in D is array so as above (CSE) to accept and drag it down.
    Attached Files Attached Files

  6. #6
    Dear Sir

    Superb.........

    But I am using Excel 2013 so ,can you suggest me excel 2013 text join formula.

    Second index formula also great its work for me but fail if text join not showing answer because of excel 2016 And what meaning of "10^9" in text join formula.

    Sir , I would be exciting to see answer with 2013 excel formula.

    Regards
    Amar Takale

  7. #7
    OK. So you can use this (put into module in vba):

    Public Function textjoinSUB(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
    'Created by TigerAvatar at www.excelforum.com, September 2012
    'Purpose is to concatenate many strings into a single string
    'Can be used with arrays, range objects, and collections
        
        Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
        Dim strResult As String     'Used to build the result string
        
        'Test if varData is an Array, Range, or Collection
        If IsArray(varData) _
        Or TypeOf varData Is Range _
        Or TypeOf varData Is Collection Then
            
            'Found to be an, array, range object, or collection
            'Loop through each item in varData
            For Each DataIndex In varData
                'Check if the item isn't empty
                If Len(DataIndex) > 0 Then
                    'Found the item isn't empty, check if user specified bUnique as True
                    If bUnique = True Then
                        'bUnique is true, check if the item has been included in the result yet
                        If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                            'Item has not been included in the result, add item to the result
                            strResult = strResult & "||" & DataIndex
                        End If
                    Else
                        'bUnique is not true, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                End If
            Next DataIndex
            
            'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
            strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
            
        Else
            'Found not to be an array, range object, or collection
            'Simply set the result = varData
            strResult = varData
        End If
        
        'Output result
        textjoinSUB = strResult
        
    End Function
    and use instead of TEXTJOIN but parameters going in different order:

    =TEXTJOINsub(IF($C$6:$C$31=I6,$B$6:$B$31,""),",",0)
    data first and then delimiter then true/false (in textjoin delimiter,true/false, data).

    so on Data:

    J6:
    =TEXTJOINsub(IF($C$6:$C$31=I6,$B$6:$B$31,""),",",0)
    L6:
    =SUBSTITUTE(TEXTJOINsub(IF(($C$6:$C$31=I6),$D$6:$G$31,""),"/",0),"//","")
    do not forget accept it with Ctrl+Shift+Enter.

  8. #8
    Dear Sir,

    Great ,Hats off
    Wow.... amazing,sir you dont know how much time saving from this vba & formula

    Really Appreciated your effort.Thanks so much sir.

    ( I am vba learner so I am afraid can it possible for me to change code.if get formula then there are no problem in future.but now I mange this your superb work.If any query in future I will asked you)

    Thanks & Regards
    Amar Takale

  9. #9
    Happy to help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •