copy a list and skipping blanks

[Guest]

Which formula do I use if I want to copy a range of cells but skiping the
blanks. For example, in column A is a list of items, and in column B is a
list of the amounts of that item (Eg. Shirts 2, 0 Pants, 5 Socks). Which
formula should I use in column C to copy column A but only these items that
have amounts in column B? Also I want to have the list without ant blanks
cells?

 

[Guest]

=IF(ISERR(SMALL(IF($B$1:$B$3>0,ROW(INDIRECT("1:"&ROWS($B$1:$B$3)))),ROWS($1:1))),"",INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3>0,ROW(INDIRECT("1:"&ROWS($B$1:$B$3)))),ROWS($1:1))))

Adjust your range to suit
ctrl+shift+enter (not just enter)

 

[Guest]

Thanks, It works great but I need to have also the amount included to the
items, for e.g. "2 shirts" "5 pants". The formula you wrote just came out the
items that have amount but not the amount included.

 

[Biff]

Assume you have:

...............A.............B
1..........Item.........Amt
2.........pants..............
3.........shoes.........10
4.........shirts..........20
5.........socks.............
6.........hats............15
7.........gloves............

Enter this formula in, say, D2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$7,">0"),INDEX(A$2:A$7,SMALL(IF($B$2:$B$7>0,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"")

Copy across to E2. The output will look like this:

................D.................E
1...........Item.............Amt
2...........shoes.............10
3...........shirts..............20
4...........hats................15
5......................................
6......................................
7......................................

Biff

 

[T. Valko]

Ooops!

Copy across to E2

Should say:

Copy across to E2 then down until you get blanks.

Biff

 

[Guest]

thanks A lot to all of you who took the time to aswer. It worked great. But I
was thinking that I might want to put the numbers toegther with the items,
E.G. "Sock 2" "pants 3".

 

[T. Valko]

Try this:

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$7,">0"),INDEX(A$2:A$7,SMALL(IF($B$2:$B$7>0,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1)))&"
"&INDEX(B$2:B$7,SMALL(IF($B$2:$B$7>0,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"")

Biff

 

[Guest]

I have some problems with the new formula you wrote, when I pull down the
formula it starts again and again like "pants 1 shirt 1 pants 1". But Before
I try to fix it, I need to know another thing. My list of items and amounts
are not in one column, they are spread apart for E.G.
A1 B1
1 "Clothing"********
2 pants 1
3 shirts 2
4 Hats
5 Socks 6
6
7 "Food"***********
8 Apple 3
9 Orange
10 Banana 2

And so on, they are all spread out in different rows and columns, so which
formula do I use to list "all" items in one long list so it should look
something like this:
Pants 1
Shirts 2
Socks 6
Apple 3
Banana 2

Thanks for all the formulas you wrote but please help me finish.

 

[T. Valko]

The formula works.

Here's a sample file:

art.xls 14kb

http://cjoint.com/?lBdGAZWvnI

Biff

 

[Guest]

Thanks a lot, I see that it works, but I also use different columns, how do I
do that?
for E.G.

A B C D
*Food* *Clothing*
1 Bread 2 pants
2 eggs socks
1 Milk 4 hats
*Frozen* *Fruit*
2 Ice cream 10 Apples
fish 5 Peaches
4 Pastry Bananas

Please answer me how to do this! Thanks!

 

[Lori]

Try Data > Filter > AutoFilter and scroll to the end of the list in a
column to select (Non Blanks).
Then you can copy the range to another sheet.

 

[T. Valko]

I don't know how to do that using a single formula (if it can in fact be
done). If anybody can do it (with a a single formula) I'd love to see it!
You could use a helper formula to reduce it from a 4 column list to a 2
column list then use the formula I've suggested to further reduce it to a
single colunm list in the format you desire.

Biff

 

[Guest]

Thanks a lot T. Valko. You're great. If you come up with a way to do that
please tell me. I'll try doing what you wrote now. Thanks.
Lori: I don't understand what you wrote. I need a formula for the list. I
don't want to do it manually.

 

[Guest]

I was thinking about it , and I think that it's not so easy to do what you
said. How can I put first all items on one long list? What did you mean by
saying that I should first put a helper formula to put all items in one long
list, how can I do that?
C'mon I'm sure you'll figure out something!

 

[T. Valko]

You have to reduce the 4 column table into a 2 column table. Then you can
extract the required data.

Sample file

art(2).xls 24kb

http://cjoint.com/?lCiOAYgaGl

As you'll see, this is not real easy!

Biff

 

[T. Valko]

Ooops!

I found a bug. I moved the range of formulas down one row and when I did
some cells didn't get overwritten as they should have.

You'll notice that in the 2 column table milk is listed twice and this also
carried over into the single column list.

To fix this, just select cell F2 and drag copy across to G2 then down to row
19. You may want to remove the outline border first.

Biff

 

[Guest]

I see what you mean, but I still have a problem. I need the list in a certain
order. I cannot have the order, 1 Bread 2 Pants..., I need it in the order I
wrote it in the list. I hope it's possible to do, because it's very important
for me to have the order that I put it in. Is there a way that the helper
formula that converts it to a two column list should first look down the
first two columns, then the 3rd and 4th column?

 

[T. Valko]

Nope!

Biff

I see what you mean, but I still have a problem. I need the list in a
certain
order. I cannot have the order, 1 Bread 2 Pants..., I need it in the order
I
wrote it in the list. I hope it's possible to do, because it's very
important
for me to have the order that I put it in. Is there a way that the helper
formula that converts it to a two column list should first look down the
first two columns, then the 3rd and 4th column?

 

[Guest]

Do you have any suggestions?

Nope!

Biff

 

[T. Valko]

Sample file:

art(3).xls 21kb

http://cjoint.com/?lCxLt6MdLU

Biff