Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 On Aug 14, 12:22*pm, sashabaz wrote: 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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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 -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 On Aug 14, 12:22*pm, sashabaz wrote: 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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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 -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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" wrote: 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 On Aug 14, 12:22 pm, sashabaz wrote: 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 -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lookup to return all instances
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 On Aug 17, 3:46*pm, sashabaz wrote: 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" wrote: 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 On Aug 14, 12:22 pm, sashabaz wrote: 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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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)),"", INDEX(Sheet1! A:A,MATCH(A$1&"_"&ROW(A1),Sheet1!D:D,0))) B3: * *=IF(A3="","",INDEX(Sheet1!C:C,MATCH(A$1&"_"&ROW(A 1),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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula to return all instances of match? | Excel Discussion (Misc queries) | |||
Multiple instances of lookup value | Excel Discussion (Misc queries) | |||
How to return multiple instances using VLOOKUP | Excel Worksheet Functions | |||
Lookup function for a value that has multiple instances | Excel Worksheet Functions | |||
Returning multiple instances of the same lookup value | Excel Worksheet Functions |