Compare 3 column values wit other 3 column values?

[jamiguel77]

hi all
i have this:

4 AA 100.67 1 FF 310.21
2 BB 210.56 3 GG 119.12
1 FF 310.21 2 BB 210.56
3 HH 119.12 4 AA 100.67


if you see, all 3 first columns exist on the other group... right

Except

3 HH 119.12

and

3 GG 119.12


how to detect this?


thanks

 

[JLGWhiz]

Re: how to compare 3 column values wit other 3 column values?

Here is one way. It assumes data in columns A:F and uses columns H:I as helper colulmn, then removes contents of H:I when mismatched Items are identified.

Sub t()
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    c.Offset(, 7) = c.Value & c.Offset(, 1).Value & c.Offset(, 2).Value
Next
For Each c In Range("D2", Cells(Rows.Count, 4).End(xlUp))
    c.Offset(, 5) = c.Value & c.Offset(, 1).Value & c.Offset(, 2).Value
Next
For Each c In Range("H2", Cells(Rows.Count, 8).End(xlUp))
    If Application.CountIf(Range("I:I"), c.Value) = 0 Then
        c.Offset(, -7).Resize(, 3).Interior.Color = vbYellow
    End If
Next
For Each c In Range("I2", Cells(Rows.Count, 9).End(xlUp))
    If Application.CountIf(Range("H:H"), c.Value) = 0 Then
        c.Offset(, -5).Resize(, 3).Interior.Color = vbYellow
    End If
Next
Range("H:I").EntireColumn.ClearContents
End Sub

 

[jamiguel77]

Re: how to compare 3 column values wit other 3 column values?

Not work your code see:

Before:
https://imgur.com/a/DOIS7

After Click on Button (with your code).....
https://imgur.com/a/uEZw9


Any advice?

much thanks for your answer.

 

[JLGWhiz]

Re: how to compare 3 column values wit other 3 column values?

The code I posted will highlight only the three cell range that does NOT match values in the other set of columns. In the tests that I ran the code works as expected. I cannot tell from the images posted if it worked because the beginning cells were already highlighted. The OP did not specify what to do if matches were found or not found, so I improvised and opted to show only non-matching cells. What do you want to see as a result of the comparison between the two sets of columns?

 

[markmzz]

Re: how to compare 3 column values wit other 3 column values?

hi all
i have this:

4 AA 100.67 1 FF 310.21
2 BB 210.56 3 GG 119.12
1 FF 310.21 2 BB 210.56
3 HH 119.12 4 AA 100.67

if you see, all 3 first columns exist on the other group... right
Except

3 HH 119.12

and

3 GG 119.12

how to detect this?

thanks

Maybe the formulas below can help:

In D2 and copy down

=IF(SUM(--(MMULT(COUNTIF(A2:C2,$E$2:$G$5),{1;1;1})=3)),"","Not ")&"Found"

In H2 and copy down ​

=IF(SUM(--(MMULT(COUNTIF(E2:G2,$A$2:$C$5),{1;1;1})=3)),"","Not ")&"Found"

	A	B	C	D	E	F	G	H
1	1	2	3	1-3	4	5	6	4-6
2	4	AA	100,67	Found	1	FF	310,21	Found
3	2	BB	210,56	Found	3	GG	119,12	Not Found
4	1	FF	310,21	Found	2	BB	210,56	Found
5	3	HH	119,12	Not Found	4	AA	100,67	Found
6
****	***	****	********	**********	***	****	********	**********

<tbody>
</tbody>

Markmzz

 

[JLGWhiz]

Re: how to compare 3 column values wit other 3 column values?

It is possible that you have leading or trailing spaces that caused the results you gor from the code. This version will eliminate that problem.

Sub t2()
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    c.Offset(, 7) = Trim(c.Value) & Trim(c.Offset(, 1).Value) & Trim(c.Offset(, 2).Value)
Next
For Each c In Range("D2", Cells(Rows.Count, 4).End(xlUp))
    c.Offset(, 5) = Trim(c.Value) & Trim(c.Offset(, 1).Value) & Trim(c.Offset(, 2).Value)
