Spreadsheet Updates

Is this related to the question we just solved ?

If yes I see that your file Check-Sheet-Log-Template.xlsm 18MB is extremely large (18MB) can you cut some data and attach or can we use the previous one ? I did not venture in opening it it would take ages !!!

gowflow

I just saw your Mapping.xlsx file and it is EXACTLY what is needed !!! very good job. Just imagine having just made the macro to simply copy the row from orbit dump to checksheets-all without paying attention that the columns doesn't match how would your data have looked a total mess !!!

:)

side note
I would appreciate, when we work on an issue and if I propose next step and 'you still need my help' to simply let me know when you post a new question by putting a link to that new question in the old question so I can attend otherwhyse there is no way I can tell that you asked a new question and it was just by coincidence that I stumbled on this one.

For sure, if your fedup with my nasty comments then I understand you simply wanting for new help !

gowflow

Re my previous comment about the file size, no worry I opened it and noticed you posted live data which you should refrain from and scrub your data as this is accessible to public and can be mis-used.

I would suggest you request attention to moderators in this question (the triangle just under the question on top) and ask them to remove your file and you prepare a scrubbed one (scrubbed = data modified)

Back to the essence of this question:
Although I may know what you want, reading this question it is not clear what you want. Can you please re-pphrase what is the objective of this question clearly ?

Rgds/gowflow

I need to update "Checksheets-ALL on a daily or even more frequent time frame.

"Loop Synchronization" is also part of this same issue....

Sorry, not sure how to link question as yet.

Not a problem but you need to tell me exactly what do you want me or someone else to do in this question as 'loop syncronization' is a somewhat vague issue.

Best is for you to tell us how you do what you do manually and then we can automate this.
gowflow

BTW I checked your request for attention and what you asked is that question be deleted is this what you want ?? or you want the file attachment to be removed ? it is 2 different issues if the second then you need to click on request attention and specify exactly what you need or else this question will be deleted !

gowflow

Requested file be removed and keep the question. You already know what I am looking for, but when I get to work I will repost a cleansed file. Same file, but strip out most data.

No honestly I do not know what you want as I am confused with the new worksheets added pls understand we don't know your business and you have to spell it out clearly.

I can make your life easier by simply reposting the last questions item number  3)

 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.

Is this what you want to achieve in this question ? Item 3) ?
If yes then what we did in item 2) is simply put the new records found in orbit dump not in checksheets-all to put them in checksheets-all updating only Col A and Y

Now you want that routine to be revised to update not only A and Y but to also look at the mapping table and update all fields that are in Orbit Dump to their corresponding fields in checksheets-all for these records.

Is that what you want ???
Kindly confirm or clarify.

Pls note this is a public forum and questions are meant to be directed for each and every person who could run into similar situations. If the questions and answers are not clear then it is not benefiting no one.

Thank you
gowflow

Chechsheets-ALL needs to be updated with information from "Orbit", "MC Tracker Data" according to the mapping.
In addition there is another posting for "Loop Synchronization" that has to be updated prior to this update.
Sorry, I never linked it.

Sorry but it is not clear what you want.

What do you mean by:

Chechsheets-ALL needs to be updated with information from "Orbit", "MC Tracker Data" according to the mapping.

When this has to be done and pls explain the mechanics.

by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.

gowflow

"Checksheets-ALL" is the control worksheet from which all reports are generated.
There are a number of worksheets that go into producing it. "Orbit" is a dump from a DB.
"MC Tracker Data" also feeds into it. But prior to it being used it needs to have "Tracking" update it. The data in "Tracking" needs to be normalized and get rid of the X/X values and split them into 2 columns in "MC Tracker"
It is a long ang ugly process.
"Orbit" and "Tracking" get replaced daily and sometimes more often than that. Hence the idea if VBA rather than formulas.

ok fine. what do we do in this question ?

"
by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.
"
gowflow

If the Unique Number is no longer in Orbit, it can stay in Checksheets-ALL as well as the actuals, it just needs to show "Removed" or "Deleted" in Column X "Check Sheet Status"

ok I can see you have 3 buttons up there
Daily Update
Import Orbit Data
Import Tracker

I guess Daily Update is the one we already developed.
Where do you want this new macro to be kicked of ? shall we add a new button or it to be activated after completion of Daily Update with no new button ??

And what I see there is no use of the Mapping so far in this question at this point. Right ??
gowflow

The macro can come after "Daily Update"
The mapping is part of the final Daily update.

ok fine.

I see that you keep every version of code that we produce in your workbook at the end it become cumbersome. My suggestion for your production file is to only keep the last one or else you may soon run into a mess not knowing which is which.

I counted the following that need to be removed from your workbook:

