Cell Value Match

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.

gowflow

I can provide you with a VBA solution but need more clarifications before I put the script. Will wait for your confirmation first.

gowflow

I am hoping for it in VBA.... I am building the rest in VBA....

ok great give me some time ... as I am just solving an urgent matter and will get to it just after. If no one beats me to it ! :)

gowflow

ok some questions:

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 ?

gowlfow

Despite you did not have time to answer my questions, pls find my proposed solution. I hope I have addressed your request correctly.

I made a copy of your original sheets and called them bckup so you can verify the results and compare.

I made a small amendment in your Control Sheet where I removed row1  that was blank so that both Dump and Control have header on row1 and data on row2.

Load the file activate macros and press on the button Daily Update and check the results.

here is the code for your convenience.

Sub DailyUpdate()
Dim WSC As Worksheet
Dim WSD As Worksheet
Dim MaxRowC As Long, MaxRowD As Long, I As Long
Dim CRow As Long, DRow As Long
Dim cCell As Range

Set WSC = Sheets("Control")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
Set WSD = Sheets("Dump")
MaxRowD = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row


'---> Run check on Dump
'---> Create Formula to find items in Dump but not in Control
'     =IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"")
WSD.Range("B2:B" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"""")"
WSD.Range("B2:B" & MaxRowD).Copy
WSD.Range("B2").PasteSpecial xlPasteValues
WSD.Range("A1:B" & MaxRowD).Sort Key1:=WSD.Range("B1"), order1:=xlDescending, Header:=xlYes
If WSD.Range("B2") <> "" Then
    Set cCell = WSD.Range("B2:B" & MaxRowD).Find(what:="")
    DRow = cCell.Row - 1
    WSD.Range("B2:B" & DRow).Copy WSC.Range("A" & MaxRowC + 1)
    WSC.Range("B" & MaxRowC + 1 & ":B" & MaxRowC + 1 + DRow - 1).Value = DateValue(Now)
End If
WSD.Range("B:B").Delete


'---> Run check on Control
'---> Create Formula to find items in Control but not in Dump
'     =IF(ISERROR(VLOOKUP(A2,Dump!A:A,1)),A2,"")
WSC.Range("D2:D" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A2,Dump!A:A,1)),A2,"""")"
WSC.Range("D2:D" & MaxRowC).Copy
WSC.Range("D2").PasteSpecial xlPasteValues
WSC.Range("A1:D" & MaxRowC).Sort Key1:=WSC.Range("D1"), order1:=xlDescending, Header:=xlYes
If WSC.Range("D2") <> "" Then
    Set cCell = WSC.Range("D2:D" & MaxRowC).Find(what:="")
    CRow = cCell.Row - 1
    WSC.Range("C2:C" & CRow).Value = DateValue(Now)
End If
WSC.Range("D:D").Delete

'---> Sort Control on Col A
WSC.Range("A1:D" & MaxRowC + DRow).Sort Key1:=WSC.Range("A1"), order1:=xlAscending, Header:=xlYes
WSC.Range("A1").Select

MsgBox "Daily Compare Done !", vbExclamation

End Sub

Select allOpen in new window

Let me know
gowflow
Controls-V01.xlsm

Not Really..... but close

I am sending you another workbook

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.

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

Again !!!

Why didn't you post the correct workbook at the first place ???

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

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

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.

ok in few words if you want to make it up to me then you will need to do the following:

For this question what is the difference (in format) between the file you posted (Test.xlsx) and to which I proposed the solution and the second file you posted.

This should facilitate my integration.
gowflow

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

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

Notes.
....

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...

Do we have an understanding ?

Waiting for your reply to know where to put the effort.

gowflow

That is it......

No more additions.....

ok lets summarize what I will do is the following:

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.

Let me rephrase my understanding

1) If the unique identifier exist in both Checksheets-All and  Orbit Dump then:
Put today's date in Col U of sheet Orbit Dump.
Put "Completed" in Col X in sheet Checksheets-All

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.

gowflow

Perfect, you got it.....

What about Point 3 ????

does it have to be a copy of whole row ??? it seems you only read 1/2 of what is written or we have a major problem in communicating.

gowflow

I got it, and you are correct, the entire row needs to be appended.