CODEWITHSUNDEEP
excelexcel-formulapivot-tableexport-to-csvworksheet-function
Bevan
Bevan

Reputation: 584

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

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?

Upvotes: 1

Views: 6359

Answers (1)

pnuts
pnuts

Reputation: 59475

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.

Upvotes: 5

Related Questions