Solved How do I automatically find duplicated and missing data

July 19, 2019 at 09:59:36
Specs: Windows 10
I have a workbook that im trying to set it up so it automatically finds missing data and duplicates.

In columns A-E and H-L I have data

What im trying to do is look in columns A-E at data and then compare against data in columns H-L and if they are both a exact match I dont need to do anything.

If data is in columns A-E but NOT in H-L then I need the data in the row that is in columns A-E being marked as "MISSING" in column "F" on the line thats missing. I.E lets say data in line 14 is in columns A-E but not H-L then I need F14 marked up as MISSING.

I also then need it to go through columns H-L and look for duplicated entries. If there is any I need them marked DUPLICATE in column "N" This one I only need to look for duplicated entries in H-L

The other issue is that all the data will not be in order so when checking against data I need check all the way down all entries in columnd H-L to check against A-E

Enclosed is file to make a bit easier,

https://1drv.ms/x/s!AlFxNGHiZueY9QA...

Thanks


See More: How do I automatically find duplicated and missing data

Reply ↓  Report •

✔ Best Answer
July 23, 2019 at 08:37:22
So just clear in my head formula look in p2 then look in q2 to q250 for a match then look in q3 looking in q2 to q250 for a match and so on....

I think you mean P3.....
but YES the formula takes the data in cell P2 and attempts to match it against the data in the cells in the range Q2 thru Q250.
As you drag down, the cell numbers change to P3, P4, P5

when you say column P has no match with column Q the data is missing from what is in theory columns I-L as we using columns I-L to merge data in column Q?

Again correct.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
July 19, 2019 at 13:01:38
Just as a quick start:

I also then need it to go through columns H-L and look for duplicated entries.

Have you tried the Remove Duplicates function on the ribbon under the Data tab?

If that does not do what you want then this formula should work.
Enter the formula in cell N2 and drag down

=IF(SUMPRODUCT(--($H$2:$H$250=H2),--($I$2:$I$250=I2),--($J$2:$J$250=J2),--($K$2:$K$250=K2),--($L$2:L$250=L2))>1,"duplicate","")

It is only setup for 250 rows. Change if needed.

I'll get back with something for your range comparison problem later today, hopefully.

Also, for future reference, Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
July 19, 2019 at 13:33:54
For the first part of your question
the simplest way is to use a couple of Helper Columns

First we need to determine what data we need,
so in column P we are going to combine all the data from ROW 2, COLUMNS B thru E
We can't use column A because it does not match Column H
In P2 enter the formula: =B2&C2&D2&E2

Next, in column Q we are going to combine all the data from ROW 2, COLUMNS I thru L
We can't use column H because it does not match Column A
In Q2 enter the formula: =I2&J2&K2&L2

Now we can compare the two columns
In cell R2 enter the formula:

=IF(ISERROR(MATCH(P2,$Q$2:$Q$250,0)),"Col P has no match in Col Q","")

See how that works for you

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
July 22, 2019 at 08:12:16
Sorry one thing I forgot to mention. The formula looking for missing data is brilliant but the data will not always be in the same row. . What I mean is that data may be in B2,C2,D2,E2 but not matching in I2,J2,K2,L2 and it could be in I99,J99,K99,L99.

Is there anyway it can look all the way down columns I,J,K,L and see if there a match for each entry?


Reply ↓  Report •

Related Solutions

#4
July 22, 2019 at 19:08:10
How do you determine where the comparison data is going to be?

Not sure it can be done with a formula. It might be possible with a macro, but need
more information on what is where and how to find it.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
July 22, 2019 at 22:41:59
Not too sure what you mean but the comparison data must be in columns I,J,K,L.

What it is the entries are probably all there in columns A-D and I-L but they are not in order as in meaning row 1 data in A-D matching data could be in row 99 in columns I-L

