Transpose question

[rbac]

I have a large spreadsheet that is setup like this more or less:

cityA client1 email
cityB client1 email
cityA client2 email
cityD client3 email
cityT client3 email
cityH client3 email

There are multiple clients, but some clients have multiple listings, one for each city. I need to somehow make it look like this:

Client1 email CityA CityB
Client2 email CityA
Client3 emal CidtyD CityT CityH

I was thinking some sort of filter and transpose... but i can't figure out how to do it.

Thanks!

[newdoverman]

Depending upon the use to which you are putting your data, the way that you have your data at present is probably the best organization to have especially if there is more data for each record than is shown.

[rbac]

It is not the best way to organize it. I need it in the format I specified for a mail merge, as I do not want to send the same person an email for each city, instead send each unique client 1 email and in the email list all the cities they are in.

I am trying to figure this out... I am thinking maybe some sort of if/or type of vlookup formula or something.

[newdoverman]

Are you wanting to send to one location per client or to all locations per client? If just one location, what is your criteria for choosing?

[rbac]

For each client, i need to list each city inside the email, which is why i want each unique client on its own row, so that I can properly run a mailmerge in Word.

[rbac]

Here is an example of before/after.

Transpose Question.xlsx

[newdoverman]

Thank you for the clarification.

[SamCV]

Clients.xlsxHello rbac...

Have ever try Pivot Tables?? They of big help in cases like this.
I had attached a sample for your review. If you find it useful, excellent. If "No", we can think in something else.

Best regards!!

SamCV.

[rbac]

Attachment 283144Hello rbac...

Have ever try Pivot Tables?? They of big help in cases like this.
I had attached a sample for your review. If you find it useful, excellent. If "No", we can think in something else.

Best regards!!

SamCV.

This is really close... but in Word mailmerge, each unique field must be on the same row as the email it is sending it to.. the cities in the pivot table are not on the same row, otherwise it would work perfect.

The best idea i have so far is to use a combination of unique finding if/or vlookup formula, but this seems to be rather innefctive when i have to do 6000+ rows of this.

Thanks!

[AlKey]

Please see attached file with a solution done by formulas.

[rbac]

Please see attached file with a solution done by formulas.

Perfect, this is exactly the formula cleverness I was trying to figure out.

Thanks a ton!

[AlKey]

You're welcome and thanks for the feedback!