Delete rows if?

Hi,

I have two worksheets named "NEW" & "OLD".
Both have hundreds of rows now I want 'if entire row of NEW sheet is same in sheet OLD'.
Entire row deleted in both sheets.
so only those rows would be left in both sheets which are not identical.
if anybody could help me it'd be great.

Thanks,

Supply a workbook for us to experiment/demonstrate with.

Workbook!

Here is the workbook!

Two solutions offered.
The first (attached with file name ending udf) is a user defined function solution. Examine the formula in cell S3 and copy down. Be aware that before you filter on this column and delete rows that the formulae on BOTH sheets should be converted to plain values with copy/paste-special|Values, otherwise, after you've deleted rows on one sheet, the formulae will update on the other sheet and no rows will be indicated to be deleted.

The second (attached file name ending in PQ) is a Power Query solution. I needed to convert data to tables on both original sheets. This solution retains the original tables and provides you with new sheets with your results. You can right-click one of the new tables and choose Refresh to update the results.

Another possibility is a formula in row 3 (say cell U3) of the NEW sheet (TEXTJOIN may not be present in Excel 2010):
copied down. This is not as robust as the other solutions but will very likely give the same results in your case. You can do the corresponding formula on the other sheet. Same as before, convert to plain values before deleting anything.

I can't help feeling there might be a more straightforward solution!

ps. since posting I've updated the PQ file removing the macros that I'd been trying.

Thanks,

That UDF thing worked out just fine, I managed to make it useful in my job it spares me from too much work.
I'm really thankful!

Hi,
I don't know if I should ask this question in any other post but since you helped me here so I thought may be you could suggest me something about it, Actually the question is 'Is it possible with VBA macro to generate details with cell reference at the end of the data that if any cell value repeats itself what I mean to say is take the example of New sheet the word "clear" is repeated 9 times with details of cell in A4,A6,A8,A10,A12,A14,A16,A18,A20
Same like this amount "500" in B3,F5,N5,H7,P7,J11,F13,N13,B15,H15,L15,P15,J19,D21!
And so on,
If you could suggest anything it'd be really helpful,
Thanks!

Supply a workbook with a manually generated example of what you expect in 2 or 3 cells.
Consider also creating a new thread.

Need a little update!

p45cal said:

Two solutions offered.
The first (attached with file name ending udf) is a user defined function solution. Examine the formula in cell S3 and copy down. Be aware that before you filter on this column and delete rows that the formulae on BOTH sheets should be converted to plain values with copy/paste-special|Values, otherwise, after you've deleted rows on one sheet, the formulae will update on the other sheet and no rows will be indicated to be deleted.

Click to expand...

HI p45cal,

I had an issue related VBA and you helped me out with a great solution with UDF its the best, it's working just fine but in recent past I found out that it works for 10,000 rows what if I wanna work with 25,000 rows or maybe 50,000 or maybe more than that how would I edit this UDF.

Thanks!

It should work for any number of rows but it could be slow.
See the formula in cells T3 in both the sheets of the attached.
I don't know what version 'Excel10' is (according to your profile); Try first entering normally the formula in one cell and if that doesn't 'spill' the results down then in older versions of Excel you will have to select all the cells T3:T21 while entering the formula, then committing the formula to the sheet using Ctrl+Shift+Enter rather than just Enter. It is important for speed in older versions of Excel that the Shift button is held down at the same time as Ctrl + Enter (you'll be able to see whether you've done this successfully if there are curly brackets showing around the entire formula).

This version should work a lot faster than the other udf.
Otherwise, the Power Query offering should be fast too.

Thanks!