Extract latest date from mixed range.

[uceaamh]

Hello,

I am trying to extract the latest date from a range of dates, with a unique identifier as the criteria. My data looks like:

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1/2/1900[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3/1/1901[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1/5/1899[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2/3/1900[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]7/4/1901[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4/2/1901[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1/5/1900[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3/2/1901[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]6/5/1901[/TD]
[/TR]
</tbody>[/TABLE]

And I would like my output (in a different sheet) to look like:

Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD]1/5/1901[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]6/5/1901[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2/3/1900[/TD]
[/TR]
</tbody>[/TABLE]

So far I have tried (as the input to Sheet2!B) :

=if(sheet2!A1=Sheet1!A:A,max(sheet1!b:b),"")

=SUMPRODUCT((sheet2!A1=Sheet1!A:A)*1,(Sheet1!B)*1)

to no avail. The closest I have got is:

=SUMIFS(Sheet1!B,Sheet1!A:A,Sheet2!A1)

- but that sums all the date ranges, so my answer will be 2/3/2676.

Any help would be appreciated!

Thanks

 

[liveinhope]

Try a pivot table

row field - identifier
values - dates (then change the value field setting for "dates" to "max of dates"


[TABLE="width: 180"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Max of Dates[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]6/05/1901[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]1/05/1900[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]2/03/1900[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]3/02/1901[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]7/04/1901[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

If you want a formula solution...

Book1
	A	B
1	Identifier	Dates
2	12	1/2/1900
3	10	3/1/1901
4	12	1/5/1899
5	13	2/3/1900
6	19	7/4/1901
7	10	4/2/1901
8	12	1/5/1900
9	14	3/2/1901
10	10	6/5/1901
Sheet1

Book1
	A	B
1	5
2	id	max date
3	10	6/5/1901
4	12	1/5/1900
5	13	2/3/1900
6	14	3/2/1901
7	19	7/4/1901
8
Sheet2

In A1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(Sheet1!A2:A10,Sheet1!A2:A10),1))

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($1:1)>$A$1,"",MIN(IF(ISNA(MATCH(Sheet1!$A$2:$A$10,$A$2:A2,0)),Sheet1!$A$2:$A$10)))

In B3 control+shift+enter, not just enter, and copy down:

=MAX(IF(Sheet1!$A$2:$A$10=$A3,Sheet1!$B$2:$B$10))

If you have MAXIFS, in B3 just enter and copy down:

=MAXIFS(Sheet1!$B$2:$B$10,Sheet1!$A$2:$A$10,$A3)

 

[uceaamh]

Thanks for the replies @liveinhope and @Aladin Akyurek. Unfortunately I am having problems with both approaches.

liveinhope:

I was thinking about a pivot, but I'm not sure if I can. The data from sheet 1 and sheet 2 are both from separate Excel spreadsheets. I'm pulling them in via a Query, so they update automatically. Since they come in as separate queries, I don't know how to make a pivot relying on both of them. Is there a way to build a pivot using the IDs from Sheet 2 and Dates from Sheet1?


Aladin Akyurek:

I tried following through those approaches, but in A1, =SUM(IF(FREQUENCY(Sheet1!A2:A10,Sheet1!A2:A10),1)) , the return is 0. That means the formula in A3 returns "", and nothing follows on.

In sheet 2 I already have a list of the unique IDs I am after, so I tried the MAX/MAXIFS formulas. The MAX formula returns 0, and the MAXIFS returns #VALUE . I tried it using both the cell reference (a2 etc.) and the named ranges, with the same effect either way.



Thanks again for the responses!

 

[Aladin Akyurek]

Control+shift+enter means press down the control and the shift keys at the same time while you hit the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[uceaamh]

Thanks Aladin. I did input using ctrl+shift+enter, and saw the { } brackets around the formula. Still, I was getting 0 and #VALUE with the ctrl+shift+enter.

 

[Aladin Akyurek]

Why don't you try out post #3 exactly as given in a clean workbook?

 

[uceaamh]

Thanks for the suggestions. I finally made it work by merging the separate data queries and running a pivot table off of that data.

Again, I appreciate your time and effort!