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.

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

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
_ _
^¢¯^
--

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
_ _
^¢¯^
--