How can I return multiple header values in a cell with non-blank criteria?

[nabilahms99]

[FONT=q_serif]Here is the table to help you understand the problem.

[/FONT][TABLE="width: 648"]
<tbody>[TR]
[TD="class: xl67, width: 72"]r1[/TD]
[TD="class: xl67, width: 72"]8B[/TD]
[TD="class: xl67, width: 72"]GA[/TD]
[TD="class: xl67, width: 72"]ID[/TD]
[TD="class: xl67, width: 72"]IL[/TD]
[TD="class: xl67, width: 72"]IN[/TD]
[TD="class: xl67, width: 72"]IW[/TD]
[TD="class: xl67, width: 72"]JT[/TD]
[TD="class: xl67, width: 72"]QG[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABU-KOE[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]AEG-KNO[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]AMQ-BXB[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]AMQ-CGK[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]35[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Routes[/TD]
[TD="class: xl67"]Competitor[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]ABU-KOE[/TD]
[TD="class: xl66"]IW[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]AMQ-CGK[/TD]
[TD="class: xl66"]GA, ID, JT, QG[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=q_serif]
What formula should I use to get the result like those in “Competitor” column?[/FONT]

 

[Nine Zero]

Hello,

Alright i think i went overboard on your solution lol but it works

First paste this code into a module in the VBA editor

Public Function Nabilah(route_name As String, route_search As Range, header_range As Range)
Dim r, q As Range
Dim route As String
Dim r_row As Integer
Nabilah = ""
r_row = Application.WorksheetFunction.Match(route_name, route_search, 0) + 1
Set r = header_range
For Each q In r
    If Not q.Offset(r_row - 1, 0) = "" Then
        Nabilah = Nabilah + "," + q.Text
    End If
Next
Nabilah = Right(Nabilah, Len(Nabilah) - 1)
End Function

then go to your cell where you want your result and type this

=Nabilah(A13,A2:A5,B1:I1)

This is assuming your cell with value "r1" is at cell A1 and your Routes and Competitor headers are in cells A12 and B12 respectively

these were my results

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ABU-KOE[/TD]
[TD]IW[/TD]
[/TR]
[TR]
[TD]AMQ-CGK[/TD]
[TD]GA,ID,JT,QG[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Hi!

Maybe the Formulas below can helps.

In C9 and copy down and to the right - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX($B$1:$I$1,SMALL(IF(INDEX($B$2:$I$5,MATCH($A9,$A$2:$A$5,0),)<>"",
COLUMN($B$1:$I$1)-COLUMN($B$1)+1),COLUMNS($C9:C9))),"")

In B9 and copy down - use Enter to entre the formula

=SUBSTITUTE(TRIM(C9&" "&D9&" "&E9&" "&F9&" "&G9&" "&H9&" "&I9&" "&J9)," ",", ")


[TABLE="class: grid, width: 834"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]r1[/TD]
[TD="align: center"]8B[/TD]
[TD="align: center"]GA[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]IL[/TD]
[TD="align: center"]IN[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"]JT[/TD]
[TD="align: center"]QG[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ABU-KOE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]AEG-KNO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]AMQ-BXB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]AMQ-CGK[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Routes[/TD]
[TD="align: center"]Competitor[/TD]
[TD="align: center"]HelpCol01[/TD]
[TD="align: center"]HelpCol02[/TD]
[TD="align: center"]HelpCol03[/TD]
[TD="align: center"]HelpCol04[/TD]
[TD="align: center"]HelpCol05[/TD]
[TD="align: center"]HelpCol06[/TD]
[TD="align: center"]HelpCol07[/TD]
[TD="align: center"]HelpCol08[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]ABU-KOE[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]AEG-KNO[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]AMQ-BXB[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"]IW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]AMQ-CGK[/TD]
[TD="align: center"]GA, ID, JT, QG[/TD]
[TD="align: center"]GA[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]JT[/TD]
[TD="align: center"]QG[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[nabilahms99]

Amazing! It works perfectly Thanks a lot for the code ))

 

[nabilahms99]

Amazing! It works perfectly. Thanks a lot for the code