getting records with MAX value

**REI_707** · 06-06-2013, 03:01 PM

Hi

this may be a very basic question. I have a dataset with 5 columns. i want to identify the the max users in each zip code and the corresponding ID for it. For e.g. for zip 01105, max users are 1380 for ID 010840. I want my final data to have just these 3 values.

I tried the pivot table but it hides the ID corresponding to the MAX value. Any input is appreciated. thanks.

ID Zip City State Users
010840 01105 Springfield MA 1380
010120 01105 Springfield MA 1102
010030 01105 Springfield MA 518
011450 01105 Springfield MA 27
013840 01105 Springfield MA 3
010840 01107 Springfield MA 629
010030 01107 Springfield MA 498
010120 01107 Springfield MA 487
011450 01107 Springfield MA 158
013260 01107 Springfield MA 18
012160 01840 Lawrence MA 989
010860 01840 Lawrence MA 10
0218870 01840 Lawrence MA 1
011280 02119 Boston MA 3121
010290 02119 Boston MA 2179
012070 02119 Boston MA 1789
01E00090 02119 Boston MA 814
011210 02119 Boston MA 660
01E00089 02119 Boston MA 495
010600 02119 Boston MA 217
010170 02119 Boston MA 205
013600 02119 Boston MA 114
012010 02119 Boston MA 101
010720 02119 Boston MA 97

**REI_707** · 06-06-2013, 03:43 PM

please let me know if its not feasible in excel. the above is just a sample...there are thousands of rows of data so doing it manually is not possible. thanks.

**Haseeb Avarakkan** · 06-06-2013, 05:05 PM

Hello,

Assuming the data is in A1:E25 (including header)

Enter your required Zip codes in a range, ie: H2 to down, then in

I2; with CTRL+SHIFT+ENTER not just ENTER

=MAX(IF(B$2:B$25=H2,E$2:E$25))

J2; with CTRL+SHIFT+ENTER not just ENTER

=INDEX(A$2:A$25,MATCH(1,IF(B$2:B$25=H2,IF(E$2:E$25=I2,1)),0))

Then after select I2:J2 & copy down.

Hope this helps;