index/match, lookup, countif ohmy!

[FreakyGirl]

hello
i have 8 columns consisting of name/thenscore (A-H). (gray section)

there are approx 60 names but most will be duplicates because players can play 1, 2 or 3 games.
the duplicate names may be in the same column (A2 & A18) or one of the others (A3 & E17)

i have used countif to give each name a unique idenitifier (columns I-P). (purple section)

here starts the problem. i need to take 1 instance of each name but get the total score of all games that player played.

i have tried with if statements & index/match but im not getting the results i need.

columns s, t, u, v show the unique names in each of the columns (I-P) but i still need to combine them into 1 list.

column s has been further modified to try for that 1 list but not working.

if someone can pls point me in the right direction of what i should be doing that im not it would be very helpful.


workbook attached.

thanks,
FreakyGirl

 

[Villalobos]

Hi,

Check this proposal (column I & J) if Ok for you.

 

[FreakyGirl]

thanks very much. i will look at it shortly & get back with you.

have a great day
FreakyGirl

 

[bosco_yip]

Try,

1] Your score values are displayed as TEXT, try to convert all text to number by :
Highlight all score value cells >> click the error button next to the cell >> click Convert to Number on the shortcut menu >> OK

2] In "Unique Game list" J2, array formula copy down :

=INDIRECT(TEXT(MIN(IF((COUNTIF(J$1:J1,$A$2:$G$28)=0)*ISTEXT($A$2:$G$28),ROW($2:$28)/1%+{1,2,3,4,5,6,7},4^8)),"R0C00"),)&""

Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER

3] In "Total Score list" K2, formula copy down :

=IF(J2="","",SUMIF(A$2:G$28,J2,B$2:H$28))

Regards
Bosco

 

[bosco_yip]

Another option by using helper cell and "Consoildate" build-in function :

1] Convert your score values from text to number (see reply#4)

2] Cell A29, formula copy across to F29

=C2

3] Select range A29:F29, all copy down until end of lines appear 0.

4] In J1 >> Data >> Consolidate :

Function : Sum

Browse : $A$1:$B$109

Use Labels in : Check "Top Row" and "Left Column"

5] Omit all helper cell

Regards
Bosco

 

[bosco_yip]

Or, using Consolidate build-in function only,

In J1 >> Data Tools >> select Consolidate :

Function : Sum

Reference :

Browse : $A$1:$B$28 click Add, and

Browse : $C$1:$D$28 click Add, and

Browse : $E$1:$F$28 click Add, and

Browse : $G$1:$H$28 click Add.

Use Labels in : Check the box of "Top Row" and "Left Column"



Regards
Bosco

 

[FreakyGirl]

Hi,

Check this proposal (column I & J) if Ok for you.

hello

this works great. lol i guess i wasnt anywhere close to that formula.
if i understand it correctly it look for a match & when there isnt one puts a 0. then you can hide the dups with the isserr.

question. A28 may not be the last row of names/scores. when i try to edit the array from A28 to say A51 Column I does not reflect the change of the added names. additionally it makes every cell change to #NAME. i did hit CTRL SHIFT ENTER but doesnt make a diff. at this point i have to close workbook & reopen to have the names back.


what am i doing wrong?
FreakyGirl

 

[FreakyGirl]

Try,

1] Your score values are displayed as TEXT, try to convert all text to number by :
Highlight all score value cells >> click the error button next to the cell >> click Convert to Number on the shortcut menu >> OK

2] In "Unique Game list" J2, array formula copy down :

=INDIRECT(TEXT(MIN(IF((COUNTIF(J$1:J1,$A$2:$G$28)=0)*ISTEXT($A$2:$G$28),ROW($2:$28)/1%+{1,2,3,4,5,6,7},4^8)),"R0C00"),)&""

Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER

3] In "Total Score list" K2, formula copy down :

=IF(J2="","",SUMIF(A$2:G$28,J2,B$2:H$28))

Regards
Bosco

i tried this & it would only show 1 name rather than all the names. i havent had a chance to try your other suggestions yet.

thank you for your input
FreakyGirl

 

[Villalobos]

hello

this works great. lol i guess i wasnt anywhere close to that formula.
if i understand it correctly it look for a match & when there isnt one puts a 0. then you can hide the dups with the isserr.

question. A28 may not be the last row of names/scores. when i try to edit the array from A28 to say A51 Column I does not reflect the change of the added names. additionally it makes every cell change to #NAME. i did hit CTRL SHIFT ENTER but doesnt make a diff. at this point i have to close workbook & reopen to have the names back.


what am i doing wrong?
FreakyGirl

Hi,

