| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy a list and skipping blanks
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
=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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copy a list and skipping blanks
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
| Skipping cells problem | Excel Worksheet Functions | |||
| Skipping a cell and moving on to the next, with no blanks in betwe | Excel Discussion (Misc queries) | |||
| Returning list values | Excel Worksheet Functions | |||
Hybrid Mode
