Excel column comparison

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I am trying to compare severalcolumns in excel which have very large data sets- eg. column A & B- (A1:A3000) and (B1:B380). I want to find out all values of B in column A and if exists then highlight the match in column B. Similarly compare column C with column A, and C has 120 data sets. If it exists in column A then highlight the match in column C with a different or same colour, and so on. I have several columns which I want to compare with master column A. Can someone help me with this problem. I will greatly appreciate the help. Please also email me the solution.
Thanks
Sunil

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
assume there is no gap in data in any coumn . columns can have diffrent set of data

try this macro
if there is problem please tell clearly what is the problem, where in the code statements the macro stops and error message that comes up.
This macro will test cells in column 1 with cells in each and all of the columns to the right.
Sub test()
Dim r1 As Range, r0 As Range, c1 As Range, cfind As Range, x
Dim j As Integer, k As Integer
On Error Resume Next
Set r1 = Range(Range("A2"), Range("A2").End(xlDown))
j = Range("A2").End(xlToRight).Column
For Each c1 In r1
x = c1.Value
For k = 2 To j
Set r0 = Range(Cells(2, k), Cells(2, k).End(xlDown))
Set cfind = r0.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then cfind.Interior.ColorIndex = 6
Next k
Next c1
End Sub
Thanks Venkat!

I ran the macro with three sets of data but it did not highlight the data in the third set which is common to set 1. I also may have multiple numbers repeated and want them to highlight. This is to be used in comparing master entry (column 1) and subset entries from individual vendors (columns 2 thru 6). The actual example of issue is described here-

Column A contains data of all enteries from a bank statement for FY 2009.

Column B contains data of payments received for a vendor XXX of all enteries done in quicken for FY 2009.

Column C contains data of payments received from a vendor YYY of all enteries done in Quicken for FY 2009.

Column D contains data of payments paid to a vendor ZZZ for FY 2009. and so on.................

I want to see if all data in columns B thru D(...) exists in column A and/or all data in column A matches with column B thru D (in this case)...

I want to match in both direction so that all entries in columns B onwards exists in A and all entries done in A also exists in B onwards.

The result of your macro is shown below -

<code><code>QB FCU
1 1 1
2 3 2
3 5 4
4 7 8
5 9 16
6 11 21
7 13 27
8 15 33
9 17 39
10 19 45
11 21 51
12 23 57
13 25 63
14 27 69
15 29 128
16 31 64
17 33 32
18 35 16
19 37 8
20 39 4
21 41 2
22 43 1
23 45 3
24 47 3
25 49 5
26 51 7
27 53 9
28 55 11
29 57 13
30 59 59
31 2 76
32 4 89
33 6 4
34 8 6
35 10 43
36 12 32
37 14 12
38 16 21
39 18 34
40 20 56
41 22 78
42 24 90
43 26 9
44 28 87
45 30 65
46 32 43
47 34 21
48 36 13
49 38 14
50 40 25

it missed few numbers in column 3 which do exists in column A? eg:1, 21, 27,33,39,45,16,8,4,2,3,32,9,43,21,13

Thanks for all your help and support.

Sunil
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
take the sample data as given below in a separate excel worksheet

hdng1 hdng2 hdgfn3 hdng4
4 8 7 9
5 5 1 6
3 8 8 8
9 7 3 7
3 4 1 6
2 9 8 5
5 4 9 4
5 5 1 7


On this sheet s run the modified macro given below

Sub test()
Dim r1 As Range, r0 As Range, c1 As Range, cfind As Range, x
Dim j As Integer, k As Integer, add As String
On Error Resume Next
ActiveSheet.Cells.Interior.ColorIndex = xlNone
Set r1 = Range(Range("A2"), Range("A2").End(xlDown))
j = Range("A2").End(xlToRight).Column
For Each c1 In r1
x = c1.Value
For k = 2 To j
Set r0 = Range(Cells(2, k), Cells(2, k).End(xlDown))
Set cfind = r0.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Interior.ColorIndex = 6
add = cfind.Address
End If
Do
Set cfind = r0.Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind.Interior.ColorIndex = 6
Loop
Next k
Next c1
End Sub


Scrutinise the result after running the macro,
if the result on this sample data is ok for you then use the modified macro to your original data.