Module 10               -  Sub GetData()              You can delete this entire module.
modDailyUpdate2  - Sub DailyUpdate2()     You can delete this entire module.
modDailyUpdate    - Sub DailyUpdate()        Delete this Sub and replace it with the following new code:

Sub DailyUpdate()

Dim WSC As Worksheet
Dim WSD As Worksheet
Dim MaxRowC As Long, MaxRowD As Long, I As Long
Dim MinRowC As Long, MinRowD As Long
Dim CRow As Long, DRow As Long
Dim cCell As Range

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

'---> Set Variables
Set WSC = Sheets("Checksheets-ALL")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
MinRowC = 9
Set WSD = Sheets("Orbit Dump")
MaxRowD = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row
MinRowD = 2


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

'---> a) Put "Completed" in Col X in sheet Checksheets-All
'     Create Formula to find items in Checksheets-ALL and in Orbit Dump
'     =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),"",A2)
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),"""",A" & MinRowC & ")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
    Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
    CRow = cCell.Row - 1
    WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Completed"
End If
WSC.Range("ZZ:ZZ").Delete

'---> b)Put today's date in Col U of sheet Orbit Dump.
'---> Create Formula to find items in both Orbit Dump and in Checksheets-ALL
'     =IF(ISERROR(VLOOKUP(A2,Checksheets-ALL!A:A,1)),"",A2)
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowD & ",'Checksheets-ALL'!A:A,1)),"""",A" & MinRowD & ")"
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Copy
WSD.Range("ZZ" & MinRowD).PasteSpecial xlPasteValues
WSD.Range("A" & MinRowD & ":ZZ" & MaxRowD).Sort Key1:=WSD.Range("ZZ" & MinRowD), order1:=xlDescending, Header:=xlNo
If WSD.Range("ZZ" & MinRowD) <> "" Then
    Set cCell = WSD.Range("ZZ" & MinRowD & ":ZZ" & WSD.Rows.Count).Find(what:="")
    DRow = cCell.Row - 1
    WSD.Range("U" & MinRowD & ":U" & DRow).Value = DateValue(Now)
End If
WSD.Range("ZZ:ZZ").Delete


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

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


'---> 3) If the Unique Number exist in sheet Checksheets-All but DOES not exist in sheet Orbit Dump
'     it just needs to show "Removed" in Column X "Check Sheet Status" of sheet Checksheets-All
'     Create Formula to find items in Checksheets-ALL and NOT in Orbit Dump
'     =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),A2,"")
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),A" & MinRowC & ","""")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
    Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
    CRow = cCell.Row - 1
    WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Removed"
End If
WSC.Range("ZZ:ZZ").Delete


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

'---> Ensable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

MsgBox "Daily Update Done !", vbExclamation

End Sub

Select allOpen in new window

Pls let me know outcome.
gowflow

Greetings,

I cleaned out the Modules you advised on.

I cleared all fields and ran the macro to see what would be updated.

There is a lot of empty cells.

The mapping I provided did not seem to be incorporated.

I have again shown the mapping, this time in Row 6 of "Checksheets-ALL", and where that data should come from.....

As you can see, there is lots of empty columns.....
Check-Sheet-Log-Template---Copy.xlsm

It seems we cannot communicate !!!!

for sure no mapping was taken into consideration as I clearly asked what need to be done in this question and you answered in ID ID: 40564539:

If the Unique Number is no longer in Orbit, it can stay in Checksheets-ALL as well as the actuals, it just needs to show "Removed" or "Deleted" in Column X "Check Sheet Status"

and I answered in ID: 40564680

I guess Daily Update is the one we already developed.
 Where do you want this new macro to be kicked of ? shall we add a new button or it to be activated after completion of Daily Update with no new button ??

 And what I see there is no use of the Mapping so far in this question at this point. Right ??
 gowflow

I expressly mentioned Mapping coz you did not referred to it, but here again your answer was in ID: 40564844

The macro can come after "Daily Update"
 The mapping is part of the final Daily update.

Again not mentioning anything about Mapping so I went forward and developed it to update Col X with "Removed"

and now you mention Mapping ??????


Sorry but this is not professional at all you need to read the threads to the end.
I am totally upset !!!

gowflow

Great Work....

The next step should be how to incorporate "MC Tracking Data" into the update. Let me know how to link this to the next question.....

when you draft you next question first you put this question is related to previous or continuation of previous and you copy paste the address of this question (the http ...) and paste it in the text of the question and explain what you want the question to be.

Once you finish drafting the question and you post it then you copy the address of the new question and paste it here that is if you want my help in the new question.

Note try to avoid putting this humongous file in the new question as you will be multiplying your personal info. Note that moderators did not remove your file it seems they need a reminder !

gowflow

Related question....

https://www.experts-exchange.com/questions/28603624/How-do-I-use-VBA-to-update-a-worksheet-from-another-worksheet.html