Comapring two excel worksheets and adding or deleting rows as needed

[dmreno]

Good Afternoon,

I am new to VBA coding and need help with excel VBA.

I have one work book with two worksheets, one is a current worksheet (S1) the other is an update version of the first(S2). All columns are the same and each will contain approximately 20,000 rows of data in.

1. I want to compare each cell in column A of S2 to all of column A in S1
a. If cell from S2 is equal to any cell in column A of S1 then do nothing
b. If cell from S2 is not equal to any cell in column A of S1 then add the corresponding row data to S1

2. Compare each cell in cloumn A of S1 to each cell in column A of S2
a. If cell from S1 is equal to any cell in column A of S2 then do nothing
b. If cell from column A of S1 is not equal to any cell in column A of S2 then do:
1. Create new worksheet (S3) and add corresponding row of data to new row in S3
2. Delete corresponding row of data from S1

If you need a sample of the workbook I can sanitize and supply, but preferable on to give out.

[jaslake]

Hi dmreno

Welcome to the Forum!

Try this code. The code assumes you have a header row in Row 1 of each worksheet. Let me know of issues

Please Login or Register  to view this content.

[dmreno]

jaslake thanks for the help. This code worked perfectly on a sample of about 30 rows in each spreadsheet. the real ones have 20K so will take a bit to run, but I have no doubt that it will work as well. You are a god send. I am hoping to learn this myself, I have used visual basic and basic in past but has been over 15 years since using it. so rust is thick. Let me know what else I need to do to ensure you get credit and I close this out properly.

Thanks again for the help.

David

[jaslake]

Hi David
You're welcome...glad I could help.

[dmreno]

jaslake,

did you recieve the message sent regarding additional help with this code?

[dmreno]

When comparing current sheet col A cells to corresponding updated sheet col A cells if they were the same, we did nothing ( in the below code), but I actually need to have the code do: If col A cells are equal then I need to copy the information from the corresponding row # for columns H, I & J (updated sheet) and place it into the corresponding row # in columns H, I & J in the current sheet.

Those three columns of information need to be updated whether or not the col A cell is new or not.

I hope this makes sense to you. I dont think it is very difficult to do.

I understand your programing logic, but I do not know the code well enough to attempt to modify myself.

Thank you in advance for this last bit of assistance.

[jaslake]

Hi dmreno

Yes I received your PM. Been on the road for the last several days with Family responsibilities...haven't paid too much attention to the Forum. I'll look at this probably tomorrow.

[jaslake]

Hi dmreno
Dummy up a file that represents what you're working with. Please show examples of S1 and S2. Include S3 that demonstrates what you wish to see in S3. If you choose not to do so, I'll need to do so and I'd prefer not to guess.

[dmreno]

I will get you something by end of business today.

[dmreno]

here is the workbook you asked for

[jaslake]

Hi dmreno
I've gotta admit, I'm a bit confused about what you're after. The code in the attached does this:

First
With each value in Current Sheet Column A, find it in
Update Sheet Column A; if found, copy Update Sheet Columns
H, I and J values to Current Sheet Columns H, I and J

Second
With each value in Update Sheet Column A, find it in
Current Sheet Column A; if not found, copy Update Sheet entire row
to the next row on Current Sheet

The results I get look nothing like the results you get. Explain a bit further and perhaps we can get on the same page.

[dmreno]

Jaslake

I have been looking at this all wrong. Can you contact me to discuss? I can supply phone number in private message. Then I will post to the thread what exactly I need and we can wrap this up.

[dmreno]

jaslake

I have attached a new file to use.

For instance:

For each T# in the NewMEL sheet I need to find it in the OldMEL. If it is found in the OldMEL sheet, then copy OldMEL data for that T# row into NewMEL row.

If T# from NewMEL cannot be found in OldMEL then do nothing.

[jaslake]

Hi dmreno

This code is in the attached and does this:
With each value in OldMel Column A, find it in NewMel Column A; if found, replace NewMel row with OldMel row; if not found do nothing.

Let me know of issues.

Please Login or Register  to view this content.

[dmreno]

I am running it on the real file, how long do you think it will take to run through 22,00o rows?

[jaslake]

Hi dmreno

A while I'd think...let me know how it goes.

[dmreno]

It is at 3 hours now and still running. is it vastly different from what the first code was doing? That code took less than 30 minutes to run through the 22,000 rows. Tha is why I am asking the question.

[jaslake]

Hi dmreno

Actually the code is significantly less complex with far fewer IO's. I'm assuming you've run the procedure on a COPY of the real file???
Assuming you did, kill the procedure (hold down the escape key...it may take a bit...make a note of the line of code where the procedure break point is) and let me know that line.
Assuming you didn't, I have to assume you have a BACKUP???

Let me play with it for a bit...I can look at eliminating the Copy/Paste stuff...that may speed it up.

[jaslake]

Hi David

I should of asked and didn't...which file ALWAYS has the fewer records...OldMel or NewMel?

[dmreno]

It locked up but did not see your post till now. I am going to run it again, right now.

[dmreno]

here is the line of code that was highlighted. the current situation has the newmel file with more rows than the oldmel but not sure if that will always be the case.


Application.CutCopyMode = False

[jaslake]

Hi David
See if this code works any better

Please Login or Register  to view this content.

[jaslake]

Hi David

Then try this code...let me know what works...what doesn't

Please Login or Register  to view this content.

[dmreno]

It appears that both codes work and provide same results. What is the difference in the two? Is one faster running than the other? I will test on a backup version of the 22k row file and let you know.

[jaslake]

Hi David...the two are the same code and do the same things the same way. The second version turns off calculation so I would expect it to be at least marginally faster.

[dmreno]

Hey John,

I finally was able to get it to run on the the main file. Both worked and it took only 5 minutes to run on the 22 K row of information. We are still not sure why they were hangin up on the older files I had, but I did creat a whole new one and it ran perfectly. Thanks again for all your help..

Once again you came through with excellent code that works flawlessly and I greatly appreciate it very much.

David

[jaslake]

You're welcome.