Selecting the latest date reference

[Shams]

Folks,
I am trying to figure how to best get the data I need given multiple changes
in a year. For example, cost for a product may go through several updates
throughout the year. So, to ensure that I get the most up-to-date costng
during that year..I download the Jan 1 to Dec 31st range of data by our
products. Now the problem is one product may have had 4 revisions during the
year with the last one coming in, say, October. This is how my data is
coming up:

Product# Costing Date
65899 01/01/2007
65899 03/01/2007
65899 09/01/2007
65569 03/01/2007
65569 10/01/2007

Using the above as an example, how can I ensure that I get the latest update
per product. So, Prod # 65899 comes back with 09/01/2007 and 65569 comes back
with 10/01/2007

I tried to do a pivot putting the Prod #s in Rows, the costing Dates (i.e.
12 months) in Columns and ofcourse the costs under Data. By doing this I got
a view of any SKU by all 12 months. So, 65569 will populate March and
October and leave the other cells blank. I thought I would be able to manage
what I need by first putting it in a Pivot format. But I don't how to
proceed any further.

Hoping to get some tips on this file. Thank you very much.

 

[RagDyeR]

If the datalist is in chronological order, as in your example, try this:

=LOOKUP(2,1/(A1:A50=65899),B1:B50)

Assuming the product number is a true number.

It is a good idea to assign a cell to contain your criteria, so that all you
need to do to get different product date is change the contents of the cell.

Say you use C1, then:

=LOOKUP(2,1/(A1:A50=C1),B1:B50)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Folks,
I am trying to figure how to best get the data I need given multiple changes
in a year. For example, cost for a product may go through several updates
throughout the year. So, to ensure that I get the most up-to-date costng
during that year..I download the Jan 1 to Dec 31st range of data by our
products. Now the problem is one product may have had 4 revisions during
the
year with the last one coming in, say, October. This is how my data is
coming up:

Product# Costing Date
65899 01/01/2007
65899 03/01/2007
65899 09/01/2007
65569 03/01/2007
65569 10/01/2007

Using the above as an example, how can I ensure that I get the latest update
per product. So, Prod # 65899 comes back with 09/01/2007 and 65569 comes
back
with 10/01/2007

I tried to do a pivot putting the Prod #s in Rows, the costing Dates (i.e.
12 months) in Columns and ofcourse the costs under Data. By doing this I got
a view of any SKU by all 12 months. So, 65569 will populate March and
October and leave the other cells blank. I thought I would be able to
manage
what I need by first putting it in a Pivot format. But I don't how to
proceed any further.

Hoping to get some tips on this file. Thank you very much.

 

[RagDyeR]

If your dates are *not* in order, try this *array* formula instead:

=MAX(IF(A1:A50=C1,B1:B50))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

If the datalist is in chronological order, as in your example, try this:

=LOOKUP(2,1/(A1:A50=65899),B1:B50)

Assuming the product number is a true number.

It is a good idea to assign a cell to contain your criteria, so that all you
need to do to get different product date is change the contents of the cell.

Say you use C1, then:

=LOOKUP(2,1/(A1:A50=C1),B1:B50)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Folks,
I am trying to figure how to best get the data I need given multiple changes
in a year. For example, cost for a product may go through several updates
throughout the year. So, to ensure that I get the most up-to-date costng
during that year..I download the Jan 1 to Dec 31st range of data by our
products. Now the problem is one product may have had 4 revisions during
the
year with the last one coming in, say, October. This is how my data is
coming up:

Product# Costing Date
65899 01/01/2007
65899 03/01/2007
65899 09/01/2007
65569 03/01/2007
65569 10/01/2007

Using the above as an example, how can I ensure that I get the latest update
per product. So, Prod # 65899 comes back with 09/01/2007 and 65569 comes
back
with 10/01/2007

I tried to do a pivot putting the Prod #s in Rows, the costing Dates (i.e.
12 months) in Columns and ofcourse the costs under Data. By doing this I got
a view of any SKU by all 12 months. So, 65569 will populate March and
October and leave the other cells blank. I thought I would be able to
manage
what I need by first putting it in a Pivot format. But I don't how to
proceed any further.

