Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Cell Value Match

Posted on 2015-01-18
23
76 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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
 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

856 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