I have a table with an Invoice field that contains values and dates. There can be more than one invoice per record. If so, the invoices are listed separated by line breaks within a single cell. An example is shown below.
Example:
PO # PO Date PO Amt Invoice
1 05/19/15 100 100 06/01/15
500 07/1/15
1000 08/1/15
2 05/20/15 101 100 06/01/15
500 07/1/15
1000 08/1/15
3 05/21/15 102 100 06/01/15
500 07/1/15
1000 08/1/15
I want to split up these records that have more than one invoice listed. I would like to create new rows for each invoice. The new rows should have the same data in the other fields. Here's how the sample data should look when this is done
Results would be:
PO # PO Date PO Amt Invoice
1 05/19/15 100 100 06/01/15
1 05/19/15 100 500 07/1/15
1 05/19/15 100 1000 08/1/15
2 05/20/15 101 100 06/01/15
2 05/20/15 101 500 07/1/15
2 05/20/15 101 1000 08/1/15
3 05/21/15 102 100 06/01/15
3 05/21/15 102 500 07/1/15
3 05/21/15 102 1000 08/1/15
Doing this manually would take forever. How can I split these records this way in Excel?
Bookmarks