A Long shot ! leader board

[Blinx]

hey

so after my amazing first experience of someone answering my question id thought id ask my 2nd question but i think that this is gonna be more complex


within my data i have unique Ids

for example my title heading is ID , then i have id's such as

1000,
1002
1001
1000
1000
some appear in the same column if i need to link my accounts together

i want a leader board that shows
POSITION - NUMBER OF ACCOUNTS HAVE THIS ID - ID NUMBER

i looked on google and i believe they are arrays but this blew my head off ahaha.

i need to add new id's from time to time as well.

Is there a way i can count the number of times id 1000 appears in the column, then display this in my leaderboard, with the numbers used above
1000 would appear in position 1 , number of accounts would be 3 and then the id would be 1000

hope thisd makes sense lol

 

[nardagus]

Hello,

This? As you see I have two sheets: data with your duplicated IDs and leaderboard with organized data.

Excel 2016 (Windows) 32 bit

	A	B	C
POSITION	NUMBER OF ACCOUNTS HAVE THIS ID	ID NUMBER

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1001[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1002[/TD]

</tbody>

leaderboard​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Blinx]

Hello,

This? As you see I have two sheets: data with your duplicated IDs and leaderboard with organized data.

Excel 2016 (Windows) 32 bit

	A	B	C
POSITION	NUMBER OF ACCOUNTS HAVE THIS ID	ID NUMBER

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]1000
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1001
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1002
[/TD]

</tbody>

leaderboard​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]B2
[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C2)
[/TD]
[/TR]
[TR]
[TH]B3
[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C3)
[/TD]
[/TR]
[TR]
[TH]B4
[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C4)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

sort of

i have 3 pages
DATA -
ID LIST
FORMULAAS

data list contains my ID's
Id list i want to pre populate if i add a new ID and the cell at the side of the ID the amount of times it appears in the "data" sheet. i want this to be automatically filled in. So excel counts the number of ids in the data sheet with 1000 and then 1001 and so on and so on.

e.g
id

1000 3
1001 1
1002 3
1003 1
1004 1
1005 1
1006 3
1007 1
1008 2
1009 2

once i kno the amount of times the ID appears in the report i can then add this to a leaderboard

like ur example above but again needs to change dynamically to if a new id has more entries than others and move up and down the sheet

hope this make sense

 

[nardagus]

Do I get it right that you need a sorted list in leaderboard?

To be honest I'm not sure if this can be done without helper columns but...

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
POSITION	NUMBER OF ACCOUNTS HAVE THIS ID	ID NUMBER

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Sorted list[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1001[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1002[/TD]

</tbody>

leaderboard​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=LARGE(B:B,A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=INDEX(C:C,MATCH(LARGE(B:B,A2),B:B,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=LARGE(B:B,A3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=INDEX(C:C,MATCH(LARGE(B:B,A3),B:B,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=LARGE(B:B,A4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=INDEX(C:C,MATCH(LARGE(B:B,A4),B:B,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=COUNTIF(data!A:A,leaderboard!C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Blinx]

yea a leader board that changes dynamically . If one ID 1000 is in position 1 and been used 3 times and id 2000 is in position 2 and used 2 times. if i use id 2000 say 3 more times then this will go to position 1 as its the highest used id . Helps me keep track on what issues im facing more of u see , i also saw online ( but cannot make sense of it XD )POSITION=IF(ROWS(D$2:D2)>$B$3,"",ROWS(D$2:D2))number=IF(D2="","",LARGE(Data!$B$10:$B$546,D2))name corresponding to highest amount=IF(D2="","",INDEX(data!$A$10:$A$546,AGGREGATE(15,6,(ROW(data!$B$10:$B$546)-ROW(data!$B$10)+1)/(data!$B$10:$B$546=E2),COUNTIF($E$2:E2,E2))))this why it would be good to list all the Id in column A (auto populated ) and then auto populate the number of times its used in column (at side of the ID, automatic of course)hopefully u might be able to work out something from this. this was on youtube whilst i waiting for a reponses. it works well but only if i have the IDS listed and number of uses in 1 cell