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?