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:
<tbody>
</tbody>
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:
Currently | Output | ||||||||||||||||
ID | Number | Fruit | Animal | Colour | ID | Number | Fruit | Animal | Colour | Fruit | Animal | Colour | Fruit | Animal | Colour | ||
1 | 1 | Apple | 1 | 10 | Apple | Banana | Bear | Red | |||||||||
2 | 1 | 2 | 10 | Cherry | Ant | Blue | Apple | Cat | |||||||||
3 | 1 | Cherry | 3 | 2 | Cherry | Banana | |||||||||||
4 | 10 | Banana | Ant | Red | 4 | 10 | Banana | Ant | Red | Cherry | Cat | ||||||
5 | 10 | Apple | Ant | Yellow | 5 | 10 | Apple | Ant | Yellow | Banana | |||||||
1 | 2 | ||||||||||||||||
2 | 10 | Cherry | Ant | Blue | |||||||||||||
3 | 2 | Banana | |||||||||||||||
4 | 3 | ||||||||||||||||
5 | 1 | ||||||||||||||||
1 | 10 | Banana | Bear | Red | |||||||||||||
2 | 2 | Apple | Cat | ||||||||||||||
3 | 1 | ||||||||||||||||
4 | 3 | Cherry | Cat | ||||||||||||||
5 | 4 | Banana |
<tbody>
</tbody>
How would I go about doing this in Excel?
Thanks in advance.