Solved

Cell Value Match

Posted on 2015-01-18
23
73 Views
Last Modified: 2015-01-21
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.

Attached is an example worksheet.
Test.xlsx
0
Comment
Question by:DougDodge
  • 13
  • 10
23 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40556393
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
0
 
LVL 29

Expert Comment

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

gowflow
0
 

Author Comment

by:DougDodge
ID: 40556469
I am hoping for it in VBA.... I am building the rest in VBA....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40556476
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40557116
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40557189
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

Open in new window


Let me know
gowflow
Controls-V01.xlsm
0
 

Author Comment

by:DougDodge
ID: 40557790
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.
0
 

Author Comment

by:DougDodge
ID: 40557801
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40557829
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
0
 

Author Comment

by:DougDodge
ID: 40558766
Sorry, I was trying to strip the data out..... Send me your email address, I can make it up to you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40559444
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:DougDodge
ID: 40559461
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40559490
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40559602
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
 

Author Comment

by:DougDodge
ID: 40560138
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40560278
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
0
 

Author Comment

by:DougDodge
ID: 40560391
That is it......
0
 

Author Comment

by:DougDodge
ID: 40560770
No more additions.....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40561415
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
0
 

Author Comment

by:DougDodge
ID: 40561668
Perfect, you got it.....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40561742
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
0
 

Author Comment

by:DougDodge
ID: 40561798
I got it, and you are correct, the entire row needs to be appended.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40561824
YES but still you didn't get it !!!
Columns in Orbit Dump A to AV

ARE NOT the same as
Columns in Checksheets-All

So if you want me to copy the data in those columns from orbit dump to Checksheets-All you need to provide the list of conversion something like:

Orbit Dump        Checksheets-All
Col A                         A
      B                          T
      C                           M
etc....

then yes I can copy the data or else SORRY !!!

This version will do part 1) and part 2)
Part 3) which is obviously the full row is subject of a new question where you can put the conversion htat I just spoke about.

Pls check the file and let me know.
gowflow
Controls-V02.xlsm
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now