This is why I need something to look at the data in let’s say row 1 in columns A-D and then I need it to look down all the rows in columns I-L for an exact match. If found then OK nothing needs doing but if it in A-D and not I-L then need “missing” put in column E of the row affected.

I then just need it to repeat the function for all data in rows in columns A-D

Hope that makes sense

message edited by GDB


Reply ↓  Report •

#6
July 23, 2019 at 05:45:24
re: "Enclosed is file to make a bit easier,"

Please be aware that many of us are reluctant to open a file posted on a public website by someone who is essentially a stranger. My corporate system won't allow me to access the file even if I wanted to and I'm not comfortable opening the file on my personal device.

So that said, here is my confusion:

In your OP, you say:

What im trying to do is look in columns A-E at data and then compare against data in columns H-L and if they are both a exact match I dont need to do anything.

Then in #3 you say:

What I mean is that data may be in B2,C2,D2,E2 but not matching in I2,J2,K2,L2

Then in #5, you say:

What it is the entries are probably all there in columns A-D and I-L

Inconsistent requirements make it difficult for us to understand and then answer your question.

Which of these do you want us to work with?

A:E & H:L
B:E & I:L
A:D & I:L

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
July 23, 2019 at 06:08:54
Regardless of where your data is, use Mike's concatenation method and then use VLOOKUP.

With your concatenated data in Column P & Q, try this:

=IF(ISNA(VLOOKUP(P2,Q:Q,1,0)),"No Match","")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
July 23, 2019 at 07:05:07
.

message edited by GDB


Reply ↓  Report •

#9
July 23, 2019 at 07:08:20
Derby dad does that look all the way down to check and not just check against them on the same row. That’s where my issue is I need formula to check all the way down Q and if no match with column P then it displays no match

message edited by GDB


Reply ↓  Report •

#10
July 23, 2019 at 07:44:33
In the formula I posted:

=IF(ISERROR(MATCH(P2,$Q$2:$Q$250,0)),"Col P has no match in Col Q","")

The MATCH() function will try to match cell P2 with any cell in the Range Q2 thru Q250
as you drag the formula down, it will try to match P3, then P4 etc etc.

In my reply #2 I failed to indicate that you needed to drag the formula down as many rows as needed.

Sorry for the confusion.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#11
July 23, 2019 at 08:09:50
That’s fine mike .

So just clear in my head formula look in p2 then look in q2 to q250 for a match then look in q3 looking in q2 to q250 for a match and so on....

Just to also confirm when you say column P has no match with column Q the data is missing from what is in theory columns I-L as we using columns I-L to merge data in column Q?


Reply ↓  Report •

#12
July 23, 2019 at 08:37:22
✔ Best Answer
So just clear in my head formula look in p2 then look in q2 to q250 for a match then look in q3 looking in q2 to q250 for a match and so on....

I think you mean P3.....
but YES the formula takes the data in cell P2 and attempts to match it against the data in the cells in the range Q2 thru Q250.
As you drag down, the cell numbers change to P3, P4, P5

when you say column P has no match with column Q the data is missing from what is in theory columns I-L as we using columns I-L to merge data in column Q?

Again correct.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
July 23, 2019 at 09:16:41
If it will make what's happening clearer,
you can always change the Error message to something else, like:

=IF(ISERROR(MATCH(P2,$Q$2:$Q$250,0)),"Cols B thru E has NO MATCH in Cols I thru L,"")

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#14
July 23, 2019 at 11:12:14
re: "Derby dad does that look all the way down to check and not just check against them on the same row."

Did you try my formula? Did you read the Help file on VLOOKUP?

Yes, the VLOOKUP function searches the entire range, top to bottom, for the value being searched for. It stops looking once it finds the first occurrence of a match, but in your case that doesn't matter because you just want to know if the value exists, period.

VLOOKUP is much more powerful than is shown in this usage, but it works here and it introduces you to a function that you should become familiar with.

Here's one of hundreds of sites that explain the usage of VLOOKUP.

https://www.contextures.com/xlFunct...


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question