flipping a spreadsheet

[remarque]

Hey folks,

My company's invoicing program outputs data to an Excel spreadsheet
with the following header row:

Line Item Type, Customer Name, Invoice #, Invoice Date, Amount

and then lists each row as a Line Item. Thus, each invoice will show
up in as many rows as the number of its line items and there's thus a
lot of duplicate data.

Our accountant would like us to provide her with the spreadsheet where
each invoice only shows up once, by row, with columns to break down the
Line Item Types. The header row for this spreadsheet would look like
this:

Invoice #, Invoice Date, Client Name, Line Item Type 1, Line Item Type
2, Line Item Type 3, ...

with the sum of each line item type, per invoice, in the corresponding
cell in each row.

Can you provide me with suggestions on how I can set up Excel to flip
the outputted spreadsheet into the desired one for the accountant?

Thanks!!

 

[Carim]

Hi,

Something along these lines ...

=SUMIF(A2:A100,"="&D2,E2:E100)

Ranges A2:A100 and E2:E100 are the ones located in Sheet1, whereas the
formula is located in sheet 2 cell D3 ...

HTH
Cheers
Carim

 

[Guest]

You would need to build a macro in VBA to accomplish this. You may want to
post this question to the programming discussion board.

Dave