Cell Value Match

I am trying to figure out a way to perform a cell match. The worksheet "Control" is the control document. The worksheet "Dump" is the daily dump. I need to confirm matches between the two worksheets. If a value is in the "Dump" sheet but not in the "Control" sheet. The value should be appended, and an added date should be populated (Today's Date). If a value is in the control sheet but not in the "Dump" sheet. The cell should be marked as deleted in the date deleted column.

Do you expect a VBA solution or a formula solution ? as the way you are asking to append and delete items can be achieved VBA but would be a bit troublesome formula whys or I should say would require a helper column and will not be a neat solution.

1) You mentioned that you will compare Control with Dump sheet that is a daily Dump. So this means that the worksheet Dump will be cleaned every day and replaced by new data ?
2) If 1 is Correct then will Control sheet remains as-is and will not be replaced or it will also be replaced ?
3) if Answer to 2) will remain and not be replaced then Can we develop the macro in The workbook lets say we call Source.xlsm in which there are the 2 sheets Source and Dump and it will remain your master workbook ?
4) If agreeable to all of above then I envision a button on Source that you can activate that will run the macro and check all what you have asked, Is this ok with you ?

Worksheet "Checksheets-ALL" is the control document. It is relatively static. Columns never change, but rows may be added or removed.
"Orbit Dump" is the worksheet that gets replaced all the time with new data.

If a unique identifier number is in Orbit and in Checksheets, the update is to confirm the date in Column "U" in Orbit, and assign "completed" in Checksheets Column "X". If it is a new item in Orbit, it is to append the item to Checksheets and place todays date in Column "Y" in checksheets, so they know the date it was added.

When the item is appended, the associated items in the row from Orbit are to be added to the associated columns in Checksheets.

0

DougDodgeAuthor Commented: 2015-01-19

In addition if the unique number is not in Orbit, but is in Checksheets, Column "X" in Checksheets should say "Removed", and column "Y" to post todays date, the date it was removed.Cell-Match-Book1.xlsx

Don't you realize that we have to do the job all over again as if it is a total different issue ???
If your not aware its fine now you know. But sake good order please for future post the exact same thing that you have or else you will not only be wasting both your time and our but you would be royally frunstrating people and you won't get help

gowflow

0

DougDodgeAuthor Commented: 2015-01-19

Sorry, I was trying to strip the data out..... Send me your email address, I can make it up to you.

no email exchanges here it is policy break and you can be banned from EE. I only hope this file you posted is final in format I mean. I am not concerned with the content of the data this you can scrub with no sweat but need to have the exact same format of your production file.

Will attend this soon.
gowflow

0

DougDodgeAuthor Commented: 2015-01-20

It is exact. I copied the header row. And also showed the content of each column as to whether it is text, dates, or Y/N field.

Well just a side comment if you want help on this question it would be beneficial to expedite replies as fast as possible to keep the momentum going and flow.

This respect I have following questions: (which I would appreciate your answer to each one and not a total ignore as you already did !!!)

Sheet: Checksheets-ALL

1) Data starts at row 9 and row 8 is blank and row 7 is the header. Can we simply delete row 8 so that data would start at row 8 or there is a reason for this blank row ?

2) Can we sort the data in this sheet or there is a problem and you need to have it always as-is ?

3) Header always in row 7 and Columns From A to Y is this fix ?

Sheet: Orbit Dump

1) Always Columns from A to AV ? is this always like this ?

2) Can we sort the sheet or there is a problem ?

Appreciate your soonest reply.
gowlfow

0

DougDodgeAuthor Commented: 2015-01-20

Checksheets-All

1) Correct, Data starts at Row 9, Row 8 is blank. The real header is Row 7. Row 8 is being reserved by operations.
2) absolutely sorting is allowed. The key field is Unique Identifier (Column A). It is a simple process of inserting the column and Concatenating the value in Column D & "-" & Column E. The values in Column D duplicate, but are allocated to different sheets (Column E), so adding in Column E makes them Unique.
3) Header from Row 1 to 8 is static and never changes. Columns A to AC are static and never change.

Orbit Dump
1) Again, Column A is added in before the operation as it needs to become Unique. Same process as above, but in this case it is Column D & "-" & Column I
2) Sorting is Allowed.

Notes.

- The dumps I get do not have Column A at the start, I do this, so in the macro if it did that first it would prevent errors.
- I normally sort by Tag (Column D in Checksheets-All & Orbit Dump)
- Column X in Checksheets-All needs to show "Completed" if there is a date in Column U in Orbit Dump
- If a Unique Item in Checksheets-All does not have a corresponding Item in Orbit Dump, Column X in Checksheets-All should show "Deleted"
- If an item in Orbit Dump does not have a corresponding item in Checksheets-All, Column X in Checksheets-All should say, "New" and todays date should be entered in Column Y in Checksheets-All

You keep on adding and adding !!!!
Can't we just finish with one thing at a time ?????

You can't build a complete project just in 1 question. you ask a question and it is called QUESTION then it get answered then you move to the second question if it is related to the first one then you put the link in your question of that question and when it get answered you move to the third etc...

If a unique identifier number is in Orbit and in Checksheets, the update is to confirm the date in Column "U" in Orbit, and assign "completed" in Checksheets Column "X". If it is a new item in Orbit, it is to append the item to Checksheets and place todays date in Column "Y" in checksheets, so they know the date it was added.

When the item is appended, the associated items in the row from Orbit are to be added to the associated columns in Checksheets.

2) If the unique identifier exist in sheet Orbit Dump but DOES not exist in sheet Checksheets-All then
Add the item in Col A of sheet Checksheets-All and put today's date in Col Y

3) When point 2) happens we should:
Copy the entire row of the specific Unique identifier into Checksheets-All and not simply the Col A like all columns.

COMMENTS:
if point 3) is correct then what happens here you have different columns in Orbit Dump than in Checksheets-All which one is which ?? You need to provide a clear match col to col.
If it is the case prefer this to be object of a new question as it would entitle a different approach. If not please clarify.