I am trying to consolidate information in a database that is not very user friendly.
Column A contains a list if invoice numbers. Column B is the name of the Vendor. Column C is the Sales Rep ID Number.
The problem I am having is each invoice is listed in one or more line items (which are dependent on the number of reps on the invoice in question). I am trying to create a separate sheet that will list each invoice only once but list every sales rep assigned to the invoice in separate columns.
For example, invoice A might be listed 5 in five line items with 5 different rep numbers
Invoice ID Rep Number
A Rep_474
A Rep_66
A Rep_9
A Rep_1
A Rep_5668
I would like the data to be listed as follows
Invoice ID Rep Number1 ..Rep Number2 ..Rep Number3 ..Rep Number4 ..Rep Number5
A Rep_474 .Rep_66 Rep_9 ..Rep_1 .Rep_5668
Note: In some cases, a rep may be listed more than once on the same invoice, such as
Invoice ID Rep Number
A Rep_474
A Rep_474
A Rep_9
A Rep_9
A Rep_9
In this case, the output would be as follows:
Invoice ID Rep Number1 ..Rep Number2
A Rep_474 .Rep_9
I have attached a small sample of raw data and desired output. Thanks in advance for any comments.
Bookmarks