I have done a lot of research using google and watched numerous videos on youtube and I still can't seem to get this down. I'm not the best at Excel but understand the basics but am weak with understanding functions completely. I have had no luck finding help or tutorials for exactly what I want to do.
I have a spreadsheet (attached here) that I am using to try to compare two people's data entry for errors. I have tried conditional formatting but ran into errors doing that. What would be the best way to compare both of these lists to look for any errors in spelling or number data entry? I want to compare every cell on both tables and I have organized them alphabetically using the first three columns. I've tried for too many hours and I give up.
Many thanks. I am using excel that comes with windows 7 (I'm guessing excel 2010 perhaps)
Last edited by djgriffin; 01-09-2012 at 11:19 PM.
For start You can use such formula to find rows with differences
=if(A2&B2&C2&D2&E2&F2&G2&H2=J2&K2&L2&M2&N2&O2&P2&Q2,"ok","not ok")
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
U can enter this formula in column R:
=ISNA(MATCH(A2,$J$2:$J$116,FALSE))
It compares the column A to column J. If it returns True then they don't match. U can use this formula for the rest of the columns.
But Conditional Formatting should work as well I think. Just highlight both columns A and J and select Highlight Cells -> Duplicate values. Not matching results won't be highlighted.
Thank you. Row 4 reports a true discrepancy, but in Row 6 it reports not okay, but I can't seem to find the difference. Font should not matter right? Am I overlooking a difference on Row 6 or could it be that the blank fields are causing the "not okay" result.Originally Posted by tom1977
problem is that sometimes you have spacebar " " after word (I do not know is it problem or not?)
font for sure is not the problem
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Thank you, but neither one of these seem to solve my problem. The first option will take me forever to find the discrepancy because I will end up having thousands of rows when I continue and complete my project. Finding the appropriate rows would be impossible.
Using the conditional formatting doesnt seem to work because blank fields stand out and also i noticed the number discrepancies don't stand out. I appreciate the suggestion though.
having a space is not a problem, but it showing an error will be one in the long run. This database is going to be thousands of rows long and if there was a way for it to ignore spaces that would be amazing. Otherwise I guess we can just enter in the data without spacesYour thoughts?
Thanks you
you can use trim function to get rid of spaces.
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
I'm almost there- sorry for my lack of function knowledge. How might I add Trim to the function you shared with me earlier? (=if(A2&B2&C2&D2&E2&F2&G2&H2=J2&K2&L2&M2&N2&O2&P2&Q2,"ok","not ok")
hi your spaces are so call "hard" so first select all your data and use such code then my formula will be ok
Sub aa() Dim k As Range For Each k In Selection k.Value = Trim(k.Value) k.Value = Replace(k.Value, Chr(160), "") Next End Sub
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Thank you. Very helpful.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks