Forum Discussion

ethi_nix's avatar
ethi_nix
Copper Contributor
Jan 26, 2024
Solved

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 |

Experts please help me. 

 

 

excel
Macros and VBA

9 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 27, 2024

    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.

    • ethi_nix's avatar
      ethi_nix
      Copper Contributor
      Jan 27, 2024

      Lorenzo 

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

      https://1drv.ms/x/c/00e6adad09017b5a/EWE1KHvB67VHjoMGvWHwmqIBtNDU16ZfjP-UOqukH2X5nQ 

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 27, 2024
        OK, but which columns do you want in the output?