How can I extract each Max key value ?
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 |
How can I extract each Max key value ?
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 |
How can I extract each Max key value ?
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 |
All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com