Excel column comparison
Closed
sunil
-
Jan 11, 2010 at 09:29 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 12, 2010 at 09:45 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 12, 2010 at 09:45 PM
Related:
- Excel column comparison
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 11, 2010 at 09:24 PM
Jan 11, 2010 at 09:24 PM
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.
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 12, 2010 at 09:45 PM
Jan 12, 2010 at 09:45 PM
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
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.
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.
