How can I extract each Max key value ?

  • Thread starter Thread starter diglas1 via OfficeKB.com
  • Start date Start date
D

diglas1 via OfficeKB.com

How can I extract the first entries for each key in Excel from the following
4 columns ?
(i.e. the maximum value of cocatenated columns 1,2 &4 e.g. JohnSmith200). I
already concatenated the first 2 columns as the key and SUMmed the "Values"
for each key to create the "Running total" column.

key & key Values Running total
john smith 100 200
john smith 50 100
john smith 50 50
john thomas 50 50
bob jones 10 110
bob jones 100 100

I want:
john smith 100 200
john smith 50 50
bob jones 10 110
("Value" column though shown here, is not needed)

Thanks in anticipation.
 
Enter these formulae

F2: =A2
F3: =IF(ISERROR(MATCH(0,COUNTIF(F$2:F2,$A$2:$A$7&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$7),"",$A$2:$A$7),MATCH(0,COUNTIF(F$2:F2,$A$2:$A$7&"
"),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down to F7

G2: =MAX(IF($A$2:$A$7=$F2,$C$2:$C$7))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down to G7

---
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob, thanks very much, it took me some time to work out how you arrived at
this ! ...I'm just exploring Excel macros and VBA to solve a couple of
specific problems... but it's advanced me a huge step. Onward...


Bob said:
Enter these formulae

F2: =A2
F3: =IF(ISERROR(MATCH(0,COUNTIF(F$2:F2,$A$2:$A$7&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$7),"",$A$2:$A$7),MATCH(0,COUNTIF(F$2:F2,$A$2:$A$7&"
"),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down to F7

G2: =MAX(IF($A$2:$A$7=$F2,$C$2:$C$7))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down to G7

---
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
How can I extract the first entries for each key in Excel from the following
4 columns ?
Click to expand...
[quoted text clipped - 17 lines]
Thanks in anticipation.
Click to expand...
Click to expand...
 
Back
Top