I am working with a spreadsheet with internal part numbers in the leftmost column, vendors as the headers, and the associated vendor part number in the corresponding cell. I think that makes sense the way I put it, but I think y'all get the idea. The problem is that a number of the internal part numbers are repeated because we get the item from multiple vendors, and the source for this data repeated them. My job is to take the repeats and collapse them into a single column.

For example:

Part No. | Vendor 1 | Vendor 2 | Vendor 3
ABC123 | MSN521 | |
ABC123 | | | VIN654
ABC124 | | NBC234 |
ABC125 | MSN522 | |
ABC125 | | NBC235 |

Becomes:

Part No. | Vendor 1 | Vendor 2 | Vendor 3
ABC123 | MSN521 | | VIN654
ABC124 | | NBC234 |
ABC125 | MSN522 | NBC235 |

*EDIT* I just discovered that my spacing doesn't display right in the thread. The pipes are supposed to delineate the columns, if that helps.

Right now, I'm trying to do it one at a time, but there are over 40,000 items in the database and it will take forever. Does anyone know of a way to automate this? I'm still a bit of an Excel greenhorn. Thanks!