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

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.  Reply With Quote

2. Why is HDFC Bank Ltd in table with BBB?  Reply With Quote

3. Because HDFC BANK LTD found and part of table BBB.  Reply With Quote

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

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.  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

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  Reply With Quote

9. Happy to help.  Reply With Quote

#### Posting Permissions

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