Hi Pete,

Sorry, I didn't have a chance to check the reply before making my 'other'
post, but I have gone through it now and am having a slight problem. The
reason I put another post was that I need to have a similar thing but with
two input/read variables..

The first formula works fine "=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))" but
the second one results in a "#N/A". Im pretty sure I have it all down
correctly and have checked that everything is pointing to the correct
references, but can't get it right.

Just to run through it again For this formula:

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)))

A$1 and A1 - holds the discipline name in Art/Audio sheet
"Sheet1!A:A" - points to the company name column in the main sheet
"Sheet1!D:D" - points to the first formula in the main sheet

"Pete_UK" wrote:

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