Find the largest number by group and populate

[sanjimmy]

Would like to get help on this :
I have a worksheet looks like this:
Group ID qnty
567 5
567 5
678 0
678 0
678 0
678 0
678 0
568 4
568 0
568 0
568 4
777 1
777 0
777 0
777 1

Desired result: Find the largest number by group Id and populate al
the associated ID.
The data are huge, but here is the sample result:

Group ID qnty qnty updated
567 5 5
567 5 5
678 0 0
678 0 0
678 0 0
678 0 0
678 0 0
568 4 4
568 0 4
568 0 4
568 4 4
777 1 1
777 0 1
777 0 1
777 1 1


Thanks.
Jimm

 

[Guest]

Try this ARRAY FORMULA*:

With
GroupID's in A2:A20
Qty's in B2:B20

C2: =MAX(IF($A$2:$A$20=A2,$B$2:$B$20,0))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy C2
Paste into C3:C20

Change range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[sanjimmy]

Ron,
Thanks..works fine.
But I have a large data ( about 40K records) and it takes about 15min
to calculate the formule ( for copy and past action)..Other that looks
fine..

Thanks again
Jimmy