Using Lookup to return all instances

[sashabaz]

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

 

[Pete_UK]

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,0)),"",INDEX(Sheet1!
A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D,0)))

B3: =IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A1),Sheet1!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

 

[sashabaz]

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,0)),"",INDEX(Sheet1!
A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D,0)))

B3: =IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A1),Sheet1!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" - points to the first formula in the main sheet

 

[Pete_UK]

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,0)),"
",INDEX(Sheet1!A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D,0)))

and this is the one for B3:

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

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

Hope this helps.

Pete

 

[sashabaz]

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]

Glad to hear that the first problem is solved - thanks for feeding
back.

As for your second post, no-one else has replied to it yet. I'll take
a look at it later, but I'm a bit tied up at the moment, so it will
have to be this evening - please check back later.

Pete

 

[Pete_UK]

Okay, the method I've come up with basically copies all the data
across from the Company List sheet (named as Comp_List on my mock-up)
to the Main sheet, so that it appears in blocks of seven rows, one for
each discipline, by 3 columns, for each company. The idea is that you
can then apply a custom autofilter to a helper column to hide all the
rows that show as blanks. I have assumed that you have this setup in
Comp_List:

Company name Disc_1 Disc_2 Disc_3 Disc_4
Q C V Q C V Q C V Q C V Q
Company_1 1 2 2
Company_2 1 1 2 2 2 1
Company_3 2 2 2

(I hope it lines up okay - spaces are not the best thing to get these
aligned). So, I have assumed that your disciplines are in row 1, and
in columns B, E, H, K etc, 3 columns apart. I've assumed that the
first set of data is on row 3. Your final column, V for discipline-7,
is in column V. The positioning of these columns is very important for
the formulae to work.

In the Main sheet I have headings in row 1 and use 5 columns - A1 is
Company name, B1 is Discipline, C1 is Quality, D1 is Cost, and E1 is
Value. I also have a header Filter in G1, leaving column F empty. This
formula is in A2:

=INDIRECT("Comp_List!R"&INT((ROW(A1)-1)/7)+3&"C1",FALSE)

The next one goes in B2:

=INDIRECT("Comp_List!R1C"&MOD(3*ROW(A1)-3,21)+2,FALSE)

And finally, put this formula in C2:

=INDIRECT("Comp_List!R"&INT((ROW(A1)-1)/7)+3&"C"&MOD(COLUMN(A1)-1 +3*
(ROW(A1)-1),21)+2,FALSE)

All one formula - be wary of spurious line breaks.

I've applied a simple Conditional Format to these cells - if the cell
value is zero then use a white font. This gives the appearance of a
blank cell if the corresponding cell in the Comp_List sheet is empty.

Copy the formula from C2 into D2 and E2, and then you can copy A2:E2
down as far as you like. You should see something like this:

Co-Name Discipline Quality Cost Value
Company_1 Disc_1
Company_1 Disc_2 1 2 2
Company_1 Disc_3
Company_1 Disc_4
Company_1 Disc_5
Company_1 Disc_6
Company_1 Disc_7
Company_2 Disc_1 1 1 2
Company_2 Disc_2
Company_2 Disc_3 2 2 1
Company_2 Disc_4
Company_2 Disc_5
Company_2 Disc_6
Company_2 Disc_7
Company_3 Disc_1 2 2 2
Company_3 Disc_2

and so on. Put this formula in G2:

=SUM(C2:E2)

and copy it down as far as you need to, and then select column G and
turn on Autofilter. From the filter pull-down in G1 you can select
Custom then Not Equal To, and 0 (zero), then click OK. You will have
the condensed report that you wanted.

Note that autofilter is not dynamic, so if you add more data to the
Comp_List sheet you will need to apply the custom filter again. If
your cell references are not the same as mine then the formulae will
need to be amended.

Hope this helps (and I hope you let me know how you get on).

Pete

 

[sashabaz]

Hi Pete,

I was hoping that there was a way of doing this without using conditional
formatting/filtering as I wanted it to run automatically and not have to
manually adjust for additional data. Is there anyway of doing it with
formulae only? I already have a table setup that holds the data in a similar
conditional formatting solution as yours, but wanted something cleaner.

Thanks.

 

[Pete_UK]

The conditional formatting is automatic - only the filtering would
need to be refreshed when you added some more data, and that is just a
matter of click the pull-down, click Custom (already highlighted if
you have applied it before) and click OK (if you have applied it
before).

I suppose you could have an event macro which did this for you
automatically whenever data was changed in the Company_List sheet.

Alternatively, someone else might come up with a formula solution for
you.

Pete