condense List

[Lita]

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?

 

[Otto Moehrbach]

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]

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.

 

[Lita]

and Sheet2 can be locked to editing

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]

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

 

[Shane Devenshire]

Hi,

Try this formula:

=IF(ROW(A1)>ROWS($A$1:$A$12),"",IF(LARGE(ROW(Sheet5!$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