I need Excel code that will step through rows on Sheet 2, comparing and updating rows on Sheet 1.
General Idea:
If a row exists in Sheet 2 and Sheet 1, update Sheet 1 with Sheet 2 data.
If the row exists in Sheet 2 but not in Sheet 1, append the row to Sheet 1.
For a row to 'exist' a certain number of columns from Sheet 2 must match a certain combination of columns in Sheet 1, but the row might not necessarily have the same (excel) row number; so each row in S2 must compare against each row in S1.
## Deliverables
Detailed Spec:
Using format S1A, S1B.. S1H to represent Sheet 1 Col A through H and S2A..S2H to represent Sheet 2 Col A through H. The logic is as follows:
Row 'exists' if:
(S2A = S1B) AND (S2B = S1C) AND (S2C = S1D) AND (S2E = S1D)
If row exists:
Update S1 as follows:
S1F=S2G
S1H=S2H
Change S2 row background colour to light green
Change S1 row background colour to light green
If row doesn't exist, add a new row to S1 thus:
S1B=S2A
S1C=S2B
S1D=S2C
S1E=S2D
S1F=S2E
S1G='NEW'
S1H=S2H
S1A should increment S1A+1, e.g. if last row S1A is 1000, the new row should have S1A=1001
Change S1 row background colour to light red
Change S2 row background colour to light red
NOTES -
1: The row numbers on S2 & S1 do not match up, i.e. a match could occur from row 5 in S2 to and row 95 in S1.
2: Rows should match only once, but MAY match more than once - unfortunately because of historical reasons all duplicate matches should be compared and updated too, so the entire record set needs to be checked.
3: Compare all fields as text, even though some are numbers. Ignore whitespace (i.e. 'trim') when comparing, there shouldn't be any, but just in case...
4: Provide visual feedback to user to give an indication of what's going on.. this can be very simple, just something like "Comparing Row 1 of 14838
That's it!
Small dataset is attached; actual dataset has 14K+ rows in S2.
Efficiency / speed of code is not an issue since it will be run only once.
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
## Platform
Microsoft Excel Macro / VBA
Windows