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.

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!
================================================== ===


"Shams" wrote in message
...
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.

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!
================================================== ===

"RagDyeR" wrote in message
...
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!
================================================== ===


"Shams" wrote in message
...
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.

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" wrote:

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!
================================================== ===


"Shams" wrote in message
...
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.

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

Regards,

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

"Shams" wrote in message
...
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" wrote:

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!
================================================== ===


"Shams" wrote in message
...
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.

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" wrote:

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

Regards,

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

"Shams" wrote in message
...
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" wrote:

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!
================================================== ===


"Shams" wrote in message
...
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.

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Shams" wrote in message
...
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" wrote:

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

Regards,

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

"Shams" wrote in message
...
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" wrote:

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!
================================================== ===


"Shams" wrote in message
...
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.