rule to select and delete rows in excel

[vimleshpatelia]

Dear all,

I use excel 2003/2007. I have excel sheet with around 1000 rows.

example sheet as follow
1 drawing no. abdnfhrekdfk
2 123 456 789
3 date adfhfbhe feei
4 drawing no. abdnfhrekdfk
5 shaft key
6 date fghrjt
7 drawing no. abdnfhrekdfk
8 pump housing

My question is, I want to delete rows which start with "date" (rows 3 and 6), "drawing no." (rows 1,4 7). Is it possible to create rule in excel so that I can do it?

Here starting word will be same (eg. date, drawing) so we can use "left" function.


Any help will be appreciated.

Thanks in advance

Vimlesh Patelia.

 

[PeterMoran]

Hi,

Firstly let me say, as you are probably aware, the mechanism for doing what you want to do is different between Excel 03 (menu) and 07 (ribbon) so I have tried to provide a generic approach.

I presume your data is text in column A, rather than the spaces indicating text in separate cells in each row.

I would use Data Filter to identify text in Column A - starting with "date". Once you have the data filtered you can delete the displayed rows with Edit Cells/Rows to delete. Then repeat procedure with "drawing nos".

You cannot create a formula to do this in Excel, but you would be able to create a macro, However it would not take long to do the above manually, unless you have many sets of data to perform it on.

Good Luck!

Peter Moran

 

[vimleshpatelia]

Peter,

Thanks for help. It would be nice if you can help me further.

I have data only in column A.

yes, I can do it by macro, but I have lot of data.

I have around hundred pdf files and this I have to paste in excel and then delete rows.

I can not paste all data in same sheet.

 

[PeterMoran]

Hi Vimlesh,

Can you explain what you need to finish up with, so I can understand your problem better. Do you need to have the data in one spreadsheet, or are all the sets of data to remain separate?

If the data is in pdf files, then you cannot avoid a manual selection process to copy and paste the data into a spreadsheet. Maybe you can have 5 sheets in a workbook and load a set of data to each sheet.

Do you use Excel 2003 or 2007? Or do you have data separated so that you use both versions on the data?

Good Luck!

Peter Moran

 

[vimleshpatelia]

Peter,

I have a data in pdf (50 files). Now I can not paste all 50 files in same excel sheet as they all are different as per my work and so I can paste in 50 different sheets. so copy/paste is must that I can agree.

Once I paste data in excel, I want to delete some rows in excel which starts with some specific word (as I told eg. date, drawing no etc).

I have both 2003 and 2007 so you can suggest it for any one. lets say 2007.

Thank you.

 

[PeterMoran]

Hi Vimlesh,

In 2007, with a trial set of your data pasted into a sheet, select the whole column with the data. Then select the Data ribbon, and then Filter. with the Filter on, select the little arrow at the top of the column, then select the "Select All" box to turn off that part of the filter, then select "Text Filters" and then "Begins with...", enter "date" (no quotes) and then select OK. This should now display all rows starting with "date".

Select these rows in the column, and then select the Home ribbon, then at the right, in Cells, select Delete, then Delete Sheet Rows. The rows you selected should now be deleted.

Then select the little arrow again at the top of the column, and select Clear Filter. You should now see the remaining data less the rows you have deleted.

Repeat this procedure for each other rows you wish to remove.

Good Luck!

Peter Moran

 

[Scott24x7]

Vimleshpatelia,
If you are just importing data from another location, have you considered using Access? Then you can delete all for a specific value in a field criteria, and then output that to Excel. May not be an option if you don't have access, but it sounds like if you are dealing with 50 files at a time, this is worth taking a couple hours to learn to do.
Just an alternative...
-S

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."