I was using a vlookup to get data from an ID, but I have realised that I have duplicate ID's in the database.
I am wanting to merge duplicate row ID's within a database, but keeping all data contained for them.
I am expecting there to be up to a max of 5 duplicates, so repeating the Fruit/Animal/Colour columns up to 5 times to the right.
I also want to be able to keep the highest number in Column B that is attributed to that ID.
ID's are not in numerical order in the database, and contain both numbers and letters.
Please see the following example:
[TABLE="width: 1152"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Currently[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl67, width: 64"]Output[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Number[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD][/TD]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Number[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Bear[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Blue[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD][/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Bear[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
How would I go about doing this in Excel?
Thanks in advance.
I am wanting to merge duplicate row ID's within a database, but keeping all data contained for them.
I am expecting there to be up to a max of 5 duplicates, so repeating the Fruit/Animal/Colour columns up to 5 times to the right.
I also want to be able to keep the highest number in Column B that is attributed to that ID.
ID's are not in numerical order in the database, and contain both numbers and letters.
Please see the following example:
[TABLE="width: 1152"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Currently[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl67, width: 64"]Output[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Number[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD][/TD]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Number[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD="class: xl65"]Fruit[/TD]
[TD="class: xl65"]Animal[/TD]
[TD="class: xl65"]Colour[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Bear[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Blue[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD][/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Ant[/TD]
[TD="class: xl64"]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"]Bear[/TD]
[TD="class: xl64"]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Apple[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]Cherry[/TD]
[TD="class: xl64"]Cat[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]Banana[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
How would I go about doing this in Excel?
Thanks in advance.