Highlight unmatched data

Hi Experts,

I would like to request Experts help create a macro to crosscheck data in Column A and B (Time and Cert No) at “Source” sheet with Column A and C (Time and Cert No) in “Data sheet”. Time and Cert No at Source sheet  need to be matched with Time and Cert No at Data sheet. If the its not matched, highlight data in Column B (Source sheet) with Red color. Hope Experts will help me create this feature.



Highlight-Data.xls
CartilloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Hi Cartillo,
Is this what your looking for ?
Pls make sure macros are enabled and activate Match Data and see the results. I aded a button to reset Col B in case you need to add some more data in sheet data or source and experiment.

Pls let me know your comments.
gowflow
Highlight-Data.xls
0
gowflowCommented:
Cartillo,
a small clarification I read your post and you want to compare BOTH A & B in source and see if they exist in sheet Data in A & C so pls understand that in Source the following are disreguarded and not taken into concideration:
1) Any line that has not both A and B filled AND
  are not Start in Col A
  are not value in A but no value in Col B
  are not ---------------- in Col A
  are not blank in Col A
  are not Time in Col A

if my assum,ption are wrokng pls clarify and I will modify the model accordingly.
gowflow
0
CartilloAuthor Commented:
Hi Gowflow,


This is what I’m looking for. However, I’m having difficulty when crosschecking time value after 24:00:00 hrs. if you noticed at source sheet, the time continues from 23:00:00:00 to 29:00:00:00 but in Data sheet the time sequence changed after 23:00:00. The following time will be 00:00:00 to 08:00:00. Is that way for us to overcome this?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

gowflowCommented:
Well here is my pov
1) You need to check the origin that produced the time for you in Source and why is it keeping time after 23:59:59 to goto 24:00:00 and  25:00:00 and so on
2) an other way we can deal with it if you are not able to fix the source origin producing time is to tell me for the below examples what should be the time to concider

25:49:09:00
26:00:30.00
27:59:31.00
28:00:01.00

Rgds/gowflow
0
gowflowCommented:
Well seeing no reply I assumed the following:
1) as you posted the maximum is 23:00:00 so for my above examples you should have:
25:49:09:00  ---> 02:49:09.00
26:00:30.00  ---> 03:00:30.00
27:59:31.00  ---> 04:59:31.00
28:00:01.00  ---> 05:00:01.00

I asked you for the exampleas I was surprised by your 23:00:00 where it should have been 23:59:59
Anyway pls check this version it takes into concderation a cut of 23:00:00 I wonder if this is correct as running it give red for all hours after 23:00:00

Pls advise your comments.
gowflow

Highlight-Data.xls
0
gowflowCommented:
After looking more carefully at your data here is what I can conclude
time is reset at 24:00:00 (normally not 23:00:00) as you stated.
check out this version !
gowflow
Highlight-Data2.xls
0
CartilloAuthor Commented:
Hi Gowflow,

Sorry for the late reply. You're very right for the time sequence. The main reason it gave a red alert is because it has different at the second (SS) and millisecond (ms) . Is that any change crosscheck the timing only at hh:mm, omit ss and ms? Hope you will consider.
0
gowflowCommented:
Will try as not obvious as you data in Data is of format hh:mm:ss.00
will revert
gowflow
0
CartilloAuthor Commented:
Hi Gowflow,

You're right. The time value really makes the whole crosschecking process mess. Not sure how to overcome this.
0
gowflowCommented:
ok so to summ it up before i go on the lengthy path you are sure it is hh:mm that you need to check and if ok then found and time reset at 24:00 pls confirm
gowflow
0
CartilloAuthor Commented:
Hi gowflow,

Yes, the time check just sufficient at hh:mm. I'm in the process of converting the the "Source" sheet timing (column A )  from 24 to 30 hours into 00 to 06 hours. This will make both sheets are having the same time format. I will update you once this timing got fixed.

0
gowflowCommented:
Well NO pls do not convert it I have build it so it works with over 24 !!! You will make me work more to fix it back !!! Pls do not touch the file I will do with it as is !!!
Pls confirm
gowflow
0
gowflowCommented:
OK here it is pls try this version it test HH:MM in both source and Data
gowflow
Highlight-Data3.xls
0
CartilloAuthor Commented:
Hi gowflow,

Thanks a lot for the code. I've tested and found some data which is differed were not highlighted. I marked those data in yellow. Any idea why its not being highlighted even though the data not matched?
Highlight-Data3.xls
0
gowflowCommented:
Cartillo,
It was not easy to decode and tks your highlighting in yellow whcih showed me lots of flopps in the code. I think This version is more stable and got things right.
Pls check it thourolly and let me know.
gowflow
Highlight-Data4-Fix.xls
0
CartilloAuthor Commented:
Hi gowflow,

Thanks for the revised version. All data have been checked perfectly except those data with red (attached the workbook). By right the data shouldn't highlight with red, its matched with both column A and B.
Highlight-Data5-Fix.xls
0
gowflowCommented:
Obviously they are red as F005942a IS NOT THE SAME AS F005942A (note the small a and the big A at the end) You have the second in Source and the first in Data. Anyway this has been fixed. Chk and let me know if other similar issues.
gowflow
Highlight-Data5-Fix.xls
0
CartilloAuthor Commented:
Hi gowflow,

Sometime I do have data at Data sheet with this type:

 F006825A/F006836A

Data at Source sheet, at first F006825A will be used, and followed by F006836A. But at Data sheet both timing will be listed with F006825A/F006836A. Is that way to overcome this?
0
gowflowCommented:
HEY !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111
PLS PUT ALL POSSIBILITIES FROM THE FIRST TIME !!!!
everything can get overcomed but you keep on throwing things I have no problem but need to see all diffrent possibilities not discover them as we go along.

Please chk your data carefully and once your done attach a file that has a sample of all kind of possibilities including (the Total that I discovered on the way etc ... and the /split between 2 items)

gowflow
0
CartilloAuthor Commented:
Hi gowflow,

I'm very sorry for this, totally overlooked with this pair data since its not always appeared in the list. I have attached the workbook with this type of data (marked in red). Hope you will consider to fix this. Again, sorry for annoyance.
Highlight-Data5-Fix.xls
0
gowflowCommented:
Sorry if I came a bit strong. Here it is pls let me know if other issues. Do not understand me wrong I like very much to solve issues for you as see your very structured and clear in your requests maybe why I went over when several issues turned unexpected !

Cheers
gowflow
Highlight-Data6-Fix.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CartilloAuthor Commented:
Hi gowflow,

Thanks a lot for the great help. I'm not think so I able to achieve this type of result without your help. Sorry for the inconvenience caused.
0
gowflowCommented:
Your welcome any time don't worry and tks for the grade
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.