Most Recent date and additional information

[wiwchar]

Good day. I have a puzzle that I know someone out there is able to help me with. I have a workbook where on one tab I have information about a city, and then 20+ additional pieces of information for each city in the same row. Each city has multiple entries stored here for history purposes. What I need to do is on another tab, using a list of all the cities, for each one, place the most recent date and then the remaining items for that row. For example, the data entry tab may look like:

City Date D1 D2 D3 D4 D5 D6 D7
City1 12/1/12 1 1 0 3 2 2 0
City2 12/2/12 0 1 3 1 0 2 4
City3 12/2/12 1 0 0 1 1 0 3
City2 5/1/13 1 1 3 2 0 3 4
City3 6/8/13 2 1 1 1 2 1 3
City2 6/8/13 2 1 3 2 0 3 4
City1 1/10/14 1 2 1 3 2 2 1

The summary tab would have City1, City2, City3 in column A and then clicking on a button to run some code would retrieve:

City Date D1 D2 D3 D4 D5 D6 D7
City1 1/10/14 1 2 1 3 2 2 1
City2 6/8/13 2 1 3 2 0 3 4
City3 6/8/13 2 1 1 1 2 1 3

Any help with this matter is much appreciated.

 

[yesterdays]

[TABLE="class: grid, width: 646"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]D2[/TD]
[TD="align: center"]D3[/TD]
[TD="align: center"]D4[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]D6[/TD]
[TD="align: center"]D7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]City1[/TD]
[TD="align: center"]12/1/2012[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]City2[/TD]
[TD="align: center"]12/2/2012[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]City3[/TD]
[TD="align: center"]12/2/2012[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]City2[/TD]
[TD="align: center"]5/1/2013[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]City3[/TD]
[TD="align: center"]6/8/2013[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]City2[/TD]
[TD="align: center"]6/8/2013[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]City1[/TD]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]D2[/TD]
[TD="align: center"]D3[/TD]
[TD="align: center"]D4[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]D6[/TD]
[TD="align: center"]D7[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]City1[/TD]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]City2[/TD]
[TD="align: center"]6/8/2013[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]City3[/TD]
[TD="align: center"]6/8/2013[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

No need to use VBA

B12 :
=MAX($B$2:$B$8*($A$2:$A$8=A12))
Ctrl Shift Enter
Fill down

C12:
=INDEX($C$2:$I$8,MATCH(1,($A$2:$A$8=$A12)*($B$2:$B$8=$B12),0),COLUMN()-COLUMN($B12))
Ctrl Shift Enter
Fill down and across
Edit range to suit your data

 

[wiwchar]

Thanks for the feedback. When I enter the formulas into B12 and C12, I get #VALUE. What am I doing incorrectly?

 

[yesterdays]

You need to confirm it with Ctrl Shift Enter , not just enter