Comparing Two Tables - Output Changes

[Fishbone]

Hi guys,

Hope you can help me solve this one. Have searched the forum high and low but couldn't see anything that resembles what I am trying to achieve.

What I have is two lists, one staff list from the previous month and the current staff list from this month. What I would like to do is have these two lists on different tabs and a third tab that looks at the changes that have occured between the two. I suppose this will be split into two parts, part one that looks at the previous month and compares that with the current and anyone who exists in the first but not the second will be classed as a leaver.

The second part will compare the two lists together and if there are any changes for a particular person then to ouput the current months data and highlight what has changed. This will all be reflected in the third tab.

I have no need to look for people that are in the current month that did not exist in the previous month.

Hope this has been descriptive enough and I will attach an example. The number of rows could be anywhere up to 4000

[snb]

I think this will answer your question.
2 caveats:
- Adapt E:\OF\ to your actual situation.
- the fieldseparator I'm working with is ; in your case that may be , If so, you have to adapt this macro.

I hope you feel intrigued by this solution, so you will dive into the code until you fully understand what it does and how it perfoms it's task.
At last I am curious how much time this macro will save your company.

Please Login or Register  to view this content.

[Fishbone]

Hi snb,

First of all, thank you for your reply and time.

Ok, tried running the vb making changes to the directory that the csv gets saved to but am getting a run-time error '1004' Application-defined or object-defined error on the following line,

sn = Sheets("HR Changes").Cells(30, 14).Resize(UBound(sc), 12)

[snb]

That means that the fieldseparator on your system isn't semicolon ; but comma,

Replace every ";" in the macro by ","

[Fishbone]

Thanks again snb,

Will try and work out exactly what it is doing as the code is a little advanced to what I am used to.

[jkelly228]

I am trying to format this to work with a different spreadsheet. However, I get a "Subscript out of range on this line

Please Login or Register  to view this content.

Any idea why that woud happen? I appreciate the help.

Kelly

[jkelly228]

Just as an update, I tried replacing all Semicolons with commas as recommended above and I still get the same error in the same spot.

Thanks for the help.

Kelly

[jkelly228]

OK. Quick update. I found what I was doing wrong and created Sheet 3 in the same spreadsheet. So, now it is giving an error at this line

Please Login or Register  to view this content.

The full code that I am using with the attached spreadsheet is:

Please Login or Register  to view this content.

This is exactly what I need to compare 2 sets of data, but I would like it to start in section A1. This seems to start it in the middle of the sheet. Thank you all for any help you can provide.



Kelly

[snb]

Please Login or Register  to view this content.

[jkelly228]

Thank you for the feedback. I believe that will start the data in A1. However, I get an issue in this line of code and I will be honest, I don't know the function of this lline to be able to trouble shoot it. The error is "Subscript out of Range"

Please Login or Register  to view this content.

[snb]

You'll have to go through the code line by line. (using F8 )
Make the window 'direct' in the VBEditor visible.
Debugging is one of the finest methods to learn VBA.

[jkelly228]

SNB,

I have done that. I am not sure what is happening with that line of code. I can see the code taking the 2 different sheets and saving them as .cvs file and then doing the comparrison between them. However, since I'm not a programmer I really don't know the function of that line and thus how to fix it.

I do appreciate all of your help. Thanks

Kelly

[jkelly228]

Bumping this up. I have looking up IIF and found that it is an Immediate If statement and SN is a Seriel Number statement, but neither pieces of that information has helped me to determine why my spreadsheet keeps stopping at the same point.