How to export data from a PivotTable to .csv in a specific format?

Question

I have a worksheet with 65000 rows. The first cell in each row is from a list of 1,500 values. For example:

PartNumber1|Application1
PartNumber1|Application2
PartNumber1|Application3
PartNumber2|application1
Partnumber2|Application2
PartNumber3|Application1
PartNumber3|Application2
PartNumber3|Application3

Etc...

I need to get an output like the following

PartNumber1,Application1,application2,application3
PartNumber2,Application1,Application2
PartNumber3,Application1,Application2,Application3 ....etc

Now, in Excel, I can create a PivotTable - but I want to have a CSV file to import into a database.

Can anyone point me in the right direction?

Answer 1

My hunch is PivotTables are irrelevant here (and that this is more "one off" than "routine") so suggest:

Work on a copy. Parse data assumed to be in ColumnA with Text to Columns and pipe as the delimiter. Insert a row at the top. In C2:

=IF(A1=A2,C1&","&B2,A2&","&B2)  

in D2:

 =A2<>A3  

Copy both formulae down to suit. Select all, Copy, Paste Special, Values. Filter to select FALSE in ColumnD. Delete all visible. Delete ColumnD, delete ColumnsA:B. Save result as .csv.