Copying data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
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)
 
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
 
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.
 
Back
Top