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?

=IF(ISERR(SMALL(IF($B$1:$B$30,ROW(INDIRECT("1:"&R OWS($B$1:$B$3)))),ROWS($1:1))),"",INDEX($A$1:$A$3, SMALL(IF($B$1:$B$30,ROW(INDIRECT("1:"&ROWS($B$1:$ B$3)))),ROWS($1:1))))

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


"art" wrote:

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?

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.

"Teethless mama" wrote:

=IF(ISERR(SMALL(IF($B$1:$B$30,ROW(INDIRECT("1:"&R OWS($B$1:$B$3)))),ROWS($1:1))),"",INDEX($A$1:$A$3, SMALL(IF($B$1:$B$30,ROW(INDIRECT("1:"&ROWS($B$1:$ B$3)))),ROWS($1:1))))

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


"art" wrote:

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?

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?

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?

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?

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?

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?

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?

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?

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.


art wrote:

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?

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?

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.


"T. Valko" wrote:

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?

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

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?

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

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

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?

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

"T. Valko" wrote in message
...
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

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

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?

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

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

"T. Valko" wrote in message
...
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

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

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?

Nope!

Biff

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

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

"T. Valko" wrote in message
...
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

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

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?

Do you have any suggestions?

"T. Valko" wrote:

Nope!

Biff

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

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

"T. Valko" wrote in message
...
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

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

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?

Sample file:

art(3).xls 21kb

http://cjoint.com/?lCxLt6MdLU

Biff

"art" wrote in message
...
Do you have any suggestions?

"T. Valko" wrote:

Nope!

Biff

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

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

"T. Valko" wrote in message
...
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

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

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?

Thanks, I'll try using that, but it's still not so good, 'cause A. I have
more then just 4 columns, I use about 12-16 columns. B. If I use your method
for all colmuns, means that I will have to do this formula for the whole
length of each column.

"T. Valko" wrote:

Sample file:

art(3).xls 21kb

http://cjoint.com/?lCxLt6MdLU

Biff

"art" wrote in message
...
Do you have any suggestions?

"T. Valko" wrote:

Nope!

Biff

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

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

"T. Valko" wrote in message
...
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

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

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?