Copying data

I have created a price list and would like to generate an order form from
that liston a separate worksheet. Is there a way of copying an entire row if
the column has a figure in the total column? The rows with a 0 total would
not need to be copied
Thank you

Hi Jules

http://www.rondebruin.nl/copy5.htm

For a Add-in solution see
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"jules" <[email protected]> wrote in message news:[email protected]...
>I have created a price list and would like to generate an order form from
> that liston a separate worksheet. Is there a way of copying an entire row if
> the column has a figure in the total column? The rows with a 0 total would
> not need to be copied
> Thank you

Thank you very much for your prompt reply.
Best wishes
Jules

"Ron de Bruin" wrote:

> Hi Jules
>
> http://www.rondebruin.nl/copy5.htm
>
> For a Add-in solution see
> http://www.rondebruin.nl/easyfilter.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "jules" <[email protected]> wrote in message news:[email protected]...
> >I have created a price list and would like to generate an order form from
> > that liston a separate worksheet. Is there a way of copying an entire row if
> > the column has a figure in the total column? The rows with a 0 total would
> > not need to be copied
> > Thank you
>
>
>

Good morning. I just have one other thing to ask you - what do i do with the
formula you wrote? It looks like ancient greek!!

"jules" wrote:

> Thank you very much for your prompt reply.
> Best wishes
> Jules
>
> "Ron de Bruin" wrote:
>
> > Hi Jules
> >
> > http://www.rondebruin.nl/copy5.htm
> >
> > For a Add-in solution see
> > http://www.rondebruin.nl/easyfilter.htm
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "jules" <[email protected]> wrote in message news:[email protected]...
> > >I have created a price list and would like to generate an order form from
> > > that liston a separate worksheet. Is there a way of copying an entire row if
> > > the column has a figure in the total column? The rows with a 0 total would
> > > not need to be copied
> > > Thank you
> >
> >
> >

Perhaps you'd also like to play around
with this formula approach as well ?

Assume you have the table below in Sheet1,
col headers in row1, data in row2 down
where the key "TotalQty" col is in col B

Item TotalQty <etc>
1111 1
1112 0
1113 1
1114 0
1115 1
etc

In an empty col to the right, say col K?
Put in K2: =IF(B2<>0,ROW(),"")
Copy K2 down by as many rows as data is expected
in the table, say, down to K1000?
(can copy down ahead of expected data input)

In Sheet2
----
With the same col headers in row1:
Item TotalQty <etc>

Put in A2:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$K:$K,ROWS($A$1:A1)),Sheet1!$K:$K,0)))

Copy A2 across by as many cols as there is in the table in Sheet1, then fill
down by as many rows as was done in col K in Sheet1, i.e. down by 1000 rows

Sheet2 will return only the rows from the table in Sheet1 where the
"TotalQty" col is <> 0, and doing so without any "blank" rows in-between

For the sample data given, you'll get:

Item TotalQty <etc>
1111 1
1113 1
1115 1
etc

(Rows for Items 1112 and 1114 will not show)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
jules <[email protected]> wrote in message
news:[email protected]...
> Good morning. I just have one other thing to ask you - what do i do with
the
> formula you wrote? It looks like ancient greek!!
>
> "jules" wrote:
>
> > Thank you very much for your prompt reply.
> > Best wishes
> > Jules
> >
> > "Ron de Bruin" wrote:
> >
> > > Hi Jules
> > >
> > > http://www.rondebruin.nl/copy5.htm
> > >
> > > For a Add-in solution see
> > > http://www.rondebruin.nl/easyfilter.htm
> > >
> > >
> > > --
> > > Regards Ron de Bruin
> > > http://www.rondebruin.nl
> > >
> > >
> > >
> > > "jules" <[email protected]> wrote in message
news:[email protected]...
> > > >I have created a price list and would like to generate an order form
from
> > > > that liston a separate worksheet. Is there a way of copying an
entire row if
> > > > the column has a figure in the total column? The rows with a 0
total would
> > > > not need to be copied

Hello Max thank you for your reply. I tried your formula out but have to
tinker with it a bit as my rows and columns start a bit lower down than row1.
If i have a problem with it i will be sure to re-post!!!!! Thanks for
your help.
Regards.
Julia

"Max" wrote:

