Formula selection: a real tough one

[David Lipetz]

Folks,

This newsgroup has been extremely helpful over the years and I am thankful
to all whom have helped me in the past. I now have a scenario that I can not
even begin to solve and hope that someone can send me in the right
direction.

Our ERP system can export transactional data. I would like to be able to
analyze the data on an invoice by invoice basis to determine metrics such as
average total invoice amount for invoices that include a specific part
number.

The ERP data extract looks something like this:

CustNo Date InvNumber ItemNo QTY UnitPr ExtPr UnitCo
ExtCo
2301 03/12/08 100101 90873 3 5.00 15.00
3.00 9.00
9911 03/12/08 100102 72FG6 1 75.00 75.00 42.00
42.00
1090 03/12/08 100103 SYSTM 1 500.00 500.00 250.00
250.00
1090 03/12/08 100103 AR987 1 20.00 20.00 10.00
10.00
1090 03/12/08 100103 AR990 1 50.00 50.00 29.00
29.00
1090 03/12/08 100103 SW321 1 100.00 100.00 65.00
65.00
3125 03/12/08 100104 ACC01 10 5.00 50.00 3.00
30.00
7865 03/12/08 100105 ACC12 2 19.00 38.00 11.00
22.00
2029 03/12/08 100106 SYSTM 1 500.00 500.00 250.00
250.00
2029 03/12/08 100106 AR987 1 20.00 20.00 10.00
10.00


Assume that there are thousands of records in every extract and that the
column headers are also the named ranges.

I would like a formula that would identify every "InvNumber" for a specific
"ItemNo", then be able to provide a total sum for that invoice as well as
the "CustNo".

Using the data set above, if the "ItemNo" criteria were set to "SYSTM", the
result of the formula would be:

CustNo Date InvNumber ExtPr ExtCo
1090 03/12/08 100103 670.00 354.00
2029 03/12/08 100106 520.00 260.00

Completely at a loss here.

Thanks in advance,

David

 

[Roger Govier]

Hi David

Create a Pivot Table
Place cursor in Header row>Data>Pivot Table>Finish
On the PT skeleton that appears on the new page
Drag Item No to Page area
Drag Cust No to Row area
Drag Inv Number to Row Area
Drag Date to Row Area
Drag Ext Pr to Data Area
Drag Ext Co to Data Area

Drag the Data button and drop on Total to get the two data values side by
side.
Use the dropdown on Item No to select SYSTM

For more information on using Pivot Tables, take a look at Debra Dalgleish's
site and scroll to Pivot Tables
http://www.contextures.com/tiptech.html
or Mike Alexander's site at
http://www.datapigtechnologies.com/ExcelMain.htm

 

[David Lipetz]

Thank you for the suggestion. I will check this out.

I was hoping for a programmatic way of getting this done as I'd like to be
able to handle over a template in which the data extracts can be pasted in
to and the formulas just do there thing. Asking users to create a Pivot
Table may be asking for too much.

 

[JP]

You could try recording a macro of yourself following the steps Roger
posted.


HTH,
JP

 

[GerryGerry]

Once SYSTM is selected, it filters and totals for that item only messing up
the ExtPr and ExtCo totals for the given invNumber

 

[Roger Govier]

But that is the whole point about a Page Filter.
The totals will be just for that Item.

 

[GerryGerry]

I understand that, but what I was pointing out was that it doesn't give the
result the original poster required.

 

[Roger Govier]

Perhaps I am being extremely dense here - not an uncommon occurrence - but
when I took the OP's data and Pivoted it in the manner I described, I
obtained the result layout he was seeking.

 

[Pete_UK]

The following describes a formula-based solution, which will give you
what you asked for:

Assuming your data with header occupies cells A1 to i11, and that you
have the summary table headers on row 20 in columns A to E, this gives
you space to select the itemNo in A18.

Insert a new column A, and put this formula in A2:

=E2&"_"&COUNTIF(E$2:E2,E2)

and copy this down to A11 - this will give you a sequential number
tagged on to each ItemNo.

Then put this formula in A21:

=IF(ISNA(MATCH(B$18&"_"&ROW(A1),A$2:A$11,0)),"",MATCH(B
$18&"_"&ROW(A1),A$2:A$11,0))

You can copy this down for as many rows as you think you may need - I
copied it to A29. The next formula goes into B21:

=IF($A21="","",INDEX(B$2:B$11,$A21))

and this can also be copied into C2121. A slightly different formula
is needed for the next two cells, as follows:

E21: =IF($A21="","",INDEX(H$2:H$11,$A21))
F21: =IF($A21="","",INDEX(J$2:J$11,$A21))

and now the cells B21:F21 can be copied down for as many rows as you
think you need to cater for duplicates (eg to row 29). These cells
will all appear blank, so to finish it off put "Item Num:" as a label
in A18, set the background colour of B18 to bright yellow, and put
SYSTM in B18 - immediately the appropriate data appears in rows 21 and
22. Change B18 to a different ItemNo, and the display changes again.

I think this is what you wanted. The summary table could be on a
different sheet - Insert a new sheet and then cut/paste the summary
block from A18:F29 into the new sheet. Obviously, wherever ranges go
to $11, you should change these to suit the amount of data that you
have. If you do move the formula to a different sheet, then you could
make these into full-column references so that you don't need to
bother with row numbers.

I'm not sure where the ExtPr and ExtCo values come from in your
example.

Hope this helps.

Pete

 

[GerryGerry]

It's probably me being dense, but although I got the correct layout, the
totals were different, (as it only totaled the product selected but not for
the entire invoice) i.e I got the following:

CustNo Date InvNumber ExtPr ExtCo
1090 03/12/08 100103 500.00 250.00
2029 03/12/08 100106 500.00 250.00

Which does not match the OP's desired table.

BTW I'm using Excel 2007

 

[Pete_UK]

Having seen GerryGerry's latest post, I now understand where your
figures come from for ExtPr and ExtCo in your example - they are the
sum for each invoice. You can obtain these values by modifying these
formulae:

E21: =IF($A21="","",SUMIF($D$2:$D$11,D21,H$2:H$11))

F21: =IF($A21="","",SUMIF($D$2:$D$11,D21,J$2:J$11))

Copy these down to row 29.

This now gives you exactly what you asked for in your opening post.

Hope this helps.

Pete

 

[Roger Govier]

Hi Gerry

No, its definitely not you.
I misread the OP saying he wanted the Invoice TOTAL, where Item SYSTM was
included.
I read it as him wanting the value for SYSTM

Mea Culpa