As columns adjust largest to smalles, how to keep other formulas intact.

[DrReph]

I have a spreadsheet and it has data which includes starting number, ending number, amount difference, and % change. the group consists of about 40 members each with these pieces of information.

I know how to make the columns adjust for high to low, or low to high and also have that information follow the person it is attached to as it is supposed to. However within the 40 people there are random people who I wanted to group together.

so paul, thomas, and jacob are on a "team" but they are not next to each other in the spreadsheet. The problem i am having is when i have the columns adjust for hightest to lowest, the groupings get messed up because it is based upon adding the cells together. so when i update Paul may move from that cell and another person will enter the cell. how do i adjust the groupings to make the cells in the formula follow the name of the person and not stay with the specific cell they were in previously?

[soberguy]

Can you post a sample of the data without any identifying information? I'm sure we can figure something out.

[DrReph]

i have attached the file.

My goal was to have those people with each different color have a running total for their group.

but when i adjust their totals and put them in numerical order the grouping equation that i was using for each group is then wrong, because the information that it was attached to has moved.

[soberguy]

Is B, the affiliation column, related to their group? If not, would you be opposed to a group column? If it is the Group column, what should we do with those who are blank?

[DrReph]

yes it was the name of their group and i was also using it as an information column, (its an online game, so i wanted to try to keep them organized ). The people who are named in coumn B are the people who play more often and they are actually grouped together as family members or alternate accounts. Those who are blank are by themselves.. so they could be named something else if that would fix the problem.

[soberguy]

Won't change a thing!

So down in C50, you could have something like:
=Countif(B2:B42,"Sids")

And then adjust that for each House or group.

D50 would look like:
=C50/counta($A$2:$A$42)

Copy and paste that down D colum.

D58 would look like:
=Countif($D$2:$D$42,A58)

Copy and paste down D.

F50 would be:
=Sumif($B$2:$B$42,"Sids",C$2:C$42)

Adjust that for each House or group. Note that if you change the Affiliations in B to be an exact match of what's down in A50-55 you can do:
=Sumif($B$2:$B$42,$A50,C$2:C$42)

F can be copied over to G. I and K won't change.

Then some of the above can be tweaked a little to go by Rank (D) instead of House (B).

Make sense?

[DrReph]

thank you for your help i will try that and see what happens.!