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