Multiple Zipcode of one city and one state to be clubbed

[ethi_nix]

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. 

[#2 · Accepted solution]

Hi @ethi_nix 

Something like this (Power Query option attached)?

[#3]

@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 @Lorenzo 's solution however with Office 365 or Excel for the web you can do something like this.

[#4]

@Lorenzo 

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

Sample File 

[#5]

OK, but which columns do you want in the output?

[#6]

I want all the columns in the output. I have sent the full file to you via private message. Please check. Thanks

[#7]

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

[#8]

I didn't foresee that. Let come back to you.

[#9]

I have made some alterations at my end as such this would not be needed. Thanks anyway for your help.

[#10]

OK, Thanks for keeping me posted
Cheers