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

Markalon

New Member
Joined
Aug 22, 2014
Messages
11
Hi all. Need some help here. What I have:


ModelOptionAB1234
FALSETRUEFALSETRUEFALSEFALSE
TRUETRUETRUEFALSEFALSETRUE
TRUEFALSEFALSEFALSETRUEFALSE
FALSETRUETRUETRUEFALSETRUE

<tbody>
</tbody>

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

ModelOptionAB1234
B2FALSETRUEFALSETRUEFALSEFALSE
A,B1,4TRUETRUETRUEFALSEFALSETRUE
A3TRUEFALSEFALSEFALSETRUEFALSE
B1,2,4FALSETRUETRUETRUEFALSETRUE

<tbody>
</tbody>


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

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 22, 2014
Messages
11
Try this:-
Code:
[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

New Member
Joined
Aug 22, 2014
Messages
11

ADVERTISEMENT

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

New Member
Joined
Aug 22, 2014
Messages
11
Perhaps I should be a little more specific:

What I have:


ModelOptionMod1
Mod2
Op1
Op2
Op3
Op4
FALSETRUEFALSETRUEFALSEFALSE
TRUETRUETRUEFALSEFALSETRUE
TRUEFALSEFALSEFALSETRUEFALSE
FALSETRUETRUETRUEFALSETRUE

<tbody>
</tbody>


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

ModelOptionMod1
Mod2
Op1
Op2
Op3
Op4
B2FALSETRUEFALSETRUEFALSEFALSE
A,B1,4TRUETRUETRUEFALSEFALSETRUE
A3TRUEFALSEFALSEFALSETRUEFALSE
B1,2,4FALSETRUETRUETRUEFALSETRUE

<tbody>
</tbody>



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

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
I think you need to use VBA in this case.
BTW MickG's code works well.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,542
Messages
5,529,453
Members
409,878
Latest member
DDhol
Top