Microsoft Excel - Data Matching Help

Status
Not open for further replies.
R

RD274

I.can.edit.this?omg
Galvanizer
My moms new job requires that she goes through large lists of data in Excel and check to see if values match or not. So she asked me to figure out a quick formula to check if a value matchs or not.

So using IF and VLOOKUP, I managed to get it to compare values of single cells. But I have no idea how to get it to search through the entire list and identify which values match and which don't match.

I've tried Goal Seek but I can't get it to work with my data. Any other simple solution?

--- Updated Post - Automerged ---

temp.jpg

^Heres my current temporary worksheet. I've got table blue and table red, both are identical except value 9 of table blue.

I'm looking for a simple solution to go through the values of table blue and table red and report any false value (Eg. value 9 of blue table) or at least stop at a false value.
 
View attachment 12731

try this. quick and dirty solution. highlights any non matching cells in red

ensure that column 1 is sorted in ascending

click on any cell in column 2 and have a look at its conditional formatting rules

pretty easy. really. I sense that you want something a bit more complex, but cant quite put my finger on it

other alternative is to export as csv and run them through diff or some sort of compare tool
 

Attachments

  • Like
Reactions: 1 person
That conditional formatting method is really sweet. I'd never have thought of that even thought the Condition Formatting button was just lying. Problem is teaching my mom to use this method but I think she'll manage ....

On the contrary I need something simple so that my mom can learn it and implement it at her office and this seems to cut it. The Export CSV method also sounds nice but her laptop has tons of security restrictions making it difficult to install any other compare tool.

Can I code a Windows Batch File (.bat) into processing a csv file?
 
If both tables are sorted, the easiest way to achieve this is using conditional formatting. You can select one of the top cells in either of the tables, let's say we take E1 from the blue table, go to Conditional Formatting, and set the color as Red if E1 <> B1. Ensure there are no $ symbols. Now just double click when the + sign appears at the bottom of E1, the formula is applied to the entire range :)
 
  • Like
Reactions: 1 person
I will provide you a simple solution.
Suppose your data is in Column B and is to be compared with Column D. (As shown in the snapshot)
Make a new column where you want the result wether the data is matching or not to be displayed. (Eg-Match A & B i.e. column E)
Use the formula in column E:
=IF(AND(B2=D2),"YES", "NO")
And just drag the formula till the end.
Wherever data would match, it would display a YES or else a NO.

207ault.jpg
 
  • Like
Reactions: 1 person
rocker123 said:
=IF(AND(B2=D2),"YES", "NO")
Click to expand...
Not required to write the whole thing. You can simply put what you have written as

=b2=d2

Being a boolean operation, it would put in TRUE or FALSE by default
 
agantuk said:
Not required to write the whole thing. You can simply put what you have written as

=b2=d2

Being a boolean operation, it would put in TRUE or FALSE by default
Click to expand...
Ya, you can use this as well, much more easier. :-)
 
most compare tools are standalone executables. You should be able to get them running by just copying the main exe to the laptop. we had to work with a similar set of problems :D

EDIT: do you want to ensure that the corresponding cells match ( Cell A1= Cell D1) or check if D1 exists in Column A ?
 
assuming you want to check col A with col C,

Use formula =IF(ISERROR(MATCH(A1,C:C,0)),"Not Present", "Present") in B1 and drag down..

This will check if value present in cell of col A of current row, is present anywhere in col C or not..
 
  • Like
Reactions: 1 person
Ok.....

Quickly put this one together.

It will compare column A for existence in column B (can easily be changed to show row vs. row). You will also get a concatenated list of unmatched values, and the count. Right now is set to run on a depth of ~100 rows.

Hope this helps....!

Link to file.
 
  • Like
Reactions: 1 person
rocker123 said:
I will provide you a simple solution.
Suppose your data is in Column B and is to be compared with Column D. (As shown in the snapshot)
Make a new column where you want the result wether the data is matching or not to be displayed. (Eg-Match A & B i.e. column E)
Use the formula in column E:
=IF(AND(B2=D2),"YES", "NO")
And just drag the formula till the end.
Wherever data would match, it would display a YES or else a NO.
Click to expand...

Thanks. It would still require a person to go through the entire column E, but it sounds quite easy to implement :)

greenhorn said:
most compare tools are standalone executables. You should be able to get them running by just copying the main exe to the laptop. we had to work with a similar set of problems :D

do you want to ensure that the corresponding cells match ( Cell A1= Cell D1) or check if D1 exists in Column A ?
Click to expand...

Not on my moms laptop. Its basically a TATA workstation which is practically in a reduced user state mode. She can only use the applications which an administrator installs on her laptop. Thats why I was looking for a native solution at the very least....

I wanted to ensure that the corresponding cells matched of each table (Cells beside A1 = Cells beside D1)
nihit said:
assuming you want to check col A with col C,
Use formula =IF(ISERROR(MATCH(A1,C:C,0)),"Not Present", "Present") in B1 and drag down..

This will check if value present in cell of col A of current row, is present anywhere in col C or not..
Click to expand...

It would still require me to go through the B1 list to verify where the matchs are. But I guess I'll be content with this lolz .....

asingh said:
Ok.....
Quickly put this one together.

It will compare column A for existence in column B (can easily be changed to show row vs. row). You will also get a concatenated list of unmatched values, and the count. Right now is set to run on a depth of ~100 rows.

Hope this helps....!
Link to file.
Click to expand...

I don't understand how you scripted this. Could you tell me how you made this, accordingly I can teach my mother. Besides the depth is too low. I need it be ranging from somewhere between 100 ~ 500.

-----------------------

On the side note. Thanks everyone for the suggestions (including those whom I haven't quoted) :) Rep+
 
^^

If my version is good to go, let me know the modifications I will set it for a depth of ~5000. It should run fine. Can also explain how I created it.
 
oh ok. my sheet is for if cell in B exists in A. for the former, the simple formula that the others gave :)
 
Status
Not open for further replies.
Top Bottom