On Mar 25, 9:30 pm, Caroline <Carol...@discussions.microsoft.com>
wrote:

Is it possible to add an extra column on the "list-sheet"?
If so you could add a column which counts how many times each state i
represented.
I've simplified somewhat and made a list that look like this:
Name State Count
John   CA     =COUNTIF(B$2:B;B2) and copy down.
Mary   CA    2

Then A2 on the CA sheet would look like this:
=INDEX(Sheet1!$A$2:$A$36;MATCH("CA"&ROW()-1;Sheet1!$B$2:$B$36&Sheet1!$C
$2:$C$36;0))
Note this is an array formula and should be closed with ctrl+shurt
+enter. Then you will get {} around it.

BTW if this was confusing, don't worry I am sure someone comes up with
a much more clever solution pretty soon.

Per Erik

On the sheet where you have the data, assuming that it is "Sheet1" and you
have names in column A and state in Column B.
On the California sheet, you have state name in..say....A2.

put the following in the cell where you want the first name to appear.

=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$100,A$1),INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!B$2:B$100=A$1,ROW(Sheet1!A$2:A$100)-MIN(ROW(Sheet1!A$2:A$100))+1),ROWS(A$2:A2))),"")

Press CTRL+SHIFT+ENTER and drag it all the way down.

hope this helps.

Thanks
Gaurav

Or use Pivot Tables.
No formulas needed.
Automatic update.
http://www.freefilehosting.net/download/3e67k