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" wrote:

Try this:

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

Biff

"art" wrote in message
...
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" wrote:

Ooops!

Copy across to E2

Should say:

Copy across to E2 then down until you get blanks.

Biff

"Biff" wrote in message
...
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$70,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

"art" wrote in message
...
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?