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

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


"T. Valko" wrote:

The formula works.

Here's a sample file:

art.xls 14kb

http://cjoint.com/?lBdGAZWvnI

Biff

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