Join Header Text into separate cells based on conditions of data below headers.

[Markalon]

Hi all. Need some help here. What I have:

Model	Option	A	B	1	2	3	4
		FALSE	TRUE	FALSE	TRUE	FALSE	FALSE
		TRUE	TRUE	TRUE	FALSE	FALSE	TRUE
		TRUE	FALSE	FALSE	FALSE	TRUE	FALSE
		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE

<tbody>
</tbody>

Need to comma delimit groupings of Models and Options based on True False condition. So, what I want:

Model	Option	A	B	1	2	3	4
B	2	FALSE	TRUE	FALSE	TRUE	FALSE	FALSE
A,B	1,4	TRUE	TRUE	TRUE	FALSE	FALSE	TRUE
A	3	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE
B	1,2,4	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE

<tbody>
</tbody>

I hope this is clear enough. Any help would be greatly appreciated. And thanks in advance.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG22Aug03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("c2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 2
        [COLOR="Navy"]If[/COLOR] Dn(, Ac) = True [COLOR="Navy"]Then[/COLOR] Str = Str & ", " & Cells(1, Ac + 2)
    [COLOR="Navy"]Next[/COLOR] Ac
        Dn.Offset(, -2).Value = Mid(Str, 2)
        Str = ""
    [COLOR="Navy"]For[/COLOR] Ac = 3 To 6
        [COLOR="Navy"]If[/COLOR] Dn(, Ac) = True [COLOR="Navy"]Then[/COLOR] Str = Str & ", " & Cells(1, Ac + 2)
    [COLOR="Navy"]Next[/COLOR] Ac
        Dn.Offset(, -1).Value = Mid(Str, 2)
        Str = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[anglais428]

For Model you could use the following formula (and fill down):
=IF(AND(TEXT(C2,0)="TRUE",TEXT(D2,0)="TRUE"),$C$1&","&$D$1,IF(AND(TEXT(C2,0)="true",TEXT(D2,0)="false"),$C$1,IF(AND(TEXT(C2,0)="false",TEXT(D2,0)="true"),$D$1,IF(AND(TEXT(C2,0)="false",TEXT(D2,0)="false"),""))))


For the Option column, it gets slightly more complicated but you can either apply the same methodology as above but add more steps or check out adding a UDF here .

 

[Markalon]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG22Aug03
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] Str [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("c2"), Range("C" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]For[/COLOR] Ac = 1 To 2
        [COLOR=Navy]If[/COLOR] Dn(, Ac) = True [COLOR=Navy]Then[/COLOR] Str = Str & ", " & Cells(1, Ac + 2)
    [COLOR=Navy]Next[/COLOR] Ac
        Dn.Offset(, -2).Value = Mid(Str, 2)
        Str = ""
    [COLOR=Navy]For[/COLOR] Ac = 3 To 6
        [COLOR=Navy]If[/COLOR] Dn(, Ac) = True [COLOR=Navy]Then[/COLOR] Str = Str & ", " & Cells(1, Ac + 2)
    [COLOR=Navy]Next[/COLOR] Ac
        Dn.Offset(, -1).Value = Mid(Str, 2)
        Str = ""
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Mick,

First, thank you for the reply. Second, I was thinking more along the lines of comparison formula(s). I'm not sure what to do with this code or how to use it. My apologies for my ignorance.

 

[Markalon]

For Model you could use the following formula (and fill down):
=IF(AND(TEXT(C2,0)="TRUE",TEXT(D2,0)="TRUE"),$C$1&","&$D$1,IF(AND(TEXT(C2,0)="true",TEXT(D2,0)="false"),$C$1,IF(AND(TEXT(C2,0)="false",TEXT(D2,0)="true"),$D$1,IF(AND(TEXT(C2,0)="false",TEXT(D2,0)="false"),""))))


For the Option column, it gets slightly more complicated but you can either apply the same methodology as above but add more steps or check out adding a UDF here .

This is fantastic, and what I was looking for, except for the fact that I kind of simplified my table a bit. For the models, I actually have 4 different mixes. For the options, I have about 30. Looks like this formula is developed with every combination possible, which as you said, would make it more complicated, especially for the 30+ options.

 

[Markalon]

Perhaps I should be a little more specific:

What I have:

Model	Option	Mod1	Mod2	Op1	Op2	Op3	Op4
		FALSE	TRUE	FALSE	TRUE	FALSE	FALSE
		TRUE	TRUE	TRUE	FALSE	FALSE	TRUE
		TRUE	FALSE	FALSE	FALSE	TRUE	FALSE
		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE

<tbody>
</tbody>

Need to comma delimit groupings of Models and Options based on True False condition. So, what I want:

Model	Option	Mod1	Mod2	Op1	Op2	Op3	Op4
B	2	FALSE	TRUE	FALSE	TRUE	FALSE	FALSE
A,B	1,4	TRUE	TRUE	TRUE	FALSE	FALSE	TRUE
A	3	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE
B	1,2,4	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE

<tbody>
</tbody>

I actually have 4+ models, and 30+ options in this table.

 

[anglais428]

I think you need to use VBA in this case.
BTW MickG's code works well.