Hoping to get some tips on this file. Thank you very much.

 

[Shams]

Hi RagDyeR.
I am trying to understand the syntax in your first formula: Lookup,,not sure
how to interpret it..i didn't understand what " (2,1/(a1:a50...." meant

So my product numbers are like 65899C, 65569.
They reside in Column A, the Costing Dates actually reside in Col. E (I have
other data like Brand, Product Description, etc. columns B to D) and the
costings are in Col. F
now i have 300 rows of data for all our Products and their costing change
date (just like I showed in my original email)..so..i wouldn't want to type
the exact Prod# like you did in the first formula

so i thought that's what you were referring to in your second formula
recommending a criteria cell - not sure how to read this either!!

so could i now say in Column C: =a1...so that it populates it with 65899c
and then do the formula
I get #N/A when i do this

I'll appreciate if you can prod me along a bit further!! Thanks

 

[RagDyeR]

Are you actually looking for the cost and *not* the date?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi RagDyeR.
I am trying to understand the syntax in your first formula: Lookup,,not sure
how to interpret it..i didn't understand what " (2,1/(a1:a50...." meant

So my product numbers are like 65899C, 65569.
They reside in Column A, the Costing Dates actually reside in Col. E (I have
other data like Brand, Product Description, etc. columns B to D) and the
costings are in Col. F
now i have 300 rows of data for all our Products and their costing change
date (just like I showed in my original email)..so..i wouldn't want to type
the exact Prod# like you did in the first formula

so i thought that's what you were referring to in your second formula
recommending a criteria cell - not sure how to read this either!!

so could i now say in Column C: =a1...so that it populates it with 65899c
and then do the formula
I get #N/A when i do this

I'll appreciate if you can prod me along a bit further!! Thanks

 

[Shams]

Hi RagDyeR,
At the end of the day, I'd like to see the latest costing...
so i thought that the lookup function would find me the latest date by
Product#

the Max function would have worked perfectly had i been able to scamble my
dates per product

 

[RagDyer]

The Max formula *will work* with dates in any order.
But since it's an array formula, you try to use it only where necessary.
Since you're only talking about 300 rows, there's no problem.
Just remember to use CSE when entering it.

Now, if you want to *exactly* describe your datalist configuration, I can
work up a formula, or set of formulas, for you to be able to see the latest
cost on all your products.

If you enter your *entire unique* list of products down a column, you could
have a formula in the adjoining column display the latest cost - using the
data that you have in your original datalist.

If you're interested, post back with a description of exactly how your data
is set up.

 

[Shams]

Thanks again for your help. Yes, the max formula works only as far to
identify the latest date and not the costing! So, let me tabulate for you the
data structure:

Product # Brand Costing Date Value

65899 SD 01/01/2007
$0.3652
65899 SD 03/01/2007
$0.4269
65899 SD 09/01/2007
$0.4400
65569 PD 03/01/2007
$0.5698
65569 PD 10/01/2007
$0.6900
70569 SD 03/01/2007
$0.6549
70569 SD 06/01/2007
$0.7259

Starts in Column A and ends in Column D. So, the idea is to only get the
latest costing $$. When I used the max formula and then did a pivot, it
summed up the instances of SKU..i.e. 65899 summed up to be $1.232 instead of
$0.4440. At the end of the day, I would like to be able to show one row per
Product # with the latest costing $$

Thanks for your help so far..I learnt to use the max formula in a very
intuitive manner!!

Shams.

 

[RagDyer]

Let's say that you enter, in Column G, a unique list of your product #'s,
starting in G2.

Assuming that the data in Column A and the data in Column G is identical
(Text or Numeric).

Assuming that all your dates are in chronological order.
With dates in order, that means that the *last* entered product number will
represent the *latest* cost.
Therefore, we don't have to consider the actual date in the calculation.

In H2, enter this formula:

=LOOKUP(2,1/($A$1:$A$50=G2),$D$1:$D$50)

And copy down as far as needed.

This should give you the latest cost for each product entered in Column G.

Let me know if I assumed incorrectly.