Preferential Total 2

[PeanutB7]

Hello Folks,

I have a very large spreadsheet (139,000 rows cut into multiple worksheets) with columns of data that includes various line item costs and a corresponding ID number. The ID number repeats itself providing multiple line items per each ID number. This would be similar to multiple line items identified and grouped by a single invoice number. I have subtotaled the line items based on the invoice numbers using Micro Soft 2007 subtotal function which took a while but worked great.

The ID numbers (invoice numbers)are 12 digits and can indicate later adjustments by the subtitution of the last digit for a 1,2, 3 or 4 depending on how many times the invoice has been adjusted.

123456789000 - Original invoice (presently subtotaled)
123456789001 - First adjustment to original invoice
123456789002 - Second adjustment to original invoice
123456789003 - Third adjustment to original invoice
123456789004 - Fourth adjustment to original invoice.

What I would like to do is obtain all of the subtotals that incorporate the most recent adjustment. In other words if 123455678900 was revised then my grand total should ignore 12345678900 and take the subtotal from 12345678901 or 02, or 03, or 04. This would depend on how many revisions were made to the original line item subtotal. In this respect I would be looking at the ultimate true total of all invoices incorporating all subsequent adjustments and revisions to date.

Is there a formula or function that would help me with this requirement.

I thank you in advance and appreciate the effort.

John B

 

[xlhelp]

I would create another column and if the numbers above start in cell A2, then the formula in the new column would be =if(left(A2,11)=left(A3,11),"",A2)

this way only the last revison would show up in the new column. Then you would filter and subtotal etc.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[xlhelp]

I re-read your post. Instead of 11 make it 10.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[PeanutB7]

xlhelp,

I do not how and/or why but it is working like a champ. Thank you soooooo much for the needed assistance.

Greatly appreciated,

JB

 

[macropod]

Hi PeanutB7,

You can do the same thing without a 'helper' column, using an array formula (input with Ctrl-Shift-Enter)like:
=SUM(IF(LEFT(A1:A5,LEN(A1:A5)-1)=LEFT(A1,LEN(A1)-1),B1:B5)-IF(A1:A5=A1,B1:B5))
where the invoice you want to exclude (ie the one ending in '000') is in the cell referenced by the last 'A1' in the formula.


Cheers
[MS MVP - Word]

 

[xlhelp]

macropod,

my few grey cells cannot fathom how you can get away without going to another column, helper or not. And I thought I was pretty good at grasping things, but for the life of me I cannot understand the formula.


Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[macropod]

Hi xlhelp,

Basically, what the formula does is to sum all the cells in column B where the corresponding cells in column A match in all except the last character. Then, since the OP didn't want the cell ending in 000 counted, the formula simply deducts the value of that cell's column B neighbour from the total.

Oh, and I oversimplified the description of how to reference the invoice you want to exclude. The references in bold are the ones that reference that invoice:
=SUM(IF(LEFT(A1:A5,LEN(A1:A5)-1)=LEFT(A1,LEN(A1)-1),B1:B5)-IF(A1:A5=A1,B1:B5))


Cheers
[MS MVP - Word]