how to get maximum column value(date) for each distinct record of first column

S

sksuraj01

New Member
Joined
Oct 14, 2012
Messages
4
hi,
i require latest date for each number given in first column,i am using ms excel 2003 on windows xp

i went through some other threads as well very similar to this problem, but none is working for me

sample data-
[TABLE="class: cms_table, width: 355"]
<tbody>[TR]
[TD]number[/TD]
[TD]date[/TD]
[TD]mark[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]07-09-2012 12:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]06-09-2012 00:17[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]21-07-2012 13:58[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]20-07-2012 22:08[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 12:31[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 00:13[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]24-07-2012 10:13[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]24-07-2012 00:11[/TD]
[TD="align: right"]891[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]18-07-2012 13:12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]04-06-2012 22:15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]791[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]08-05-2012 22:06[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]10-07-2012 18:23[/TD]
[TD="align: right"]822[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]08-05-2012 23:02[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]7417238304[/TD]
[TD="align: right"]16-08-2012 22:06[/TD]
[TD="align: right"]766[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]01-10-2012 13:19[/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]10-07-2012 13:32[/TD]
[TD="align: right"]942[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]09-05-2012 22:36[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]


o/p required-

[TABLE="class: cms_table, width: 280"]
<tbody>[TR]
[TD]number[/TD]
[TD]date[/TD]
[TD]mark[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]07-09-2012 12:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 12:31[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]791[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7417238304[/TD]
[TD="align: right"]16-08-2012 22:06[/TD]
[TD="align: right"]766[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]01-10-2012 13:19[/TD]
[TD="align: right"]205[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Try something like:
Excel Workbook
ABCDEFG
1numberdatemarknumberdatemark
27 417 001 2137.9.2012 12:3057 417 001 2137.9.2012 12:305
37 417 001 2136.9.2012 0:1747 417 094 5152.8.2012 12:312
47 417 001 21321.7.2012 13:5837 417 094 51618.7.2012 13:10791
57 417 001 21320.7.2012 22:0817 417 094 52418.7.2012 13:1044
67 417 094 5152.8.2012 12:3127 417 238 30416.8.2012 22:06766
77 417 094 5152.8.2012 0:1367 417 271 7411.10.2012 13:19205
87 417 094 51524.7.2012 10:136
97 417 094 51524.7.2012 0:11891
107 417 094 51518.7.2012 13:1210
117 417 094 5154.6.2012 22:155
127 417 094 51618.7.2012 13:10791
137 417 094 5168.5.2012 22:0631
147 417 094 52418.7.2012 13:1044
157 417 094 52410.7.2012 18:23822
167 417 094 5248.5.2012 23:0249
177 417 238 30416.8.2012 22:06766
187 417 271 7411.10.2012 13:19205
197 417 271 74110.7.2012 13:32942
207 417 271 7419.5.2012 22:3611
Sheet
 
Upvote 0
sksuraj01 said:
hi,
i require latest date for each number given in first column,i am using ms excel 2003 on windows xp

i went through some other threads as well very similar to this problem, but none is working for me

sample data-
[TABLE="class: cms_table, width: 355"]
<tbody>[TR]
[TD]number[/TD]
[TD]date[/TD]
[TD]mark[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]07-09-2012 12:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]06-09-2012 00:17[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]21-07-2012 13:58[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]20-07-2012 22:08[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 12:31[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 00:13[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]24-07-2012 10:13[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]24-07-2012 00:11[/TD]
[TD="align: right"]891[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]18-07-2012 13:12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]04-06-2012 22:15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]791[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]08-05-2012 22:06[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]10-07-2012 18:23[/TD]
[TD="align: right"]822[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]08-05-2012 23:02[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]7417238304[/TD]
[TD="align: right"]16-08-2012 22:06[/TD]
[TD="align: right"]766[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]01-10-2012 13:19[/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]10-07-2012 13:32[/TD]
[TD="align: right"]942[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]09-05-2012 22:36[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]


o/p required-

[TABLE="class: cms_table, width: 280"]
<tbody>[TR]
[TD]number[/TD]
[TD]date[/TD]
[TD]mark[/TD]
[/TR]
[TR]
[TD="align: right"]7417001213[/TD]
[TD="align: right"]07-09-2012 12:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7417094515[/TD]
[TD="align: right"]02-08-2012 12:31[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7417094516[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]791[/TD]
[/TR]
[TR]
[TD="align: right"]7417094524[/TD]
[TD="align: right"]18-07-2012 13:10[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7417238304[/TD]
[TD="align: right"]16-08-2012 22:06[/TD]
[TD="align: right"]766[/TD]
[/TR]
[TR]
[TD="align: right"]7417271741[/TD]
[TD="align: right"]01-10-2012 13:19[/TD]
[TD="align: right"]205[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Another way:
Excel 2010
ABCDEFG
1numberdatemarknumberdatemark
274170012139/7/2012 12:305741700121307/09/2012 12:30:005
374170012139/6/2012 0:174741709451502/08/2012 12:31:002
474170012137/21/2012 13:583741709451618/07/2012 13:10:00791
574170012137/20/2012 22:081741709452418/07/2012 13:10:0044
674170945158/2/2012 12:312741723830416/08/2012 22:06:00766
774170945158/22012 12:13:00 AM6741727174101/10/2012 13:19:00205
874170945157/24/2012 10:136
974170945157/24/2012 0:11891
1074170945157/18/2012 13:1210
1174170945156/4/2012 22:155
1274170945167/18/2012 13:10791
1374170945165/8/2012 22:0631
1474170945247/18/2012 13:1044
1574170945247/10/2012 18:23822
1674170945245/8/2012 23:0249
1774172383048/16/2012 22:06766
18741727174110/1/2012 13:19205
1974172717417/10/2012 13:32942
2074172717415/9/2012 22:3611
Sheet3
Cell Formulas
RangeFormula
F2{=MAX(IF($A$2:$A$20=E2,$B$2:$B$20))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Or if you don't need the Mark-value just drop your table to a pivot table, place your numbers to the row fields and dates to the data field. Then change the calculation to Max and you're done.
 
Upvote 0
The formula for the second part should have been:G2 copy down confirm Control+Shift+Enter
=INDEX($C$2:$C$20,MATCH(E2&F2,$A$2:$A$20&$B$2:$B$20,0))
 
Upvote 0
hi,
can u please explain it step by step like how many rows or column i should select,as i am getting zero value ,i tried with many options....
 
Upvote 0
Misca said:
Try something like:
ABCDEFG
numberdatemark numberdatemark

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:86px;"><col style="width:100px;"><col style="width:64px;"><col style="width:64px;"><col style="width:86px;"><col style="width:100px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]7 417 001 213[/TD]
[TD="align: right"]7.9.2012 12:30[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]7 417 001 213[/TD]
[TD="align: right"]7.9.2012 12:30[/TD]
[TD="align: right"]5
[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]7 417 001 213[/TD]
[TD="align: right"]6.9.2012 0:17[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]2.8.2012 12:31[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]7 417 001 213[/TD]
[TD="align: right"]21.7.2012 13:58[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]7 417 094 516[/TD]
[TD="align: right"]18.7.2012 13:10[/TD]
[TD="align: right"]791[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]7 417 001 213[/TD]
[TD="align: right"]20.7.2012 22:08[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]7 417 094 524[/TD]
[TD="align: right"]18.7.2012 13:10[/TD]
[TD="align: right"]44[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]2.8.2012 12:31[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]7 417 238 304[/TD]
[TD="align: right"]16.8.2012 22:06[/TD]
[TD="align: right"]766[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]2.8.2012 0:13[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]7 417 271 741[/TD]
[TD="align: right"]1.10.2012 13:19[/TD]
[TD="align: right"]205[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]24.7.2012 10:13[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]24.7.2012 0:11[/TD]
[TD="align: right"]891[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]18.7.2012 13:12[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]7 417 094 515[/TD]
[TD="align: right"]4.6.2012 22:15[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]7 417 094 516[/TD]
[TD="align: right"]18.7.2012 13:10[/TD]
[TD="align: right"]791[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]7 417 094 516[/TD]
[TD="align: right"]8.5.2012 22:06[/TD]
[TD="align: right"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]7 417 094 524[/TD]
[TD="align: right"]18.7.2012 13:10[/TD]
[TD="align: right"]44[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]7 417 094 524[/TD]
[TD="align: right"]10.7.2012 18:23[/TD]
[TD="align: right"]822[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]7 417 094 524[/TD]
[TD="align: right"]8.5.2012 23:02[/TD]
[TD="align: right"]49[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]7 417 238 304[/TD]
[TD="align: right"]16.8.2012 22:06[/TD]
[TD="align: right"]766[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]7 417 271 741[/TD]
[TD="align: right"]1.10.2012 13:19[/TD]
[TD="align: right"]205[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]7 417 271 741[/TD]
[TD="align: right"]10.7.2012 13:32[/TD]
[TD="align: right"]942[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]7 417 271 741[/TD]
[TD="align: right"]9.5.2012 22:36[/TD]
[TD="align: right"]11[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
F2{=MAX(IF($A$2:$A$20=$E2,$B$2:$B$20,0))}
G2=SUMPRODUCT(--($A$2:$A$20=E2),--($B$2:$B$20=F2),$C$2:$C$20)

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...



hi,
can u please explain it step by step like how many rows or column i should select,as i am getting zero value ,i tried with many options....
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
489
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top