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

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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’)
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...
Thank you. i just updated my account detail as suggested.
 
Upvote 0
alansidman said:
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
Click to expand...
I didn't think about using power query to do this. Thank you, I will try it out and get back to you
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...
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
 
Upvote 0
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)),""))
 
Upvote 0
Solution
MHamid said:
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
Click to expand...
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)),""))
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top