Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
I'm trying to see if there is a formula to use to condense a list. Quickest
example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
Lita
Select the entire range of your data. Click on Data - Filter - AutoFilter. A down-arrow will appear to the right of each header. Click the down-arrow in the Qty header. One of the options available is "Non-Blanks". Click on it. That hides all the blank Qty rows. Copy everything and paste it to the next sheet. Back on your first sheet, click on Data - Filter - Autofilter to make everything visible again. HTH Otto "Lita" wrote in message ... I'm trying to see if there is a formula to use to condense a list. Quickest example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
Is there a way that it can be done automatically? The point of the sheet is
to have multiple selections and Sheet2 to be a summary page. "Otto Moehrbach" wrote: Lita Select the entire range of your data. Click on Data - Filter - AutoFilter. A down-arrow will appear to the right of each header. Click the down-arrow in the Qty header. One of the options available is "Non-Blanks". Click on it. That hides all the blank Qty rows. Copy everything and paste it to the next sheet. Back on your first sheet, click on Data - Filter - Autofilter to make everything visible again. HTH Otto "Lita" wrote in message ... I'm trying to see if there is a formula to use to condense a list. Quickest example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
and Sheet2 can be locked to editing
"Lita" wrote: Is there a way that it can be done automatically? The point of the sheet is to have multiple selections and Sheet2 to be a summary page. "Otto Moehrbach" wrote: Lita Select the entire range of your data. Click on Data - Filter - AutoFilter. A down-arrow will appear to the right of each header. Click the down-arrow in the Qty header. One of the options available is "Non-Blanks". Click on it. That hides all the blank Qty rows. Copy everything and paste it to the next sheet. Back on your first sheet, click on Data - Filter - Autofilter to make everything visible again. HTH Otto "Lita" wrote in message ... I'm trying to see if there is a formula to use to condense a list. Quickest example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
Lita
You say you want it to happen automatically. What do you want to use as the trigger to make it happen? Click a button? Make a selection? Make a cell entry? Choose from a drop-down list? Something else? You say the point of the sheet is to have multiple selections and Sheet2 to be a summary page. I realize that you don't want to give specifics as to your overall data, so just use generic terms. Multiple selections of what? How do you want to make this "selection"? Do want to type it in a cell? Choose from a drop-down list? Click on a cell that contains the desired selection? Something else? HTH Otto "Lita" wrote in message ... Is there a way that it can be done automatically? The point of the sheet is to have multiple selections and Sheet2 to be a summary page. "Otto Moehrbach" wrote: Lita Select the entire range of your data. Click on Data - Filter - AutoFilter. A down-arrow will appear to the right of each header. Click the down-arrow in the Qty header. One of the options available is "Non-Blanks". Click on it. That hides all the blank Qty rows. Copy everything and paste it to the next sheet. Back on your first sheet, click on Data - Filter - Autofilter to make everything visible again. HTH Otto "Lita" wrote in message ... I'm trying to see if there is a formula to use to condense a list. Quickest example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
condense List
Hi,
Try this formula: =IF(ROW(A1)ROWS($A$1:$A$12),"",IF(LARGE(ROW(Sheet 5!$C$1:$C$12)*(Sheet5!$C$1:$C$12<""),ROW(A1))=0," ",INDEX(Sheet5!$A$1:$C$12,LARGE(ROW(Sheet5!$C$1:$C $12)*(Sheet5!$C$1:$C$12<""),ROW(A1)),COLUMN()))) It assumed that you need to enter this in cell A1 on the second sheet and that your data on the first sheet starts in cell A1 and that the quantity is in column C. Your range on the first sheet can extend down as far as you want. This is an array formula, you must enter it by pressing Ctrl+Shift+Enter. If this helps, please click the Yes button Cheers, Shane Devenshire "Lita" wrote: and Sheet2 can be locked to editing "Lita" wrote: Is there a way that it can be done automatically? The point of the sheet is to have multiple selections and Sheet2 to be a summary page. "Otto Moehrbach" wrote: Lita Select the entire range of your data. Click on Data - Filter - AutoFilter. A down-arrow will appear to the right of each header. Click the down-arrow in the Qty header. One of the options available is "Non-Blanks". Click on it. That hides all the blank Qty rows. Copy everything and paste it to the next sheet. Back on your first sheet, click on Data - Filter - Autofilter to make everything visible again. HTH Otto "Lita" wrote in message ... I'm trying to see if there is a formula to use to condense a list. Quickest example would be a shopping list. the sheet1 is as follows: Desc. Name Qty. Ice Tea Minute Maid Milk Daily Farms Cookies Chip Ahoy Veggies Carrots What I would like to happen is if in Sheet1 there is a quantity next to an Item, have that entire row appear on sheet2. Sheet2 would then give me a result of just items with quanties. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condense out blanks in a list | Excel Discussion (Misc queries) | |||
Condense list (remove blanks) | Excel Worksheet Functions | |||
How do I condense 3 columns into 1 in Excel? | Excel Worksheet Functions | |||
Condense formula | Excel Worksheet Functions | |||
Help to condense a formula | Excel Worksheet Functions |