I'm hoping somebody here knows more about excel than I do, sorry to post technical questions not related to admissions, but I could really use some help here.
I have a very large data sets that I'm working with in excel 2007 and I have to continually filter and unfilter the data by groups. I want to know if there's a way that I can have the values from my formula update themselves each time I filter/unfilter the data.
Suppose I have a table like this:
Group | Age | Rank | Gender
A 15 1 M
A 5 2 F
A 1 3 F
B 12 1 M
B 3 2 M
B 2 3 F
Where there are two groups, A and B, and each person has an age, and then the third column we have a ranking. This ranking in third column is computed by sorting the data by age and then by group, and then we set the first entry to 1 and then every other entry has an "if" equation where if the group is the same as the previous column then we add 1 to the ranking, and if not then we set it equal to 1.
Now say that just as a thought experiment we want to only look at females and have the rankings for females. If you do a basic filter and select only show girls what you get is:
Group | Age | Rank | Gender
A 5 2 F
A 1 3 F
B 2 3 F
However, what I want is:
Group | Age | Rank | Gender
A 5 1 F
A 1 1 F
B 2 2 F
Is there anyway to make the formula update like this, so it treats what we see as the first row of data as if it were actually the first row, instead of still the second row with the first row "invisible" but existent?
I know it's easy to do this in a one time thing through an advanced filter or simply deleting the rows I don't want; but I need to do literally hundreds of filters and don't want to delete anything. For example right after I look at only the female data I may want to do the same thing only looking at just the male data. Is there any easy way to do this?
I know you can have macro's work off relative position instead of absolute, is there anyway for formulas/filters to do this?
Thanks in advance!
Bookmarks