Hi Pete,

Can't seem to reply properly on the site. I have got those formulae working
and am trying to move onto my other post. Basically the issue I have is
this. I have sent up an averaging table that counts all the ratings given
for each discipline for each company. Some companies may not do certain
disciplines, but the options are still there....

My table columns look like this:

Company name - Art - Audio - etc.... (there are 7 discipline in all and each
discipline has 3 columns beneath with quality - cost - value)

Each Company is listed in this sheet (call it Company List). I want a
formula that searches through each company and discipline, only listing the
instances where they have data in them.

i.e.

Company Name - Art - Audio
Q - C - V Q - C - V
Company a 1 - 2 - 2


This transfers to the Main sheet as:

compnay Name - Discipline - Q - C - V
company a - Art - 1 - 2 - 2


Is this possible? I can't seem to figure out how to adjust the formula
correctly.

Thanks

"Pete_UK" wrote:

The newsreaders often insert line breaks at awkward places in long
formulae, so here is the first one in A3 which I have manually broken
to get it to read better:

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

and this is the one for B3:

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

Both worked fine in my test setup before I posted them to you.

Hope this helps.

Pete

On Aug 14, 9:53 am, sashabaz
wrote:
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- Hide quoted text -

- Show quoted text -