MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy info in columns A and B based on info in column A

Posted by Rick on April 11, 2000 12:56 PM

I need to copy data from one sheet to a second sheet based on the information in column A.

Ex:
Worksheet has 100 rows with multiple columns.
If Column A has a number greater than 0, I want to copy the entire row to a second worksheet. I do not want any empty rows on the final worksheet.

Posted by Celia on April 12, 2000 1:24 AM


Rick
This can, of course be done with a macro, but here’s one of doing it manually that doesn’t take very long (probably in less time than it would take to write a macro).

1.Let’s say your data starts in cell A1 on Sheet1.
2.In an unused column on Sheet1(say column K), enter in cell K1 :- =IF(A1>0,A1,””).
3.Drag this formula down as far as required
4.Sort by column K
5.Select the rows with data in column K, copy and paste to Sheet2
6.Delete column K on both sheets
7.Resort both sheets if required

Celia

Posted by Celia on April 12, 2000 8:05 AM

Rick
I don't seem to be functioning too well today.
A simpler way is :-
1.Sort by column A
2.Select the rows where column A values are greater than 0, copy and paste to Sheet2
3.Resort the sheets
Celia

Posted by Rick on April 12, 2000 11:45 AM

Thanks for the response. I did not explain my goal very well. I believe I will have to go to a macro, but do not understand them.

I want to create an Sales Order Spreadsheet and order guide.

Sheet 2 would have:
Products listed
Column to enter quantity to order

If a quantity is entered on Sheet 2, the complete row would be automatically copied to Sheet 1.

I want the listing of products ordered on Sheet 1 to fill all lines necessary, without blank lines, and without taking extra steps.
Thanks.

Posted by Celia on April 12, 2000 4:00 PM

I want to create an Sales Order Spreadsheet and order guide. Sheet 2 would have:

Rick
Send me a file with sheets 1 and 2 in the format you require and filled in with some data.
Celia