Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Comparing four cells for data non matches

0

Hi. I have used the following formulas to see if any of the cells have matching data.

=COUNTIF(B2=F2(OR(B2=J2(OR(B2=N2,"True","False")))))

I have tried changing the OR to AND, with the same error.

 When I run the function it returns a  'too few arguments' error.

I have also researched the following;

=IF(OR(B5=F5, B5=J5, B5=N5), "Match", "")

 This runs, but displays 'Match' in every result, even when a cell does not match.

All data is in one row. If cells K7 and M9 have the same data this should not be found.

I should say I am looking for cells that do not match in one or more cells as they need correcting. The data I am testing is from a formula, not from direct input. I am never really sure if that is why many of my formulas fail to run as expected.

excel
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Mon Jan 8, 2024 at 8:48 am
Answer
Discuss

Discussion

Hi MrEMann.

You question is confusing since you refer to data on rows 2, 5 and 7 in the various formulae/ text. If the data is on row 5 then (like WillieD24). this formula works for me (in Excel 365) to detect if F5 or J5 match B5:
=IF(OR(B5=F5, B5=J5, B5=N5), "Match", "")


It's therefore hard to suggest a fix without seeing your data. Please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data (and any macros). Then we should be able to give specific help.
John_Ru (rep: 6142) Jan 8, '24 at 12:09 pm
Add to Discussion

Answers

0
Selected Answer

Hi MrEMann, 

=AND(B6=C6,C6=D6,D6=E6)

will either give you a TRUE or FALSE result in comparing four cells. Not sure what you meant by testing a formula. 

posted by: ezybao (rep: 16) on Mon Jan 8, 2024 at 10:11 am
Discuss

Discussion

@ezybao

That is not the comparison wanted. MrEMann is looking for: does F5 or J5 or N5 match B5.
WillieD24 (rep: 557) Jan 8, '24 at 12:12 pm
Thank you so much to all who have contributed to this. Sorry for the confusion on the rows too. Row 1 is the header titles, so everything starts from row 2. It took over 30 rows before it found any mismatches.

In truth I don'tunderstand why it found any mismatched cells at all. The problem is as I feared. I started a test sheet and when I entered the data manually it works fine. My sheet is getting data from sheets in other workbooks.  Therefore, I suspect it is testing the formula and not the data as I am seeing it.

I cannot understand the need to pass data between worksheets or workbooks if I cannot work on that data. It looks like a case of me trying to run before learning to walk. I am very sorry for wasting your time and efforts.

Thank you to everyone that has contributed to this problem, Happy New Year to you all.
MrEMann (rep: 20) Jan 9, '24 at 4:02 am

If you would post which formula you are testing, then we could suggest a revision which looks at (compares) the cells value (formula result) raher than comparing the cells contentents. 
For example, let's say A1 holds 5, and B1 holds 2, and C1 holds A1*B1. Then C1 = 10 gives "false" because C1 is A1*B1 , but C1.Value = 10 gives "true" because C1 value is 10.
WillieD24 (rep: 557) Jan 9, '24 at 1:27 pm
Thank you for your perseverence. I will try to add a condensed file, but not sure if I can at this stage. I did not think it would help at first because all the data references sheets you cannot access.

My original file had 50 rows and I split them into 1 to 30 and 31 to 50 so that I could see at a glance if there were errors. With hindsight, perhaps using R1 to count the errors in the Q column would have been a simpler way of checking my data.

The data as I see it shows error in N10 not picked up. Row 7 has two errors and row 8 has 3 mismatches. In theory, rows 7 and 8 should both match, but one does show an error. I am not sure how much value you can get out of my file, but I assume you think it should not matter if it is checking raw data or a formula. Pressing F2 shows that the correct cells are being checked.

Incidentally,I have gone back to my original sheets with 50 rows of formulas and 8 error at random intervals are found. I cannot work out why it works properly only partt of the time.

The formula is; =IF(OR(B2=F2, B2=J2, B2=N2), "Match", "Error")

I couldn't find any way to add a sample file. Can I edit the question?
MrEMann (rep: 20) Jan 9, '24 at 7:50 pm

Yes you can edit a question to add a file. When logged-in, at the bottom of the post you will see an Edit or Update link. Click it and the go to the bottom and use the "Add File" button to add your file. A file with even only 5 or 10 rows of data should prove helpful.
You've now also mentioned counting errors in Col "Q" which was not in your original post. Is there something about Col Q that needs to be checked/included?
Do you need to know the total number of errors or just where they are?

Are Col's F, J, and N the only columns where you want to check for matches?
If the value in those columns match the value in Col B, is that good or is that an error? (Do you want to know which ones match or which ones that don't?) I ask this because another option is to set a Conditional Formatting rule for Cols F, J, and N which would highlight the cells with errors. When the error is corrected the highlight goes away. This method will flag (point out) the cells with errors by adding a fill colour to the cell.
Let me know if you are interested in this and I will guide you through it.

Cheers   :-)
WillieD24 (rep: 557) Jan 9, '24 at 9:18 pm
Thank you. I had to modify your solution to =AND(B2=F2,J2=N2) and it works exactly how I had hope in every scenario. Thank you so much to everyone who contributed to my question.
MrEMann (rep: 20) Jan 9, '24 at 11:44 pm
MrEMann,

Glad to hear you've got it figured out.
One observation though, your final formula compares B2 to F2 and J2 to N2 which is different from your original post where you compare B2 to F2 and B2 to J2 and B2 to N2. Had your original post made it clear that you wanted to compare B2 to F2 and J2 to N2 a solution could have been found sooner.

Happy New Year and come back if you need help with anything else.

Cheers   :-)
WillieD24 (rep: 557) Jan 10, '24 at 12:47 am
Add to Discussion
0

Hello MrEMann,

I'm not sure why your second formula ~~ =IF(OR(B5=F5, B5=J5, B5=N5), "Match", "") ~~ doesn't work for you because it works for me (Excel 2007). I changed the false reuslt to "No Match" and all was good. Here is the formula I tested: =IF(OR(B5=F5, B5=J5, B5=N5),"Match","No Match"). This will only tell you if F5 or J5 or N5 match B5.

If you want to know how many cells match B5 you could use the following: =COUNTIF(B5,"="&F5)+COUNTIF(B5,"="&J5)+COUNTIF(B5,"="&N5)

A possibility is ~~ =COUNTIF(F5:N5, "="&B5) ~~. This is vialble only if G5, H5, I5, K5, L5, and M5 won't contain a value which will be in B5.

I'm sure there is probably a cleaner way to achieve the count but I haven't been able to get any attempts to work.

If this solves your problem please mark my answer as Selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Mon Jan 8, 2024 at 12:09 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login