If you have names under Row28 then go to cell I2 --> extend the ranges of names in the formula --> press CSE --> copy down.

To get the scores: go to J2 --> extend the ranges of names and scores --> press Enter --> copy down.

 

[FreakyGirl]

hello

yes i did that. added in 2 names A29 & C29 with scores.

went to I2 & changed it from 28 to 100 in all places. hit CSE & all show as #NAME.

did the same to J2 & same problem.

i have attached what i see after making changes.

thanks,
FreakyGirl

 

[FreakyGirl]

bosco i cant seem to download your file. can you make it a .xls instead of .xlsx when you have time.

thanks,
FreakyGirl

 

[Villalobos]

Maybe the problem is triggered by the version of your Excel (do you have 2003 or earlier?). Some function in the formula is not exist under Excel 2007. Can you work with .xlsx files? If yes then find in attached.

 

[FreakyGirl]

yes my version is earlier than 2007. *hangs head* what i do with excel is only a hobby for me so i never found the need to upgrade.

is there a workaround for earlier versions?

_xlfn.IFERROR is the problem i believe.

thanks,
FreakyGirl

 

[FreakyGirl]

hello again.

i have got each index/match working with IF(ISERROR(

=IF(ISERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($I$1:I1,$A$2:$A$100),0))),0,INDEX($A$2:$A$100,MATCH(0,COUNTIF($I$1:I1,$A$2:$A$100),0)))
 
=IF(ISERROR(INDEX($C$2:$C$100,MATCH(0,COUNTIF($I$1:I1,$C$2:$C$100),0))),0,INDEX($C$2:$C$100,MATCH(0,COUNTIF($I$1:I1,$C$2:$C$100),0)))
 
=IF(ISERROR(INDEX($E$2:$E$100,MATCH(0,COUNTIF($I$1:I1,$E$2:$E$100),0))),0,INDEX($E$2:$E$100,MATCH(0,COUNTIF($I$1:I1,$E$2:$E$100),0)))
 
=IF(ISERROR(INDEX($G$2:$G$100,MATCH(0,COUNTIF($I$1:I1,$G$2:$G$100),0))),0,INDEX($G$2:$G$100,MATCH(0,COUNTIF($I$1:I1,$G$2:$G$100),0)))

however i am having trouble nesting them together for 1 column & not 4.

