Formula to count the number of times a number appears in a row within in a text cell

[Chi]

Hi

I've attached a sample data, on the 'Data' tab, which shows in each cell a person's name, in short form or code, and a number, in bold, which denotes a placing number for each pairing of names.

Beside each pairing, highlighted in pink, shown on the 'Pairs' tab, I wanted a formula to show the total number of times that they came 1=1st, 2=2nd, 3=3rd only, ignoring the 0=no placings.

Just so you know, each person's name for each placing is listed with their own unique code on the 'Codes' tab which I would like to be incorporated in the formula. The reason being is that the listed names on the attached sample data only shows a small list of names and the formula(s) will eventually be used in a data with a vast number of names being approximately over 300 names.

So I guess using Countif formula would not be suitable for this vast data.

I hope this all makes sense and let me know if you have any queries about the attached data.

Kind regards
Chi

[navic]

Try on 'Pairings' sheet
In A2 formula below

=SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;3;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;4;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;5;FALSE)))

In C2 Array formula formula below

=IF(ROWS(Data!A$2:A2)>Codes!$D$1*COUNTIF(Data!$A$2:$A$100;"="&"?*");"";INDEX(Data!$A$2:$A$100;SMALL(IF(Data!$A$2:$A$100<>"";ROW(Data!$A$2:$A$100)-ROW(Data!$A$2)+1);MOD(ROWS(Data!A$2:A2)-1;COUNTIF(Data!$A$2:$A$100;"="&"?*"))+1)))

In D2 formula below

=IFERROR(INDEX(Codes!$C$2:$C$29;INT((ROW(Data!A1)+4)/COUNTIF(Data!$A$1:$A$100;"*?")));"")

I do not understand the logic of calculation in column B (%)?

Try on 'Codes' sheet
There are 'helper' columns on 'Codes' worksheet

In C2 Array formula formula below

=IFERROR(INDEX($B$2:$B$29;MATCH(0;INDEX(COUNTIF($B$2:$B$29;"<"&$B$2:$B$29)-SUMPRODUCT(COUNTIF($B$2:$B$29;C$1:C1)););0));"")

In D1 formula below

=COUNTA(C2:C29)-COUNTBLANK(C2:C29)

In D2 Array formula formula below (copy across)

=IFERROR(INDEX($A$2:$A$29;MATCH(0;COUNTIF($C2:C2;$A$2:$A$29)+IF($B$2:$B$29<>$C2;1;0);0));"")

[caabyyc]

In "Pairings" tab,

H2=SUMPRODUCT((Data!$A$2:$A$6=C2)*(ISNUMBER(FIND(LEFT(D2,2)&" "&RIGHT(D2,1),Data!$B$2:$O$6)))*(RIGHT(Data!$B$2:$O$6,1)-0>=1)*1) copy down, the result matches current result in column A.

Hi

I've attached a sample data, on the 'Data' tab, which shows in each cell a person's name, in short form or code, and a number, in bold, which denotes a placing number for each pairing of names.

Beside each pairing, highlighted in pink, shown on the 'Pairs' tab, I wanted a formula to show the total number of times that they came 1=1st, 2=2nd, 3=3rd only, ignoring the 0=no placings.

Just so you know, each person's name for each placing is listed with their own unique code on the 'Codes' tab which I would like to be incorporated in the formula. The reason being is that the listed names on the attached sample data only shows a small list of names and the formula(s) will eventually be used in a data with a vast number of names being approximately over 300 names.

So I guess using Countif formula would not be suitable for this vast data.

I hope this all makes sense and let me know if you have any queries about the attached data.

Kind regards
Chi

[Pecoflyer]

@caabyyc
Please don't quote whole posts -- it's just clutter and makes the thread hard to read. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[Chi]

@navic - The formulas you provided produced the results I wanted. Thank you very much for your time. Much appreciated!

[Chi]

@caabyyc - Unfortunately, the formula you provided only looks at the data and pairing of names. In order to create the results I needed you would have to factor in the short codes on the 'Codes' tab but you didn't include this in your formula.

However, I thank you for taking the time to look into this.

[navic]

Thank you very much for your time.

You're welcome.