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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
666
ItalianPlatinum
I
edge37
  • Solved
Distribute a quantity value in a cell into other cells (is it even possible?)
2
Replies
14
Views
190
Peter_SSs
Peter_SSs
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
265
arun_eagertolearn
A
S
  • Solved
Sequence each duplicate value in multi columns (Scripting Dictionary)
Replies
5
Views
365
Peter_SSs
Peter_SSs
M
  • Question Question
Help with FILTER & SPILL and formatting...
Replies
0
Views
77
MeaclH
M

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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