Next
For Each c In Range("H2", Cells(Rows.Count, 8).End(xlUp))
    If Application.CountIf(Range("I:I"), c.Value) = 0 Then
        c.Offset(, -7).Resize(, 3).Interior.Color = vbYellow
    End If
Next
For Each c In Range("I2", Cells(Rows.Count, 9).End(xlUp))
    If Application.CountIf(Range("H:H"), c.Value) = 0 Then
        c.Offset(, -5).Resize(, 3).Interior.Color = vbYellow
    End If
Next
Range("H:I").EntireColumn.ClearContents
End Sub

 

[jamiguel77]

Re: how to compare 3 column values wit other 3 column values?

JLGWhiz thanks Worked the code.....
is possible explain the code? (if not not problem, i try understand).. thanks

markmzz Worked too...... is possible explain pls

i am grateful with the help really impresionant both...

Thanks, much thanks

 

[JLGWhiz]

Re: how to compare 3 column values wit other 3 column values?

JLGWhiz thanks Worked the code.....
is possible explain the code?

Loops through to concatenate the values in columns A:C and then columns D:F and place the concatenated range values in columns H:I where it can then compare them as single values rather than three separate values. Once the concatenation is complete then it loops through column H to check column I for a match, If none is found it then highlights the corrseponding range of cells in A:C. Tha same is done with column I against column H and unmatched items are then highlighted in columns D:F. Once all items are checked, the helper columns H:I are cleared of data.

 

[markmzz]

Re: how to compare 3 column values wit other 3 column values?

markmzz Worked too...... is possible explain pls

You're welcome and thanks for the feedback.

I think that the best way to understand the formula is to browse to the formula (step by step).

Lets go:

	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	1-3	4	5	6	4-6		Formula in D5	Details
2	4	AA	100,67	Found	1	FF	310,21	Found	Steps	=IF(SUM(--(MMULT(COUNTIF(A5:C5,$E$2:$G$5),{1;1;1})=3)),"","Not ")&"Found"	Start formula
3	2	BB	210,56	Found	3	GG	119,12	Not Found	1	=IF(SUM(--(MMULT({0\0\0;1\0\1;0\0\0;0\0\0},{1;1;1})=3)),"","Not ")&"Found"	Counts the number of occurrences of the caracters 3, HH
4	1	FF	310,21	Found	2	BB	210,56	Found			and 119,12 in the "columns" E, F e G and create a matrix
5	3	HH	119,12	Not Found	4	AA	100,67	Found			4 rows x 3 columns with the count of the found caracters
6											{0\0\0;1\0\1;0\0\0;0\0\0}.
7									2	=IF(SUM(--({0;2;0;0}=3)),"","Not ")&"Found"	Now, the function MMULT multiplies the matrix 4x3
8											{0\0\0;1\0\1;0\0\0;0\0\0} for the matrix 3x1 {1;1;1} to create
9											a matrix 4x1 {0;2;0;0} with the sum (row to row) of the founds
10											caracters with the function COUNTIF.
11									3	=IF(SUM(--({FALSE;FALSE;FALSE;FALSE})),"","Not ")&"Found"	After that, compare the values of the matrix 4x1 with the
12											number 3 to know if the 3 caracters was found or not in
13											one row {FALSE;FALSE;FALSE;FALSE}.
14									4	=IF(SUM({0;0;0;0}),"","Not ")&"Found"	Now, the --(the same of -1*-1*) change FALSE to zero
15											({0;0;0;0}).
16									5	=IF(0,"","Not ")&"Found"	Then, the function SUM sum the values of the matrix 4x1
17											({0;0;0;0}) and the result is zero (0).
18									6	="Not "&"Found"	After that, as zero is like FALSE, then the IF function choose the
19											option "Not "(third argument of the function IF).
20									7	="Not found"	Finally, we have the junction of the two texts and the result is
21											"Not found"
22
****	***	****	*******	**********	***	****	********	**********	******	****************************************************************************	*************************************************************

<tbody>
</tbody>

I hope that the step by step above help you to understand the formula.

Markmzz

 

[jamiguel77]

Re: how to compare 3 column values wit other 3 column values?

Thanks, Understand now.

Really much thanks.