Displaying multiple values in 1 cell???

[killar65]

Hey guys,

Need help with a formula...

I have a small table that shows people names (col A) and liked colors (row 1)...see attached pic

What I'm seeking to do is in col H, show what each person likes color wise...ie...H2 would show red, green, cyan for Frank...H3 would show yellow, blue for Sally etc...

If somebody had no favourite color, then the respective cell in col H would remain blank.

Col's B2 through G5 are in a font style called marlett...pressing "a" on your keyboard produces the tick.

Any helps/thoughts on this would be greatly appreciatted...

Regards,

James

 

[jolivanes]

With a simple loop

Sub Maybe()
Dim lr As Long, lc As Long, i As Long, j As Long, clrs
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To lr
        For j = 2 To lc
            If Cells(i, j).Value = "a" Then clrs = clrs & ", " & Cells(1, j).Value
        Next j
    Cells(i, lc + 1).Value = Mid(clrs, 3)
    clrs = ""
    Next i
End Sub

 

[eiloken]

Hey guys,

Need help with a formula...

I have a small table that shows people names (col A) and liked colors (row 1)...see attached pic
View attachment 103019

What I'm seeking to do is in col H, show what each person likes color wise...ie...H2 would show red, green, cyan for Frank...H3 would show yellow, blue for Sally etc...

If somebody had no favourite color, then the respective cell in col H would remain blank.

Col's B2 through G5 are in a font style called marlett...pressing "a" on your keyboard produces the tick.

Any helps/thoughts on this would be greatly appreciatted...

Regards,

James

you can do it with formula:

H2=TEXTJOIN(", ",TRUE,IF(B2:G2="a",$B$1:$G$1,""))

 

[killar65]

Jolivanes, Eiloken...

Many thanks for taking the time help out...

Going to go with Eiloken's suggestion as it is the easiest for me to modify and implement...works a treat...

Once again, thanks

James

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)