Lookup Formula to identify YES items and bring back uncoded header results

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

Not sure if the subject lines helped, but it was hard for me to specify what I need.
I am looking for an Excel formula (I know it will be a complicated formula) that will let me know the items identified as YES.

I have a sheet with accounts and within 18 columns I have a YES stating what fruit(s) is/are within the account. For evert YES found within the row, look at the column header and look at another table that has the text for each code. For instance, the table below has two accounts with a YES for the fruit associated with the account. So for account# 123456789, ORG, MNG, LMN, and PPA have a YES. For each of these, YES items, go to another table in another sheet and lookup the header (ORG, MNG, LMN, and PPA) ad bring back the actual text i want to see concatenated (Oranges, Mangoes, Lemons, Papaya) [in the Result Column].

Would anyone know a formula that can give me the results I am looking for in the Results column? I am looking for a formula, not a vba code.

Data:
Account #APPLORGBNSCHRSTRBLBBLRMNGGRFPNPAVCPMGCRBLMNWTMGRPGVAPPAResults
123456789​
NoYesNoNoNoNoNoYesNoNoNoNoNoYesNoNoNoYesOranges, Mangoes, Lemons, Papaya
987654321​
NoNoNoNoYesYesNoNoNoNoNoNoNoNoNoYesNoNoStrawberries, Blueberries, Grapes

Lookup Table in Sheet 2:
Code
APPLApples
ORGOranges
BNSBananas
CHRCherries
STRStrawberries
BLBBlueberries
BLRBlackberries
MNGMangoes
GRFGrapefruits
PNPPineapples
AVCAvocado
PMGPomegranate
CRBCranberries
LMNLemons
WTMWatermelon
GRPGrapes
GVAGuava
PPAPapaya

Thank you,
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?

I suggest that you update your Account details (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’)
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,184
Office Version
  1. 365
Platform
  1. Windows
With Power Query, I created three queries to get the result you are looking for.
1. Import your first table to the PQ Editor. Here is the Mcode for that
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account #"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"
2. Import your second table to the PQ Editor. Mcode
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content]
in
    Source
3. Join the two tables and then pivot the data
l
Power Query:
et
    Source = Table.NestedJoin(Table1, {"Attribute"}, Table3, {"Code"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Column1"}, {"Table3.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table3",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Account #"}, {{"DATA", each _, type table [#"Account #"=text, Table3.Column1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Account #", "Table3.Column1", "Index"}, {"Custom.Account #", "Custom.Table3.Column1", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"DATA", "Custom.Account #"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Table3.Column1")
in
    #"Pivoted Column"

End Result

Book5
ABCDE
1Account #1234
2123456789OrangesMangoesLemonsPapaya
3987654321StrawberriesBlueberriesGrapes
Merge1
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
What version of Excel are you using?

I suggest that you update your Account details (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
Thank you. i just updated my account detail as suggested.
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

With Power Query, I created three queries to get the result you are looking for.
1. Import your first table to the PQ Editor. Here is the Mcode for that
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account #"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"
2. Import your second table to the PQ Editor. Mcode
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content]
in
    Source
3. Join the two tables and then pivot the data
l
Power Query:
et
    Source = Table.NestedJoin(Table1, {"Attribute"}, Table3, {"Code"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Column1"}, {"Table3.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table3",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Account #"}, {{"DATA", each _, type table [#"Account #"=text, Table3.Column1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Account #", "Table3.Column1", "Index"}, {"Custom.Account #", "Custom.Table3.Column1", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"DATA", "Custom.Account #"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Table3.Column1")
in
    #"Pivoted Column"

End Result

Book5
ABCDE
1Account #1234
2123456789OrangesMangoesLemonsPapaya
3987654321StrawberriesBlueberriesGrapes
Merge1
I didn't think about using power query to do this. Thank you, I will try it out and get back to you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1Account #APPLORGBNSCHRSTRBLBBLRMNGGRFPNPAVCPMGCRBLMNWTMGRPGVAPPAResults
2123456789NoYesNoNoNoNoNoYesNoNoNoNoNoYesNoNoNoYesOranges, Mangoes, Lemons, Papaya
3987654321NoNoNoNoYesYesNoNoNoNoNoNoNoNoNoYesNoNoStrawberries, Blueberries, Grapes
Data
Cell Formulas
RangeFormula
T2:T3T2=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0))))


+Fluff 1.xlsm
AB
1Code
2APPLApples
3ORGOranges
4BNSBananas
5CHRCherries
6STRStrawberries
7BLBBlueberries
8BLRBlackberries
9MNGMangoes
10GRFGrapefruits
11PNPPineapples
12AVCAvocado
13PMGPomegranate
14CRBCranberries
15LMNLemons
16WTMWatermelon
17GRPGrapes
18GVAGuava
19PPAPapaya
Sheet2
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1Account #APPLORGBNSCHRSTRBLBBLRMNGGRFPNPAVCPMGCRBLMNWTMGRPGVAPPAResults
2123456789NoYesNoNoNoNoNoYesNoNoNoNoNoYesNoNoNoYesOranges, Mangoes, Lemons, Papaya
3987654321NoNoNoNoYesYesNoNoNoNoNoNoNoNoNoYesNoNoStrawberries, Blueberries, Grapes
Data
Cell Formulas
RangeFormula
T2:T3T2=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0))))


+Fluff 1.xlsm
AB
1Code
2APPLApples
3ORGOranges
4BNSBananas
5CHRCherries
6STRStrawberries
7BLBBlueberries
8BLRBlackberries
9MNGMangoes
10GRFGrapefruits
11PNPPineapples
12AVCAvocado
13PMGPomegranate
14CRBCranberries
15LMNLemons
16WTMWatermelon
17GRPGrapes
18GVAGuava
19PPAPapaya
Sheet2
Hi Fluff,

I tried this and I am not getting a #CALC! error for "Empty arrays". The columns that have the YES values are from a formula. So there are formulas in each column that result into a Yes or No. Would that make a difference in your formula? Also not all rows will have a YES.


Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
The #CALC error means that the filter didn't return anything, you can handle that like
Excel Formula:
=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0)),""))
 
Solution

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi Fluff,

I tried this and I am not getting a #CALC! error for "Empty arrays". The columns that have the YES values are from a formula. So there are formulas in each column that result into a Yes or No. Would that make a difference in your formula? Also not all rows will have a YES.


Thank you
Never mind, I was referencing the wrong header. However, I still got the #CALC! error and that is for those that did not have a YES. So I have updated the formula as shown below and it works perfectly. Thank you!!

Excel Formula:
=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0)),""))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,370
Messages
5,635,864
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top