> Perhaps you'd also like to play around
> with this formula approach as well ?
>
> Assume you have the table below in Sheet1,
> col headers in row1, data in row2 down
> where the key "TotalQty" col is in col B
>
> Item TotalQty <etc>
> 1111 1
> 1112 0
> 1113 1
> 1114 0
> 1115 1
> etc
>
> In an empty col to the right, say col K?
> Put in K2: =IF(B2<>0,ROW(),"")
> Copy K2 down by as many rows as data is expected
> in the table, say, down to K1000?
> (can copy down ahead of expected data input)
>
> In Sheet2
> ----
> With the same col headers in row1:
> Item TotalQty <etc>
>
> Put in A2:
>
> =IF(ISERROR(SMALL(Sheet1!$K:$K,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
> LL(Sheet1!$K:$K,ROWS($A$1:A1)),Sheet1!$K:$K,0)))
>
> Copy A2 across by as many cols as there is in the table in Sheet1, then fill
> down by as many rows as was done in col K in Sheet1, i.e. down by 1000 rows
>
> Sheet2 will return only the rows from the table in Sheet1 where the
> "TotalQty" col is <> 0, and doing so without any "blank" rows in-between
>
> For the sample data given, you'll get:
>
> Item TotalQty <etc>
> 1111 1
> 1113 1
> 1115 1
> etc
>
> (Rows for Items 1112 and 1114 will not show)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> jules <[email protected]> wrote in message
> news:[email protected]...
> > Good morning. I just have one other thing to ask you - what do i do with
> the
> > formula you wrote? It looks like ancient greek!!
> >
> > "jules" wrote:
> >
> > > Thank you very much for your prompt reply.
> > > Best wishes
> > > Jules
> > >
> > > "Ron de Bruin" wrote:
> > >
> > > > Hi Jules
> > > >
> > > > http://www.rondebruin.nl/copy5.htm
> > > >
> > > > For a Add-in solution see
> > > > http://www.rondebruin.nl/easyfilter.htm
> > > >
> > > >
> > > > --
> > > > Regards Ron de Bruin
> > > > http://www.rondebruin.nl
> > > >
> > > >
> > > >
> > > > "jules" <[email protected]> wrote in message
> news:[email protected]...
> > > > >I have created a price list and would like to generate an order form
> from
> > > > > that liston a separate worksheet. Is there a way of copying an
> entire row if
> > > > > the column has a figure in the total column? The rows with a 0
> total would
> > > > > not need to be copied
>
>
>
>

You're welcome, Julia !
Trust it'll work for you ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
jules <[email protected]> wrote in message
news:[email protected]...
> Hello Max thank you for your reply. I tried your formula out but have to
> tinker with it a bit as my rows and columns start a bit lower down than
row1.
> If i have a problem with it i will be sure to re-post!!!!! Thanks for
> your help.
> Regards.
> Julia

Hi Jules

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

--
Regards Ron de Bruin
http://www.rondebruin.nl



"jules" <[email protected]> wrote in message news:[email protected]...
> Good morning. I just have one other thing to ask you - what do i do with the
> formula you wrote? It looks like ancient greek!!
>
> "jules" wrote:
>
>> Thank you very much for your prompt reply.
>> Best wishes
>> Jules
>>
>> "Ron de Bruin" wrote:
>>
>> > Hi Jules
>> >
>> > http://www.rondebruin.nl/copy5.htm
>> >
>> > For a Add-in solution see
>> > http://www.rondebruin.nl/easyfilter.htm
>> >
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> >
>> > "jules" <[email protected]> wrote in message news:[email protected]...
>> > >I have created a price list and would like to generate an order form from
>> > > that liston a separate worksheet. Is there a way of copying an entire row if
>> > > the column has a figure in the total column? The rows with a 0 total would
>> > > not need to be copied
>> > > Thank you
>> >
>> >
>> >

Hello again Ron. I have tried your add in for the easy filter. It worked on
my computer at work and it was perfect - exactly what i needed but i have
just tried to load it at home. I put it in the correct place - i.e library
etc and it doesnt appear on the add in section, so i browed upward through my
computer and each time I select it, i get the message "......is not a valid
add in". What am I doing wrong? I have allowed the tick box for add ins etc.

"Ron de Bruin" wrote:

> Hi Jules
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> See David McRitchie's site if you just started with VBA
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "jules" <[email protected]> wrote in message news:[email protected]...
> > Good morning. I just have one other thing to ask you - what do i do with the
> > formula you wrote? It looks like ancient greek!!
> >
> > "jules" wrote:
> >
> >> Thank you very much for your prompt reply.
> >> Best wishes
> >> Jules
> >>
> >> "Ron de Bruin" wrote:
> >>
> >> > Hi Jules
> >> >
> >> > http://www.rondebruin.nl/copy5.htm
> >> >
> >> > For a Add-in solution see
> >> > http://www.rondebruin.nl/easyfilter.htm
> >> >
> >> >
> >> > --
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl
> >> >
> >> >
> >> >
> >> > "jules" <[email protected]> wrote in message news:[email protected]...
> >> > >I have created a price list and would like to generate an order form from
> >> > > that liston a separate worksheet. Is there a way of copying an entire row if
> >> > > the column has a figure in the total column? The rows with a 0 total would
> >> > > not need to be copied
> >> > > Thank you
> >> >
> >> >
> >> >
>
>
>