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.
Printable View
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.
Why is HDFC Bank Ltd in table with BBB?
Because HDFC BANK LTD found and part of table BBB.
Actually I want such way formula to extract each stock categaory in all table and its track junior and senior analyst
Try like this:
Excel 2016 (Windows) 32 bit
=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
=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.
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
OK. So you can use this (put into module in vba):
and use instead of TEXTJOIN but parameters going in different order:Code: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
data first and then delimiter then true/false (in textjoin delimiter,true/false, data).Code:=TEXTJOINsub(IF($C$6:$C$31=I6,$B$6:$B$31,""),",",0)
so on Data:
J6:
L6:Code:=TEXTJOINsub(IF($C$6:$C$31=I6,$B$6:$B$31,""),",",0)
do not forget accept it with Ctrl+Shift+Enter.Code:=SUBSTITUTE(TEXTJOINsub(IF(($C$6:$C$31=I6),$D$6:$G$31,""),"/",0),"//","")
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
Happy to help.