SOLVED

# Multiple Zipcode of one city and one state to be clubbed

Copper Contributor

# Multiple Zipcode of one city and one state to be clubbed

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 |

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

# Re: Multiple Zipcode of one city and one state to be clubbed

Something like this (Power Query option attached)?

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

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

# Re: Multiple Zipcode of one city and one state to be clubbed

OK, Thanks for keeping me posted
Cheers
1 best response

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

# Re: Multiple Zipcode of one city and one state to be clubbed

Something like this (Power Query option attached)?