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

Multiple Zipcode of one city and one state to be clubbed

Copper Contributor

My excel file contains unique Zipcodes in column A and the corresponding city in column D and corresponding state in column F. I want to club all Zip codes of a particular city AND a particular state in one row. The zipcodes to be seperated by |

Experts please help me. 

ethi_nix_0-1706336576356.png

 

 

9 Replies
best response confirmed by ethi_nix (Copper Contributor)
Solution

Hi @ethi_nix 

 

Something like this (Power Query option attached)?

Sample.png

@ethi_nix 

=HSTACK(UNIQUE(B2:C11),BYROW(UNIQUE(B2:C11),LAMBDA(r,TEXTJOIN("|",,FILTER(A2:A11,(B2:B11=TAKE(r,1,1))*(C2:C11=TAKE(r,1,-1)))))))

 

I prefer @L z. 's solution however with Office 365 or Excel for the web you can do something like this.

zip code city state.png

@L z. 

Yes. Something like this. But the file contains other columns as well. I am sharing the sample file for you to check. 

Sample File 

OK, but which columns do you want in the output?
I want all the columns in the output. I have sent the full file to you via private message. Please check. Thanks

@ethi_nix 

 

I want all the columns in the output

OK, but given Lat & Long are unique per State & City (I didn't look at the other fields), your output will likely have the same #rows as your input. I guess this is not what you expect. Think about it and let me what should be done

 

I'm gonna be off from the remaining of the day...

I didn't foresee that. Let come back to you.
I have made some alterations at my end as such this would not be needed. Thanks anyway for your help.
OK, Thanks for keeping me posted
Cheers
1 best response

Accepted Solutions
best response confirmed by ethi_nix (Copper Contributor)
Solution

Hi @ethi_nix 

 

Something like this (Power Query option attached)?

Sample.png

View solution in original post