Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

Find values from a table and transfer matches to another table

Copper Contributor

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 CodeApproved Users
645Aworthington; badekar; king; fleming; 
138Dwoods; fleming; 
230Awithers; badekar; 
150Awise; 

 

Table 2 example (what it should like)

UserCost Codes
worthington645A
badekar645A; 230A
king645A
fleming645A; 138D
woods138D
withers230A
wise150A

 

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

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

@JoeC-105 

 

Alternative Power Query option attached

Sample.png

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

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

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. 

View solution in original post