Question

[Guest]

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.

 

[Paul Falla]

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.

 

[Soo Cheon Jheong]

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:$2>0,
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:$2>0,
COLUMN(Sheet1!$2:$2),""),ROWS(B$1:B1))-1))

then drag and fill down.

--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Guest]

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