deleting double numbers after shifting data

Hi,
I'm sure this may be answered somewhere else already, but a good search around didn't lead me to an obvious answer.

I have data that has double numbers in column A, but has the information associated with those same double numbers in different columns. I want to create a row where all the information associated with a unique number is in the same row, and I can then delete the double number

I have:

A B C
1 red blue
2 red green
3 blue red
4 blue red

A D E
1 mary pat
2 mary bob
3 mary bill
5 bill pat



I want the end result to be:

A B C D E
1 red blue mary pat
2 red green mary bob
3 blue red mary bill
4 blue red (populating column B & C)
5 bill pat (populating column D & E)


I expect I'm looking for a formula that looks something like:

If a number in column 1 = another number in column 1, then take the data from the second occurance and populate the first occurance with it (and if possible within the same formula - delete the second occurance)

Any hope that there's a formula out there that will do this? I'll be working with thousands of records, so am looking for a way to reduce as much manual editing/deleting as possible.

Greta