Macro to transpose multiple rows into one row

Hello all,

I'm wondering if this is possible. Here is a sample of a large data set:
Capture.PNG

I want to transpose these multiple rows into one row per service, id, name, age, street address with multiple phone numbers like this:
Capture1.PNG

Thank you guys

Like this perhaps? To test run macro "GetData" but still as JLG said your data is inconsistent as some of the persons have two different service so should there not be two lines in that case?

Alf

Thanks for feedback and rep.

As I said before I find your data inconsistent and that the result from my macro matches your demand is caused by the fact that macro copies range B1:F18 three rows below your original data. It then deletes all duplicates in column C and D. So this means the first values in C and D are 110 and Jack are kept the rest of the data for 110 and Jack is deleted. Since the first row of this data contains service "Verizone" this is what is added to Jacks profile. If the service column was sorted alphabetically then "Sprint" would be the service assigned to Jack.

For Mary this does not matter as she only has one service provider but for John who has 3 "Verizone" and three "ATT" a sorting on service provider in alphbetical order would in this case assign ATT to John.

Alf

Originally Posted by Alf

Thanks for feedback and rep.

As I said before I find your data inconsistent and that the result from my macro matches your demand is caused by the fact that macro copies range B1:F18 three rows below your original data. It then deletes all duplicates in column C and D. So this means the first values in C and D are 110 and Jack are kept the rest of the data for 110 and Jack is deleted. Since the first row of this data contains service "Verizone" this is what is added to Jacks profile. If the service column was sorted alphabetically then "Sprint" would be the service assigned to Jack.

For Mary this does not matter as she only has one service provider but for John who has 3 "Verizone" and three "ATT" a sorting on service provider in alphbetical order would in this case assign ATT to John.

Alf

Sorry about that. I appreciate your help and thank you for explaining to me