Solved

Highlight unmatched data

Posted on 2011-09-15
23
289 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Cartillo
  • 14
  • 9
23 Comments
 
LVL 29

Expert Comment

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

Expert Comment

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

Author Comment

by:Cartillo
ID: 36542061
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
 
LVL 29

Expert Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

by:Cartillo
ID: 36542852
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
 
LVL 29

Expert Comment

by:gowflow
ID: 36542969
Will try as not obvious as you data in Data is of format hh:mm:ss.00
will revert
gowflow
0
 

Author Comment

by:Cartillo
ID: 36543727
Hi Gowflow,

You're right. The time value really makes the whole crosschecking process mess. Not sure how to overcome this.
0
 
LVL 29

Expert Comment

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

Author Comment

by:Cartillo
ID: 36545173
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
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!

 
LVL 29

Expert Comment

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

Expert Comment

by:gowflow
ID: 36545963
OK here it is pls try this version it test HH:MM in both source and Data
gowflow
Highlight-Data3.xls
0
 

Author Comment

by:Cartillo
ID: 36546500
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
 
LVL 29

Expert Comment

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

Author Comment

by:Cartillo
ID: 36548122
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
 
LVL 29

Expert Comment

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

Author Comment

by:Cartillo
ID: 36548275
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
 
LVL 29

Expert Comment

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

Author Comment

by:Cartillo
ID: 36549175
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 36550731
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
 

Author Closing Comment

by:Cartillo
ID: 36551320
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
 
LVL 29

Expert Comment

by:gowflow
ID: 36552160
Your welcome any time don't worry and tks for the grade
gowflow
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 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

11 Experts available now in Live!

Get 1:1 Help Now