Couple of things

[TVSHOWLISTER]

Hi, this is abit complicated to explain what i have in mind here, so pictures where needed im sorry if i broke the rules here and im sorry if you cant understand what im trying to do here, its kinda hard for me to work out myself, but im looking just to make this some what easier for me maybe more automated? IDK also sorry if this is confusing im not good at explaining excel :S

I have a couple of things im looking to achieve in excel i need help with


Firstly its for a fun league that im running which i dont think needs explaining from the picture above heh , which i hope to be added more characters too as i continue playing

secondly in the columns count player 1 and player 1 im looking for that to go off of an expanding range in a list from sheet6, also in count player 2/player 2 im looking for a count if or something better then count in a specific order which is also an expanding range for example Terry Bogard is soon to face Usopp which im looking to add that to a formula to get it as 9 in count player 2, so Terry Bogard, Vegeta would move to 10, and Terry Bogard, Usopp would be 9 i hope that makes sense, while count player 1 column would be 17 so the new entry would like like

Count Player 1 Player 1 Player 2 Count Player 2
17 Terry Bogard Vegeta 10
17 Terry Bogard Usopp 9 (new entry, formulas need updating))
17 Terry Bogard Sasuke Uchiha 8

Etc


here is an example of Sheet 6

In the table from A:C in Column A is where the expanding range is going to be for sheet 4 (Count Player 1), its where ill be adding new characters to and trying to work of a specific ordering, also in columns G:L, im trying to work out how to number it with a countif statement which i have successfully done (in column L) however only thing i need for that change is to make it from largest to smallest (IE Terry Bogard, Goku 1, Terry Bogard, Kakashi Hatake, 2) etc not Terry Bogard, Vegeta 1, Terry Bogard Sasuke Uchiha 2 and again i need this to be an expanding range so Terry Bogard will be facing more challengers etc in this order this is for Count Player 2 in sheet 4

What im aiming to achieve here is, if i add a new entry in the table columns, IE (Nick Fury) which would shift ordering abit again cus its a new entry in column b also so it would look like

11 Marvel Nick Fury
12 snk Andy Bogard


Another Example would be

11 DC Bruce Wayne
12 Marvel Nick Fury
13 Snk Andy Bogard
14 Snk Geese Howard
15 Snk Iori Yagami
16 Snk Joe Higashi
This would also affect count player 1 (Joe Higashi for example currently reads 14, that would shift him to 16, back in sheet 4, count player 1)

Im sorry about the lengthy probably confusing explanation

 

[jasonb75]

With such questions it would probably be better to post one part at a time with a smaller data set that is easier to follow. Also, using XL2BB to post your samples is considerably better than screen captures as we can copy and paste straight to excel instead of retyping everything.

I'm not sure if I'm barking at the proverbial wrong tree, of for that matter if I'm even in the same forest but thought I's give it a go anyway.

Looking at the first part of your post and the first image, it is my understanding that you're looking for a way to generate the player counts by formula as the details are entered. I've shown what I think is the simplest method that could be used for this, however it should be noted that because the first formula in the player 1 count column is different to the rest, sorting the data or inserting a new row at the top could mess things up.

Book1
	A	B	C	D
1	Player 1	Count P1	Player 2	Count P2
2	Terry	3	A	4
3	Terry	3	B	3
4	Terry	3	C	2
5	Terry	3	D	1
6	Shingo	2	B	3
7	Shingo	2	D	2
8	Shingo	2	A	1
9	Kyo	1	C	5
10	Kyo	1	A	4
11	Kyo	1	B	3
12	Kyo	1	E	2
13	Kyo	1	D	1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(D:D,1)
B3:B13	B3	=B2-(A2<>A3)
D2:D13	D2	=IF(A2=A3,D3+1,1)

Is that even anywhere close to what you need?

 

[TVSHOWLISTER]

The If Statement in column D (Count Player 2) worked like a charm thank you , i enter new data at the bottom so i dont think ill have a problem...

Edit: One thing i noticed however about that if statement is it worked for what was currently in the data set, however when i added new entries it messed up my specific sorting method that i was doing manually

Also

I Had trouble with Column A part, of your answers, since i do go off of another sheet, to update new entries here, its fine for right now ive been manually changing the formula ive been using which is

=Sheet6!A$4=(18) ie =Sheet6!A$5) (19) (For New entries example)

KYO KUSANAGI

But i have to manually update that formula in sheet4 when i add new entries to the table in sheet 6

 

[TVSHOWLISTER]

Update

I was able to use the getpivot table data to help me automatically count for count player 1, however i run into an obstacle when i add new entries in sheet6, where my pivottable is following it comes up with a #ref when adding new characters (New brands) for example any characters with the SNK brand come up with a #ref if i add in a new catagory for example Marvel

Ive also thought about trying something similar with Count Player 2, but again same problem and abit harder to control

AND This is what happens to sheet4, when i make a new entry

like this

I guess my question here is , how do i make this more dynamic so i dont have to go and manually readjust whats going on in sheet4 with #ref, because i added a new entry into sheet6 table, i want to make it more dynamic, then i can use this for count player 2 also

sorry about all the pictures