CODEWITHSUNDEEP
excel
geoinfo
geoinfo

Reputation: 305

Multiple Columns into one Column in excel

I have 5 columns in my excel sheet which are : HopColourGreen,HopColourGreenYellow,HopColourGreenBlack,HopColourYellowBlack,HopColourBlack their values are true or false and i want to create a single column called colourwhich will contain for example Green if HopColourGreen is set to true.

Upvotes: 2

Views: 331

Answers (2)

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Using a combination of SUBSTITUTE, MATCH, and INDEX you can put the following in F and drag down.

=SUBSTITUTE(INDEX(A:E, 1, MATCH(TRUE, A2:E2, 0)), "HopColour", "")

HopColourGreen  HopColourGreenYellow    HopColourGreenBlack HopColourYellowBlack    HopColourBlack  
TRUE            FALSE                   FALSE               FALSE                   FALSE           =SUBSTITUTE(INDEX(A:E, 1, MATCH(TRUE, A2:E2, 0)), "HopColour", "")
FALSE           TRUE                    FALSE               FALSE                   FALSE           GreenYellow
FALSE           FALSE                   TRUE                FALSE                   FALSE           GreenBlack
FALSE           FALSE                   FALSE               TRUE                    FALSE           YellowBlack

You might need to change the MATCH(TRUE) to MATCH("TRUE") if they're text.

EDIT

There is no MATCH in french excel, you have to use EQUIV instead.

=SUBSTITUTE(INDEX(A:E; 1; EQUIV(TRUE; A2:E2; 0)); "HopColour"; "")

EDIT 2

And SUBSTITUTE is SUBSTITUE in french.

=SUBSTITUE(INDEX(A:E; 1; EQUIV(TRUE; A2:E2; 0)); "HopColour"; "")

Upvotes: 3

József Kökény
József Kökény

Reputation: 336

Try this:

=IF(A2=TRUE;"Green";IF(B2=TRUE;"GreenYellow";IF(C2=TRUE;"GreenBlack";IF(D2=TRUE;"YellowBlack";IF(E2=TRUE;"Black")))))

Upvotes: 2

Related Questions