Yes. You are right Ralph...Actually got this with Index/Match function. One of my colleagues helped me with this.
Thanks for everyone's help.
________________________________
From: "ralph.gregory@skaino.co.uk" <ralph.gregory@skaino.co.uk>
To: ExcelVBA@yahoogroups.com
Sent: Friday, November 11, 2011 12:22 AM
Subject: Re: [ExcelVBA] Aging Inventory
I think the op has a list of parts sales or orders with date of dispach or sale. Some part numbers will occur many times. So what he is wanting is a list of all the parts that have moved in last 30 days, then each unique part in that bucket to be used as a criteria of a search on the remaining list. So a part may be listed with a date 45 days ago but because the same part is also listed with a date of 20 days ago , they will both be marked for bucket 1. So a filter by part then date would put them all together at least
Sent from my HTC
----- Reply message -----
From: david.smart@ubs.com
To: <ExcelVBA@yahoogroups.com>
Subject: [ExcelVBA] Aging Inventory
Date: Fri, Nov 11, 2011 7:52 am
> However, for a situation below, it would be good to have only 0-30Days
in both C2 and C3 cells.
I don't really understand what you mean here. I gave you formulas for
four columns and said that they should be copied down.
I didn't actually give any information for rows 2 or 3, just row 1. You
would fill all the other rows with your formula copy.
> In this case, it just displays the last usage date.
Again, not sure what you mean. My first set of formulas will display
the last usage date in C or D or E depending on the age of the usage.
You will probably need to put the formulas in some other columns ...
I've just given examples.
My second set of formulas displayed the number of days since last use,
but again in the appropriate column C or D or E.
> Also, doesn't the PN# be part of the condition?
I don't see why it would be. We're working down the rows here and each
row will presumably represent a different item. Reading across the row,
you can see the aging of the last usage in C or D or E (from my
examples, or whatever columns you choose to use).
You didn't read my four formulas as going into different ROWS, did you?
They are intended to go into different COLUMNS on the same row (which is
row 1 in my examples).
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of Prasanna Kumar
Sent: Friday, 11 November 2011 09:07
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Aging Inventory
Thanks David. However, for a situation below, it would be good to have
only 0-30Days in both C2 and C3 cells. In this case, it just displays
the last usage date. Also, doesn't the PN# be part of the condition?
Thanks for your help.
Usage Date Days Age Part#
11/2/2011 8 11/2/2011 C4871A
10/2/2011 39 10/2/2011 C4871A
From: David Smart <smartware.consulting@gmail.com
<mailto:smartware.consulting%40gmail.com> >
To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
Sent: Thursday, November 10, 2011 12:59 PM
Subject: Re: [ExcelVBA] Aging Inventory
You say "the formula", but be aware that you will need one for each
bucket.
You might find it a bit easier if you put a helper column in to hold the
days since use. This is a simple subtraction:
If column A is the last used date and column B is the helper:
B1 is =TODAY()-A1
C1 is =IF($B1<=30,$A1,"")
D1 is =IF(AND($B1>30,$B1<=60),$A1,"")
E1 is =IF($B1>60,$A1,"")
and these formulas copy down as needed.
Excel will probably get the cell formatting wrong. You need to change
the
helper column to display in General format, not date. You also need to
get
C, D and E to display the last used date in date format if it simply
gives
you a number.
If you want the number of days since last used in the aging columns
instead,
then
C1 is =IF($B1<=30,$B1,"")
D1 is =IF(AND($B1>30,$B1<=60),$B1,"")
E1 is =IF($B1>60,$B1,"")
and format as General if Excel formats as a date.
Regards, Dave S
----- Original Message -----
From: "pkj7461" <pkj7461@yahoo.com <mailto:pkj7461%40yahoo.com> >
To: <ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> >
Sent: Friday, November 11, 2011 5:19 AM
Subject: [ExcelVBA] Aging Inventory
> Hello,
>
> I hope someone helps me with Aging the inventory based on usage. I
have
> the usage data and am trying to create buckets of 0-30Days, 31-60Days,
> >60Days based on the date used.
> For example, if part#C4871A is used in last 30 days, it should be in
> 0-30Days. Even if this item is used in last 60 days, the aging should
be
> still set as 0-30Days. Can you please help me with the formula for
this
> type of aging?
>
> Thanks
> Kumar.
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then
search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1411 / Virus Database: 1522/4007 - Release Date:
11/09/11
>
[Non-text portions of this message have been removed]
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar