Very Very Very Very Complex excel formula required...

[amartakale]

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.

[Tom Jones]

Why is HDFC Bank Ltd in table with BBB?

[amartakale]

Because HDFC BANK LTD found and part of table BBB.

[amartakale]

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

[KOKOSEK]

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.

[amartakale]

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

[KOKOSEK]

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.

[amartakale]

Dear Sir,

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

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

[KOKOSEK]

Happy to help.