Forum Discussion
Multistep: Lookup values based on another table and then concatenate by an ID
Hi Everyone, thank you in advance for any help you are able to provide! I consider myself good at excel and power query but this is quite a beast in my opinion.
Device and OS platform: PC, Windows 10
Excel product name and version number: Excel 2102
As much context of what you're trying to do - and why:
- (I added notes in the excel file as well that should be useful)
- The overall goal of this is to generate a series of key words at the ID level based off comparing the lookup table (name) to the descriptions within each Key_ID.
- I am trying to use a lookup table to identify certain words in a Description (1 description per row) and then in a new column (Results) have the "keyword" appear. Then In another new column (All Results per ID) concatenate all keywords together by key_ID from every row within that key_ID.
- Then finally, I want to take the All Results per Key_ID, and translate that into Type (same lookup table) summarized at a Key_ID level
Expected Result and Method:
- I believe Power Query or VBA is probably the best route to go, but if it can be done in normal excel that would also work. I had envisioned this as almost a conditional column in Power Query with a few tweaks but I have not had any success in that regard. An important point to note is that tables cannot be merged it won't align properly given the data I'm working with. I am not as familiar with VBA vs Excel & Power Query, so I have not explored that option.
Assuming you have Microsoft 365 (subscription version), you may use the following formulas for cells C3 to E3:
=TEXTJOIN(", ",TRUE,FILTER(J$3:J$6,ISNUMBER(SEARCH(J$3:J$6,B3)),"")) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C$3:C$8,A$3:A$8=A3,""))) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE),A$3:A$8=A3,"")))
5 Replies
Assuming you have Microsoft 365 (subscription version), you may use the following formulas for cells C3 to E3:
=TEXTJOIN(", ",TRUE,FILTER(J$3:J$6,ISNUMBER(SEARCH(J$3:J$6,B3)),"")) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C$3:C$8,A$3:A$8=A3,""))) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE),A$3:A$8=A3,"")))The suggested formulas return an empty string (looks like a blank) if the corresponding values in columns A or B are blank.
I can make the third formula return #N/A if your lookup table contains blank cells (not a good practice). If that's your problem, you can overcome it using:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(IFNA(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE) & "",""),A$3:A$8=A3,"")))