Aged Analysis Of Stock

[Marcster]

Hello people ,
I have some data which I can get into Excel 2000.
Starting in cell A1 on Sheet1:

Post date Reference Value
01/12/2004 dec0400 458.59
30/03/2005 N156236 458.59
30/03/2005 N156236 18255.15
31/07/2005 veh jnl -458.59
31/07/2005 veh jnl2 458.59
09/09/2005 N156184 458.59
09/09/2005 N156184 32070.81
01/10/2005 N156716 21085.62
01/10/2005 N156716 1362.37
01/10/2005 N156716 458.59
04/11/2005 N156785 458.59
04/11/2005 N156785 35175.49
01/12/2005 N156852 458.59
01/12/2005 N156852 18560.52
01/12/2005 N156852 -17789.28

The data will vary in length each time it gets imported.

What I'm after is a macro which will populate the Analysis worksheet.
On sheet Analysis there's a breakdown showing how long items have been
in stock for, along with the Values.
So by looking at Analysis you'll see 2 items with a total of ?55,435.98 under 91 -120 Days
and 1 item with a total of ?18,713.74 under 180+ Days

The text on the Analysis sheet is already set-up. Like a template.
Just the cells in green need to be filled in according to the data above.

I haven't started on any VBA code for this yet as not sure where
the best place to start should be.
I thought a macro to:
Sort by reference, then by date
Delete all rows in Reference column which does NOT start with an 'N'
Add all values with same reference
Count how many unique items there are based on Reference column
Find out how many days between today and Post date.
Then somehow populate the Analysis worksheet

Don't know what the best or easist way to go about this.
Any suggestions?.
I've uploaded a sample workbook.

Thanks In Advanvce for any help on this ,

Marcster.

[Bob Phillips]

How about formulae

[OBP]

xld, that is some formula, I would have succumbed to using VBA.

[Bob Phillips]

xld, that is some formula, I would have succumbed to using VBA.

I tried to avoid VBA if I can do it with formulae. Somehow that feels right to me in Excel.

[Marcster]

Thank you SO MUCH XLD!
Awsome formula
The amounts work great

But, the number of units formula needs amending.
It shouldn't count the total of items but the number
of unique items as in the reference column there are
several items with the same reference.
In the following data:
dec0400
N156236
N156236
veh jnl
veh jnl2
N156184
N156184
N156716
N156716
N156716
N156785
N156785
N156852
N156852
N156852
There are 5 unique references but 15 in total.
(The items which does NOT start with a N is still to be ignored)
I need to use the unique references in the Anaylsis sheet so
the total number of units should be 5 with the corresponding
number of units under the relevent Days headings.

Thanks for your help, it's much appreciated.

Marcster.

[Bob Phillips]

Sorry that point got lost in my machinations.

Try this version

[geekgirlau]

Nice work xld

[Bob Phillips]

Nice work xld

Thank you

[Marcster]

Hi XLD,
Thanks again
Very much appreciated
Could you tell me how the formula works please?.
I can see that it works with data down to row 200.
Looks for references which start with an N.
Ages from today's date.
Then I'm lost...

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(B$3,FIND("-",B$3)-1)),--(TODAY()-Sheet1!
$A$2:$A$200<=--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3)))),Sheet1!$C$2:$C$200)

As I now need to set up a whole workbook with 16 worksheets in.
1 of them called Anaylsis.
I can import the data to the sheets but having problems setting up the formula's.

Thanks for all your help ,

Marcster.

[Bob Phillips]

Hi XLD,
Thanks again
Very much appreciated
Could you tell me how the formula works please?.
I can see that it works with data down to row 200.
Looks for references which start with an N.
Ages from today's date.
Then I'm lost...

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(B$3,FIND("-",B$3)-1)),--(TODAY()-Sheet1!
$A$2:$A$200<=--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3)))),Sheet1!$C$2:$C$200)

As I now need to set up a whole workbook with 16 worksheets in.
1 of them called Anaylsis.
I can import the data to the sheets but having problems setting up the formula's.

Thanks for all your help ,

Marcster.

Essentially the formula

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),
--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(B$3,FIND("-",B$3)-1)),
--(TODAY()-Sheet1!$A$2:$A$200<=--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3)))),
Sheet1!$C$2:$C$200)

is essentially just a simple SP test

=SUMPRODUCT(--(LEFT(rng1,1)="N"),--(rng2>=lower_bound_days),--(-rng<=upper_bound_days),values)

Part of the complexity comes in the way that I get lower and upper bounds of the days. I use the headings in row 3. For instance a heading of 31-60 Days can easily manipulated to get 31 and 60 which is then used to test that the aged days is within that range.

The first part, 31, is got with

--LEFT(B$3,FIND("-",B$3)-1)