i tried to use your example with the IFERROR( but it never works.

Thanks,
FreakyGirl

 

[FreakyGirl]

hello

can anyone pls help me nest those 4 IF(ISERROR( together pls.

thanks,
FreakyGirl

 

[Chihiro]

I'd recommend going with bosco's formula. It's far easier to maintain, when compared to IF(ISERROR()) nested formula.

See attached with adjustments made to bosco's formula to match your latest attachment.

 

[FreakyGirl]

I'd recommend going with bosco's formula. It's far easier to maintain, when compared to IF(ISERROR()) nested formula.

See attached with adjustments made to bosco's formula to match your latest attachment.

hello

ty for the formula. if its not to much trouble can you explain it to me. i have looked at each section but i dont understand it.

i read up on the Indirect function so i understand that but for instance the ROW($2:$100)/1%+{1,2,3,4,5,6,7},4^8)

im out of my league here

thanks,
FreakyGirl

 

[Chihiro]

Bosco can probably explain it better. But here goes my attempt.

Lets look at smaller sample to simplify explanation.



In above case formula will become.
=INDIRECT(TEXT(MIN(IF((COUNTIF(G$1:G1,A$2:C$5)=0)*ISTEXT(A$2:C$5),ROW($2:$5)/1%+{1,2,3},4^8)),"R0C00"),)&""

Lets look at (Countif()=0) portion first.
This is checking if in A2:C5 range there is any duplicate of Column G value (G$1:G1 will grow as you drag down the formula).
In G2 it evaluates to {0,0,0,0,0,0,0,0,0,0,0,0}=0 which is converted to TRUE/FALSE array, in this case all TRUE.

Next ISTEXT()
This is simply checking if range is TEXT and evaluates to...
{TRUE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,TRUE}

The multiplication "*" between the two combines both of above to single 1/0 array.
{1,0,1;1,0,1;1,0,1;1,0,1}

Up to here is logical operation part of IF function.

Now lets look at ROW($2:$5)/1%.
This can also be written as ROW($2:$5)*100. This step is necessary to convert resulting numbers to R1C1 format of cell address.
It evaluates to
{200,300,400,500}

+{1,2,3} represents columns (A,B,C in this case) and adds that number to array of Rows and evaluates to
{201,202,203,301,302,303,401,402,403,501,502,503}

Above, is TRUE operation for IF function.

4^8 is equivalent of 4*4*4*4*4*4*4*4 = 65536.
This number is FALSE operation for If function.

Therefore combining all of above, IF function evaluates to...
{201,4^8,203,301,4^8,303,401,4^8,403,501,4^8,503}

Now we take MIN of above array, which is 201 in this case. You see that large number (4^8) is used to avoid chance of any FALSE becoming smaller than TRUE.

So lets look at TEXT() part now.
From above its TEXT(201,"R0C00").
R0C00 part formats number into R1C1 addressing "R2C01" (ie. Cell A2).

INDIRECT is used to convert string into reference to address.
Which then evaluates to value stored in the address, which is "ABC".

Hope that explained how this formula works.

 

[FreakyGirl]

Bosco can probably explain it better. But here goes my attempt.

Lets look at smaller sample to simplify explanation.
View attachment 29952

ty for taking the time to explain. it helps me to understand the individual functions.

i added in a column the beg & tried to adjust the formula based on your example. A became B etc... i also changed the column numbers from 1,2,3 etc to 2,3,4 etc.

however, after hitting CSE i get #REF error. upon looking for the error i notice that the R0C00 continues to stay at 202 no matter which row i am on.

=INDIRECT(TEXT(MIN(IF((COUNTIF(J$1:J1,$B$2:$H$100)=0)*ISTEXT($B$2:$H$100),ROW($2:$100)/1%+{2,3,4,5,6,7,8},4^8)),"R0C00"))&""

where is my mistake?

thanks,
FreakyGirl

 

[Chihiro]

You are missing the comma after "R0C00") portion.

Formula should be...
=INDIRECT(TEXT(MIN(IF((COUNTIF(J$1:J1,$B$2:$H$100)=0)*ISTEXT($B$2:$H$100),ROW($2:$100)/1%+{2,3,4,5,6,7,8},4^8)),"R0C00"),)&""

To explain this portion, it's an argument for =INDIRECT(String,[A1])
If you leave out the comma, it defaults to [A1] type cell reference instead of [R1C1] type reference. Thus giving you the #REF error.

Additionally, &"" portion is there to allow formula to return blank instead of 0 when it has run out of unique strings.

 

[bosco_yip]

....evaluates to...
{201,4^8,203,301,4^8,303,401,4^8,403,501,4^8,503}
Now we take MIN of above array, which is 201 in this case. You see that large number (4^8) is used to avoid chance of any FALSE becoming smaller than TRUE.

Thank you, Chihiro,

Very nice explanation, and one point to note :

1] 4^8 is not used being the largest number of the MIN array.
The formula can work well without 4^8, just give #REF! at the last unique list results.

2] 4^8 is working together with &"" and in the purpose of Error Control.

In the example, you can check with the formula used in G7

=INDIRECT(TEXT(65536,"R0C00"),)&""

=INDIRECT("R655C36",)&""

R655C36 equal to AI655, and AI655 is generally a blank cell

=0&""

=Blank

3] In Excel 2007 or above, can use IFERROR instead of 4^8 and &"" for the Error Control.

{=IFERROR(INDIRECT(TEXT(MIN(IF((COUNTIF(G$1:G1,A$2:C$5)=0)*ISTEXT(A$2:C$5),ROW($2:$5)/1%+{1,2,3})),"R0C00"),),"")}

Remark : you can use other large number instead of 4^8, and ensure that cell location out of your table range and in blank.

Regards
Bosco

 

[FreakyGirl]

hello again

ty for that fix chihiro. i should have seen that myself when i compared the 2 formulas.

bosco ty for your insight as well. very informative.

it worked great until i linked the worksheet this will get the names from together.

in the unique player list it shows the 1st name, then instead of the 2nd name it shows the score instead of the next name.

figuring it was the number fromat - i have gone thru both worksheets & every column that is a number that i will need to refer to i have right clicked, then clicked format cells & changed to number format.

however, the cells are still showing left aligned & even more perplexing - some of them are working correctly.

the total score has stopped working as well but i beleive thats because of the unique list problems.


can you please guide me on the fix for this & then i believe it will be good to go.

thanks,
FreakyGirl

 

[Chihiro]

Since you are pulling the number using formula. Change your formula to following for Score Columns.
L2: =VALUE(code!CO2)
Copy and paste to other ranges.

 

[FreakyGirl]

hello

thanks so much.

sorry for delay in ansering but weekends are a tough time for me to get here

i will let you know if any more problems.

have a great day all
FreakyGirl

 

[Chihiro]

No worries. We all got our jobs and family obligation