Compare 3 column values wit other 3 column values?

J

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
387
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
Code: 
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
Re: how to compare 3 column values wit other 3 column values?

jamiguel77 said:
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
Click to expand...

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"


[TABLE="class: grid, width: 333"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1-3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4-6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]AA[/TD]
[TD]100,67[/TD]
[TD]Found[/TD]
[TD]1[/TD]
[TD]FF[/TD]
[TD]310,21[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]BB[/TD]
[TD]210,56[/TD]
[TD]Found[/TD]
[TD]3[/TD]
[TD]GG[/TD]
[TD]119,12[/TD]
[TD]Not Found[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]FF[/TD]
[TD]310,21[/TD]
[TD]Found[/TD]
[TD]2[/TD]
[TD]BB[/TD]
[TD]210,56[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]HH[/TD]
[TD]119,12[/TD]
[TD]Not Found[/TD]
[TD]4[/TD]
[TD]AA[/TD]
[TD]100,67[/TD]
[TD]Found[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]***[/TD]
[TD]****[/TD]
[TD]********[/TD]
[TD]**********[/TD]
[TD]***[/TD]
[TD]****[/TD]
[TD]********[/TD]
[TD]**********[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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
 
Upvote 0
Re: how to compare 3 column values wit other 3 column values?

JLGWhiz thanks Worked the code.....
is possible explain the code?
Click to expand...

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.
 
Last edited:
Upvote 0
Re: how to compare 3 column values wit other 3 column values?

jamiguel77 said:
markmzz Worked too...... is possible explain pls :)
Click to expand...

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:

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

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

Markmzz
 
Upvote 0
Re: how to compare 3 column values wit other 3 column values?

Thanks, Understand now.

Really much thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A
S
  • Solved
Sequence each duplicate value in multi columns (Scripting Dictionary)
Replies
5
Views
363
Peter_SSs
Peter_SSs
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
314
mumps
mumps
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
202
myall_blues
myall_blues
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
309
Fluff
Fluff

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top