Forum Discussion
Solved
Find values from a table and transfer matches to another table
I need some help comparing 2 tables and pulling out the data I need.
Currently we have a table of cost codes and all the users who are allowed to use them, I want to switch that and have a list of users with the cost codes they are able to use.
Table 1 example (Source)
| Cost Code | Approved Users |
| 645A | worthington; badekar; king; fleming; |
| 138D | woods; fleming; |
| 230A | withers; badekar; |
| 150A | wise; |
Table 2 example (what it should like)
| User | Cost Codes |
| worthington | 645A |
| badekar | 645A; 230A |
| king | 645A |
| fleming | 645A; 138D |
| woods | 138D |
| withers | 230A |
| wise | 150A |
I've tried a variety of formulas but I keep getting #ERROR, #VALUE, #NAME, and #NA errors. Any help would be appreciated as I have approx. 540 cost codes and I'd rather not do them each individually..
The comment that I used and that worked just fine was deleted for whatever reason so I'll put it here.
OliverScheurich said=TEXTJOIN("; ",,FILTER($A$3:$A$6,ISNUMBER(SEARCH(A10,$B$3:$B$6))))
This formula finds and combines the cost codes for each user. The formula is filled down from cell C10 in the example.
=BYROW(A10:A16,LAMBDA(x,TEXTJOIN("; ",,FILTER(A3:A6,ISNUMBER(SEARCH(x,B3:B6))))))
With Office 365 or Excel for the web you can apply this formula which spills the results. This formula is in cell D10 in the example.
I used the first formula and it would perfectly, saved me a lot of time, thank you.
The comment that I used and that worked just fine was deleted for whatever reason so I'll put it here.
OliverScheurich said=TEXTJOIN("; ",,FILTER($A$3:$A$6,ISNUMBER(SEARCH(A10,$B$3:$B$6))))
This formula finds and combines the cost codes for each user. The formula is filled down from cell C10 in the example.
=BYROW(A10:A16,LAMBDA(x,TEXTJOIN("; ",,FILTER(A3:A6,ISNUMBER(SEARCH(x,B3:B6))))))
With Office 365 or Excel for the web you can apply this formula which spills the results. This formula is in cell D10 in the example.
I used the first formula and it would perfectly, saved me a lot of time, thank you.
JoeC-105 That would be something for Power Query (PQ). You familiar with that?
Attached file contains a PQ solution. Come back here if you need more help.
