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.

--
Message posted via http://www.officekb.com

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)

"diglas1 via OfficeKB.com" <u22313@uwe wrote in message
news:61085cface8e5@uwe...
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.

--
Message posted via http://www.officekb.com

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 Phillips wrote:
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 ?
[quoted text clipped - 17 lines]

Thanks in anticipation.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200605/1