If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Question
I would like to do somethings as follows,
e.g Sheet 1 a b c d e 1 orange Apple pear grape Pineapple 2 2 3 0 0 1 Sheet 2 Orange 2 Apple 3 Pineapple 1 Is there any formula to do that? If the qty is 0 or empty, it won't appear on sheet 2. Can Excel do that? Thank you very much. |
#2
|
|||
|
|||
Question
Dear Alex
Whay you need to use is a pivot table There is a wizard that will take you through it step by step - when the wizard is running make sure you select Excel list as your data source, and then tell the wizard to put the pivot table on a new sheet. Hope this helps Paul Falla -----Original Message----- I would like to do somethings as follows, e.g Sheet 1 a b c d e 1 orange Apple pear grape Pineapple 2 2 3 0 0 1 Sheet 2 Orange 2 Apple 3 Pineapple 1 Is there any formula to do that? If the qty is 0 or empty, it won't appear on sheet 2. Can Excel do that? Thank you very much. . |
#3
|
|||
|
|||
Question
Alex,
Enter two formulas as array formulas in Sheet2 A1: =IF(ROWS(A$1:A1)COUNTIF(Sheet1!$2:$2,"0"),"", OFFSET(Sheet1!$A$1,0,SMALL(IF(Sheet1!$2:$20, COLUMN(Sheet1!$2:$2),""),ROWS(A$1:A1))-1)) B1: =IF(ROWS(B$1:B1)COUNTIF(Sheet1!$2:$2,"0"),"", OFFSET(Sheet1!$A$1,1,SMALL(IF(Sheet1!$2:$20, COLUMN(Sheet1!$2:$2),""),ROWS(B$1:B1))-1)) then drag and fill down. -- Regards, Soo Cheon Jheong Seoul, Korea _ _ ^Ά―^ -- |
#4
|
|||
|
|||
Question
Hi, Soo,
Thank a lot! But when I drag and fill down the formulas. There is some error. Example, Sheet 1, A B C D 1 Apple Orange Pear Grape 2 1 0 0 0 Sheet 2 Apple #value! Is there other way to solve this poblem. Best regards, Alex "Soo Cheon Jheong" wrote: Alex, Enter two formulas as array formulas in Sheet2 A1: =IF(ROWS(A$1:A1)COUNTIF(Sheet1!$2:$2,"0"),"", OFFSET(Sheet1!$A$1,0,SMALL(IF(Sheet1!$2:$20, COLUMN(Sheet1!$2:$2),""),ROWS(A$1:A1))-1)) B1: =IF(ROWS(B$1:B1)COUNTIF(Sheet1!$2:$2,"0"),"", OFFSET(Sheet1!$A$1,1,SMALL(IF(Sheet1!$2:$20, COLUMN(Sheet1!$2:$2),""),ROWS(B$1:B1))-1)) then drag and fill down. -- Regards, Soo Cheon Jheong Seoul, Korea _ _ ^ΒΆΒ―^ -- |
Thread Tools | |
Display Modes | |
|
|