Assign a rank using 2 columns

[MikeGeno]

I am working on some inventory data in which I have account numbers and scan dates. I would like to pivot the data and be able to determine which is the latest date for each account. In the example below hospital 1234 has 2 scan dates, 20110101 and 20110505. I would like to flag the 20110505 Date with a rank or some kind of indicator that shows it is the most recent date. Would like to do this for each hospital.



Column A Column B Column C
Acct Scan Date Most Recent?
1234 20110101
1234 20110101
1234 20110101
1234 20110101
1234 20110505 Y
1234 20110505
1234 20110505
6789 20110202
6789 20110202
6789 20110202
6789 20110404 Y
6789 20110404
6789 20110404

 

[T. Valko]

I am working on some inventory data in which I have account numbers and scan dates. I would like to pivot the data and be able to determine which is the latest date for each account. In the example below hospital 1234 has 2 scan dates, 20110101 and 20110505. I would like to flag the 20110505 Date with a rank or some kind of indicator that shows it is the most recent date. Would like to do this for each hospital.



Column A Column B Column C
Acct Scan Date Most Recent?
1234 20110101
1234 20110101
1234 20110101
1234 20110101
1234 20110505 Y
1234 20110505
1234 20110505
6789 20110202
6789 20110202
6789 20110202
6789 20110404 Y
6789 20110404
6789 20110404

What version of Excel are you using?

Maybe this...

Book1
	A	B	C
2	1234	20110101	_
3	1234	20110101	_
4	1234	20110101	_
5	1234	20110101	_
6	1234	20110505	Y
7	1234	20110505	_
8	1234	20110505	_
9	6789	20110202	_
10	6789	20110202	_
11	6789	20110202	_
12	6789	20110404	Y
13	6789	20110404	_
14	6789	20110404	_
Sheet1

This array formula** entered in C2 and copied down:

=IF(AND(B2=MAX(IF(A$2:A$14=A2,B$2:B$14)),SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))=1),"Y","")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[MikeGeno]

T. Valko, Thank you for the help!

Sorry I forgot to say I am using Excel 2007.

In my haste I forgot to show that I need the Y to carry down to all dates that are max for each account. In your example I would need to have a Y next to all 20110505 dates so when I use the page by on field on Y I will get all the data from May 5th.

 

[T. Valko]

T. Valko, Thank you for the help!

Sorry I forgot to say I am using Excel 2007.

In my haste I forgot to show that I need the Y to carry down to all dates that are max for each account. In your example I would need to have a Y next to all 20110505 dates so when I use the page by on field on Y I will get all the data from May 5th.

That makes it easier.

Book1
	A	B	C
2	1234	20110101	_
3	1234	20110101	_
4	1234	20110101	_
5	1234	20110101	_
6	1234	20110505	Y
7	1234	20110505	Y
8	1234	20110505	Y
9	6789	20110202	_
10	6789	20110202	_
11	6789	20110202	_
12	6789	20110404	Y
13	6789	20110404	Y
14	6789	20110404	Y
Sheet1

Array formula** entered in C2 and copied down:

=IF(B2=MAX(IF(A$2:A$14=A2,B$2:B$14)),"Y","")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[MikeGeno]

Works perfectly, Thanks so much for the help!

 

[T. Valko]

Works perfectly, Thanks so much for the help!

You're welcome. Thanks for the feedback!

 

[bosco_yip]

Or, in C2 this non-array formula :

=IF(B2=LOOKUP(2,1/(A$2:A$1000=A2),B$2:B$1000),"Y","")

Regards