The FIND gets the position of the -, and takes that LEFTmost number of characters -1 (for the - itself). The -- at the start it just to coerce it to a number.

The second part, 60, is somewhat similar, but more work as it is harder to find.

--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3)))

The FIND is used to get the - position again, but also to find the space. We then just get the characters between the - and the space. I TRIM it in case there are spaces in some versions, and again I coerce to a n umber.

[Marcster]

Hi XLD,

Need some more help on this...

What would the formula be without the FIND?
Using lower_bound_days as 31 and upper_bound_days as 60

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(H$3,FIND("-",H$3)-1)),--(TODAY()-Sheet1!$A$2:$A$200<=--TRIM(MID(H$3,FIND("-",H$3)+1,FIND(" ",H$3)-FIND("-",H$3)))),Sheet1!$C$2:$C$200)


What would the formula be without the FIND?
Using lower_bound_days as 31 and upper_bound_days as 60
=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(H$3,FIND("-",H$3)-1)),--(TODAY()-Sheet1!$A$2:$A$200<=--TRIM(MID(H$3,FIND("-",H$3)+1,FIND(" ",H$3)-FIND("-",H$3))))/COUNTIF(Sheet1!$B$2:$B$200,Sheet1!$B$2:$B$200&""))

Thanks,

Marcster.

[Bob Phillips]

Hi XLD,

Need some more help on this...

What would the formula be without the FIND?
Using lower_bound_days as 31 and upper_bound_days as 60

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(H$3,FIND("-",H$3)-1)),--(TODAY()-Sheet1!$A$2:$A$200<=--TRIM(MID(H$3,FIND("-",H$3)+1,FIND(" ",H$3)-FIND("-",H$3)))),Sheet1!$C$2:$C$200)


What would the formula be without the FIND?
Using lower_bound_days as 31 and upper_bound_days as 60
=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),--(TODAY()-Sheet1!$A$2:$A$200>=--LEFT(H$3,FIND("-",H$3)-1)),--(TODAY()-Sheet1!$A$2:$A$200<=--TRIM(MID(H$3,FIND("-",H$3)+1,FIND(" ",H$3)-FIND("-",H$3))))/COUNTIF(Sheet1!$B$2:$B$200,Sheet1!$B$2:$B$200&""))

Thanks,

Marcster.

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),
--(TODAY()-Sheet1!$A$2:$A$200>=31),
--(TODAY()-Sheet1!$A$2:$A$200<=60),
Sheet1!$C$2:$C$200)

and

=SUMPRODUCT(--(LEFT(Sheet1!$B$2:$B$200,1)="N"),
--(TODAY()-Sheet1!$A$2:$A$200>=31),
--(TODAY()-Sheet1!$A$2:$A$200<=60)
/COUNTIF(Sheet1!$B$2:$B$200,Sheet1!$B$2:$B$200&""))

But that is hardly dynamic.

[Marcster]

I'm trying to copy the formula's to work on data in different sheets.
The following uses sheet "9025" to find out how many unique items they are between 0-30 Days:

=SUMPRODUCT(--(LEFT('9025'!$B$2:$B$200,1)="N"),--(TODAY()-'9025'!$A$2:$A$200>=--LEFT(B$3,FIND("-",B$3)-1)),--(TODAY()-'9025'!$A$2:$A$200<=--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3))))/COUNTIF('9025'!$B$2:$B$200,'9025'!$B$2:$B$200&""))

Which works fine.
But when I use a different sheet, replacing 9025 with 9026
=SUMPRODUCT(--(LEFT('9026'!$B$2:$B$200,1)="N"),--(TODAY()-'9026'!$A$2:$A$200>=--LEFT(B$3,FIND("-",B$3)-1)),--(TODAY()-'9026'!$A$2:$A$200<=--TRIM(MID(B$3,FIND("-",B$3)+1,FIND(" ",B$3)-FIND("-",B$3))))/COUNTIF('9026'!$B$2:$B$200,'9026'!$B$2:$B$200&""))

Excel shows #DIV/0! in cell.
Any ideas on where I'm going wrong?
The sheets 9025 and 9026 are formatted the same with the data in the same columns.

Marcster.

[Bob Phillips]

no idea off the top of my head. Can you post the workbook?

[Marcster]

Hi XLD,
Here's the workbook as requested.

On the Analysis sheet the cells in red are the ones with errors.
The other colours work fine.
The sheets 9600 9068 9069 9080 9081 and 9610 are to be ignored as
not sure where I can pull that data from yet.

Once this workbook is setup I'll have a macro which pulls the data
out of our system and into the relevant sheets which with great thanks
to your expert formula coding ability will allow me to analysis by age.

Thanks again

Marcster.