|
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 <atyahoo<dotcom ---- jules wrote in message ... 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" wrote in message ... 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 <atyahoo<dotcom ---- jules wrote in message ... 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" wrote in message ... 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 <atyahoo<dotcom ---- jules wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com