Moving Information

[Anupkumar]

I have a "shopping list" which a user fills out. I need that information to move to a seperate sheet only if the quantity is changed from a 0 (zero) to a 1 or higher.

For example:

Item Qty Total
Chair 0 0
Table 1 50

I need the information with regards to the table to move to a seperate sheet. (It needs to include the name(table) quantity (1) and Total (50))

As the chair quantity remains as 0 I do not want that information on the seperate sheet.

Any assistance will be appreciated!

[Max]

One play which might suffice ..

Assume source table below is in sheet: X, cols A to C,
data from row2 to a max expected row100 (say)

> Item Qty Total
> Chair 0 0
> Table 1 50

In another sheet: Y (say)
with the same col headers in A1:C1, viz.: Item, Qty, Total

Put in A2:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

Put in D2: =IF(X!B2>0,ROW(),"")
(Leave D1 empty)

Then select A2:D2, fill down to D100
(cover the max expected extent of data in X)

Cols A to C will return the required results from X,
ie only the lines with Qty > 0,
with all results neatly bunched at the top

(Hide away the criteria col D, if desired)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Anupkumar" wrote:
>
> I have a "shopping list" which a user fills out. I need that information
> to move to a seperate sheet only if the quantity is changed from a 0
> (zero) to a 1 or higher.
>
> For example:
>
> Item Qty Total
> Chair 0 0
> Table 1 50
>
> I need the information with regards to the table to move to a seperate
> sheet. (It needs to include the name(table) quantity (1) and Total
> (50))
>
> As the chair quantity remains as 0 I do not want that information on
> the seperate sheet.
>
> Any assistance will be appreciated!
>
>
> --
> Anupkumar
> ------------------------------------------------------------------------
> Anupkumar's Profile: http://www.excelforum.com/member.php...o&userid=35152
> View this thread: http://www.excelforum.com/showthread...hreadid=549173
>
>

[Anupkumar]

What would be the formula to rather than referencing a cell but make it go on the line below. For example:

The list below is the items available to order:
Chair 1 $100
Table 0 $0
PC 2 $500

Below is the final out come as it currently stands:

Chair 1 $100

PC 2 $500

I do not want the empty line between the two items. I need the PC to go directly under the Chairs.

Thanks once again!

[Max]

"Anupkumar" wrote:
> .. I do not want the empty line between the two items.

The above won't happen if the suggested construct was implemented correctly.
Try it again ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Andy]

"Max" wrote:

> "Anupkumar" wrote:
> > .. I do not want the empty line between the two items.
>
> The above won't happen if the suggested construct was implemented correctly.
> Try it again ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Yes this is true. It does not happen when I input the formula above.
Although it still includes the items with the 0 quantity, along with placing
it in order. I unfortunatly do not want it in chronological order. The items
are listed in a specific order on the "shopping list" and need to maintain
that order, if that makes sense. I am just trying to change the above formula
to not place the items in order, along with making it not pick up the items
with 0 quantity.

Thank you for all of your help!!!

[Max]

"Andy" wrote:
> Yes this is true. It does not happen when I input the formula above.
> Although it still includes the items with the 0 quantity, along with placing
> it in order. I unfortunately do not want it in chronological order. The items
> are listed in a specific order on the "shopping list" and need to maintain
> that order, if that makes sense. I am just trying to change the above formula
> to not place the items in order, along with making it not pick up the items
> with 0 quantity.

Not sure what's happening over there <g>,
but here's a sample construct to illustrate:
http://www.savefile.com/files/6089699
Extract Lines with Qty more than zero in other sht

> Although it still includes the items with the 0 quantity, along with placing
> it in order. I unfortunately do not want it in chronological order...

The results extracted in Y do not include items with zero quantity (this is
the criteria !), and extracted items are in the same relative order that they
appear originally in X (there is no chronological ordering)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Andy]

Firstly I would like to thank you Max for your assistance! We are now trying
to take this one step further. I have numerous departments which will be
completing the workbook, also the workbook has numerous worksheets such as
Furniture, Equipment, Computers etc. Once they have completed all the
different worksheets and the information is processed using the formula
provided above, and I have all the items ordered on a seperate worksheet, I
need to group the information. An example would be:
Administration completing the "shopping list" and Marketing completing their
"shopping list". I need to place all the Furniture ordered by the two
departments on one Final worksheet and all the Equipment ordered on to one
worksheet.

(I hope you guys get the idea)
Basically I have tried copying and pasting the information but it does not
work. I have tried to tweak the formula by changing the A1 to A80 and its not
working because the cells are not matching up I guess. (A80 is where the
information for the second department would start.)

Thank you guys once again!



"Max" wrote:

> "Andy" wrote:
> > Yes this is true. It does not happen when I input the formula above.
> > Although it still includes the items with the 0 quantity, along with placing
> > it in order. I unfortunately do not want it in chronological order. The items
> > are listed in a specific order on the "shopping list" and need to maintain
> > that order, if that makes sense. I am just trying to change the above formula
> > to not place the items in order, along with making it not pick up the items
> > with 0 quantity.
>
> Not sure what's happening over there <g>,
> but here's a sample construct to illustrate:
> http://www.savefile.com/files/6089699
> Extract Lines with Qty more than zero in other sht
>
> > Although it still includes the items with the 0 quantity, along with placing
> > it in order. I unfortunately do not want it in chronological order...
>
> The results extracted in Y do not include items with zero quantity (this is
> the criteria !), and extracted items are in the same relative order that they
> appear originally in X (there is no chronological ordering)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

"Andy" wrote:
> Firstly I would like to thank you Max for your assistance!

Welcome, Andy !

> We are now trying to take this one step further....

You could hang around awhile for responses from others. But I would suggest
you put your new query in as a new post, for better radar/exposure to all the
responders out there. This thread is quite dated & deep ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---