determining latest duplicate

[rickree]

hello,

I'm trying to return a value based on revision of a duplicate number.
For example I have the following lists on two separate sheets.

Sheet 1 Database
Column A Column B column C
NAME REVISION date
Wheel 1 xx
Tire 1 xx
Tire 2 zz
Window 1 xx
Tire 3 aa
Hood 1 xx


Sheet 2 - results
Column A Column B column C
Name revision date
Wheel 1 xx
Tire 3 aa
Window 1 xx
Hood 1 xx


I would like to return in sheet two the greatest values found from
sheet one based on revision.

So I assume I would have to return the cell array of duplicates then
compare the revision number and based on that revision return a cell.
Though I'm not sure how to go about it?

 

[Ron Coderre]

Try this Pivot Table solution:

On Sheet2....
From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data range on Sheet1
beginning with the col headings: (NAME, REVISION, DATE) and including the
data below them......Click [Next]

Click the [Layout] button
ROW: Drag the NAME field here
DATA:
Drag the REVISION field here
If it doesn't list as Max of REVISION...dbl-click it and set it to
Max....Click [OK]

Drag the DATE field below the REVISION field
If it doesn't list as Max of DATE...dbl-click it and set it to Max....Click
[OK]

Click [OK] to exit the Layout window
Select where you want the Pivot Table...and click [Finish]

Almost done.....
Click and HOLD on the "Data" heading
Drag it onto the "Total" heading and release.

The end result will look like this:
Data
NAME Max of REVISION Max of date
Hood 1 01/06/07
Tire 3 01/05/07
Wheel 1 01/01/07
Window 1 01/04/07

Is that something you can work with?

Post back with more questions.
***********
Regards,
Ron

XL2002, WinXP

 

[rickree]

Thanks for that suggestion. That does take care of the filtering but
now i want to be able to use that filter to refer to the row in which
data existed.

So I have the database

name revision some value
1 0 a
1 1 a
1 2 d
1 3 d
2 0 d
3 0 c
4 0 d
5 1 a
5 2 a
5 3 d
6 0 b
6 1 b
6 2 d

I want to create a report showing a single instance of the name with
the highest revision and how it correlates to some value which can be
duplicated per name but not revision. I can not add the 'something
value' to the pivot table filtering because it only uses numerical
functions, I assume.

Is there such a thing as a double match lookup?

I was thinking of using a match to search the pivot table and then
return the revision cell for the matched part name. At which point I
can use the indirect and concentate functions to return the max
revision value. Though I'm stuck, no I have the max revision value
returned in a formula but I want to search for the part name and max
revision in the original data base to return the row value. this
ultimately would allow me to refer to any column attribute of the
part.