SOLVED

# Find values from a table and transfer matches to another table

Copper Contributor

# 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..

3 Replies

# Re: Find values from a table and transfer matches to another table

@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.

# Re: Find values from a table and transfer matches to another table

Alternative Power Query option attached

best response confirmed by JoeC-105 (Copper Contributor)
Solution

# Re: Find values from a table and transfer matches to another table

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.

1 best response

Accepted Solutions
best response confirmed by JoeC-105 (Copper Contributor)
Solution

# Re: Find values from a table and transfer matches to another table

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.