Return Largest Number with Multiple Entries

[ROSE217]

Hello everyone,

I hope you can help me with this, I've been struggling with it for a while. I have a large spreadsheet with over 100,000 rows. I need to find the largest number associated with a users id, there are multiple values associated with the user ID but I want to return the largest number associated to the user. Here's an example below, user ID 00300000002ayxtAAA has the values 4, 8, 2, 9, 2 associated with it, I need to return the number 9 (the highest value) in a column next to each entry of the users ID.

[TABLE="width: 496"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]00300000002auuQAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002auuQAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002auuQAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="align: right"]9 [/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD="align: right"]9 [/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]00300000002cuh1AAA[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C
1		00300000002auuQAAQ	0
2		00300000002auuQAAQ	0
3		00300000002auuQAAQ	0
4		00300000002ayxtAAA	9
5		00300000002ayxtAAA	9
6		00300000002ayxtAAA	9
7		00300000002ayxtAAA	9
8	4	00300000002ayxtAAA	9
9	8	00300000002ayxtAAA	9
10	2	00300000002ayxtAAA	9
11		00300000002ayxtAAA	9
12	9	00300000002ayxtAAA	9
13		00300000002ayxtAAA	9
14		00300000002ayxtAAA	9
15	2	00300000002ayxtAAA	9
16		00300000002ayxtAAA	9
17		00300000002ayxtAAA	9
18		00300000002ayxtAAA	9
19		00300000002ayxtAAA	9
20		00300000002ayxtAAA	9
21		00300000002bnjJAAQ	9
22		00300000002bnjJAAQ	9
23		00300000002bnjJAAQ	9
24	4	00300000002bnjJAAQ	9
25	9	00300000002bnjJAAQ	9
26	2	00300000002bnjJAAQ	9
27		00300000002bnjJAAQ	9
28		00300000002bnjJAAQ	9
29		00300000002bnjJAAQ	9
30		00300000002bnjJAAQ	9
31		00300000002cuh1AAA	2
32		00300000002cuh1AAA	2
33	2	00300000002cuh1AAA	2
34		00300000002cuh1AAA	2
35	1	00300000002cuh1AAA	2
36		00300000002cuh1AAA	2
37		00300000002cuh1AAA	2
38		00300000002cuh1AAA	2
39		00300000002cuh1AAA	2
40		00300000002cuh1AAA	2
41		00300000002cuh1AAA	2
42		00300000002cuh1AAA	2
43		00300000002cuh1AAA	2
44		00300000002cuh1AAA	2
Sheet1

Cell Formulas
Range	Formula
C1	{=MAX(IF($B$1:$B$44=$B1,$A$1:$A$44))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[peterhinton]

you could try this array formula,

{=IF(B:B="00300000002auuQAAQ",MAX(A:A))}
Use CTRL + SHFT + ENTER to make it an array

 

[nardagus]

Is it possible to sort the data by user ID and by this number (descending)?

What I'd do would be:

1) Sort the data by user ID ascending, Number descending
2) Copy user ID column to other place of this sheet or to another sheet
3) Remove duplicates
4) Write this formula and fill it down (sorry in this format, but I have problems with HTMLMaker):

=INDEX(A:A;MATCH(F2;B:B;0))

5) This way you will more clear view on max number for every user:

[TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Max number[/TD]
[TD="class: xl65, width: 142"]User ID[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl66, width: 142"]00300000002auuQAAQ[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl66, width: 142"]00300000002ayxtAAA[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl66, width: 142"]00300000002bnjJAAQ[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, width: 142"]00300000002cuh1AAA[/TD]
[/TR]
</tbody>[/TABLE]


P.s. I wonder if array formula is efficient enough for over 100 000 rows...

 

[peterhinton]

P.s. I wonder if array formula is efficient enough for over 100 000 rows...

Very Valid Point ?!?!
Would probably be ok if looking at say one User ID. but if its a working document with more calculations it would probably fail

 

[ROSE217]

Very Valid Point ?!?!
Would probably be ok if looking at say one User ID. but if its a working document with more calculations it would probably fail

I actually got it to work, it took the document a long time to process it and it crashed a few times but I'm happy with the result, thanks for your help