Put this formula in D2 of Sheet1:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

and copy down as far as you think you will need it (doesn't matter if
you go beyond your data).

Then put these formula in the sheet called Art:

A3: =IF(ISNA(MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0)),"",I NDEX(Sheet1!
A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0)))

B3: =IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A1 ),Sheet1!D:D,
0)))

and the same formulae in the same cells in the Audio sheet.

Copy these down the sheets as far as you think you will need them -
again, it doesn't matter how far you copy them down.

Hope this helps.

Pete

On Aug 13, 11:19*am, sashabaz
wrote:
Ok I realise that there was a previous thread with a similar subject, but
that was a) not completely answered and b) differed in a very important
aspect so please do not point me back to that one.

Sample Data:
Sheet 1:
Client * * *Discipline * * *Quality
C1 * * * * * * * * Art * * * * * * * Good
C2 * * * * * * * * Audio * * * * * Average
C1 * * * * * * * * Audio * * * * * Poor
C4 * * * * * * * * Art * * * * * * * Good

I am trying to seperate the data above (which is taken from an outside
source) into seperate worksheets. *So there are 2 seperate worksheets for Art
and Audio. *Example, the column headings in the "Art" tables are "Client" and
"Quality" in A2 and B2. *A1 holds the word "Art".

The code that I have used in cell A2 is as follows:
=IF(COUNTIF(Sheet1!B$1:B$5,A$1)=ROWS($1:1),INDEX( Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1) )),"")

However the formula only works for a table with 5 rows. *Is it possible to
set it to look at an entire column? *(i.e. B:B instead of B$1:B$5). *I
bascially have no idea how many rows there will eventually be and I would
like to set a variable number of rows. *Is this possible?

Hope someone can help...

Thanks