Microsoft Excel - Macro for compare 2 range in one Excel sheet

Asked By John on 08-Nov-10 04:59 AM

I have an Excel file given as below. I want to compare Two Range in one excel sheet.

Range1 is Total Panel (Blue Highlighted) Rage2 is Yellow Highlighted. I want which panel nos. missing in range 1. Missing panels should be highlighted with any other colour.

Can you any one give a solution for this. I got a macro for this but not working properly. Can anyone to help to make it work properly.

Sub MarkDifferences()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean

    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range",
' "")
name1 = "Range1"
    If Len(name1) = 0 Then Exit Sub
    Set range1 = active_sheet.Range(name1)

'    name2 = InputBox$("Second Range Name:", "Second
' Range", "")
name2 = "Range2"
    If Len(name2) = 0 Then Exit Sub
    Set range2 = active_sheet.Range(name2)

    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In range1.Cells
      key = cell1.Row - range1.Row & "," & cell1.Column - _
        range1.Column
      cells1.Add cell1, key
    Next cell1

    Set cells2 = New Collection
    For Each cell2 In range2.Cells
      key = cell2.Row - range2.Row & "," & cell2.Column - _
        range2.Column
      cells2.Add cell2, key
    Next cell2

    ' Examine the cells in the first collection.
    For Each cell1 In cells1
      On Error Resume Next
      Err.Clear
      key = cell1.Row - range1.Row & "," & cell1.Column - _
        range1.Column
      Set cell2 = cells2(key)
      If Err.Number <> 0 Then
        ' The second cell is missing.
        no_match = True
      ElseIf cell1.Text <> cell2.Text Then
        ' The cells don't match.
        no_match = True
      Else
        no_match = False
      End If

      ' If the cells don't match, color cell1.
      If no_match Then
        With cell1.Interior
          .ColorIndex = 35
          .Pattern = xlSolid
        End With
      Else
        With cell1.Interior
          .ColorIndex = xlNone
        End With
      End If
    Next cell1

    ' Examine the cells in the second collection.
    For Each cell2 In cells2
      On Error Resume Next
      Err.Clear
      key = cell2.Row - range2.Row & "," & cell2.Column - _
        range2.Column
      Set cell1 = cells1(key)
      If Err.Number <> 0 Then
        ' The second cell is missing.
        no_match = True
      ElseIf cell2.Text <> cell1.Text Then
        ' The cells don't match.
        no_match = True
      Else
        no_match = False
      End If

      ' If the cells don't match, color cell2.
      If no_match Then
        With cell2.Interior
          .ColorIndex = 35
          .Pattern = xlSolid
        End With
      Else
        With cell2.Interior
          .ColorIndex = xlNone
        End With
      End If
    Next cell2
End Sub

ZONE -1 BLOCK -1			ZONE -1 BLOCK - 2			ZONE -1 BLOCK - 13			ZONE -1 BLOCK - 14			ZONE -1 BLOCK - 15			ZONE -1 BLOCK - 16			ZONE -1 BLOCK - 17			ZONE -1 BLOCK - 18			TOTAL
SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	SL.NO	PANEL NO.	REQD	PANEL NO.	REQD
SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	SL.NO	PANEL NO.	QTY	PANEL NO.	QTY
1	IP-01	44	1	IP-01	42	1	IP-01	57	1	IP-01	25	1	IP-01	74	1	IP-01	71	1	IP-01	54	1	IP-01	71	IP-01	438
2	IP-03	4	2	IP-D-160	1	2	IP-05	1	2	IP-02	50	2	IP-03	11	2	IP-01A	1	2	IP-03	29	2	IP-02	2	IP-01A	1
3	IP-D-160	2	3	IP-D-180	4	3	IP-D-160	3	3	IP-03	16	3	IP-0-52	1	3	IP-03	11	3	IP-D-165	2	3	IP-03	20	IP-03	91
4	IP-D-180	3	4	IP-D-180S	6	4	IP-D-180	4	4	IP-0-58	4	4	IP-0-58	1	4	IP-0-58	1	4	IP-D-180	11	4	IP-05	2	IP-D-160	7
5	IP-D-180S	5	5	IP-0-59	4	5	IP-D-180S	4	5	IP-0-60	1	5	IP-0-62	1	5	IP-0-60	2	5	IP-D-180S	2	5	IP-D-160	1	IP-D-162	8
6	IP-0-66	2	6	IP-0-64	1	6	IP-0-60	5	6	IP-0-62	1	6	IP-0-70	4	6	IP-0-68	3	6	IP-0-58	2	6	IP-D-180	9	IP-D-165	2
7	IP-0-72	1	7	IP-0-68	1	7	IP-0-66	1	7	IP-0-66	2	7	IP-0-78	4	7	IP-0-72	1	7	IP-0-64	2	7	IP-D-180S	7	IP-D-180	49
8	IP-0-84	1	8	IP-0-82	1	8	IP-0-82	1	8	IP-0-68	2	8	IP-0-82	3	8	IP-0-76	1	8	IP-0-70	1	8	IP-0-58	2	IP-D-180S	50
9	IP-0-86	1	9	IP-0-95	1	9	IP-0-86	1	9	IP-0-72	1	9	IP-0-86	1	9	IP-0-80	3	9	IP-0-74	1	9	IP-0-60	1	IP-0-58	10
10	IP-0-90	2	10	IP-1-02	1	10	IP-1-00	1	10	IP-0-74	1	10	IP-0-90	1	10	IP-0-84	2	10	IP-0-78	2	10	IP-0-66	1	IP-0-59	4
11	IP-1-08	1	11	IP-1-23	2	11	IP-1-02	1	11	IP-0-76	1	11	IP-0-96	2	11	IP-0-98	3	11	IP-0-80	1	11	IP-0-70	1	IP-0-60	9
12	IP-1-24	1	12	IP-2-80	1	12	IP-1-28	1	12	IP-0-86	1	12	IP-2-30	1	12	IP-2-94	2	12	IP-0-86	2	12	IP-0-72	1	IP-0-64	3
13	IP-1-40	1	13	IP-2-96	2	13	IP-2-82	2	13	IP-0-88	1	13	IP-2-82	2	13	IP-3-24	2	13	IP-0-96	1	13	IP-0-74	1	IP-0-66	6
14	IP-2-76	1	14	IP-3-01	2	14	IP-2-92	2	14	IP-0-94	1	14	IP-3-20	1	14	IP-3-40	2	14	IP-0-98	3	14	IP-0-76	1	IP-0-68	6
15	IP-2-90	2	15	IP-3-23	2	15	IP-2-96	3	15	IP-1-42	1	15	IP-3-42	1	15	IP-3-48	2	15	IP-2-82	1	15	IP-0-78	1	IP-0-70	6
16	IP-2-92	2	16	IP-3-37	2	16	IP-3-04	2	16	IP-2-12	1	16	IP-3-44	1	16	IP-3-62	1	16	IP-2-98	2	16	IP-0-80	3	IP-0-72	4
17	IP-2-96	4	17	IP-3-66	1	17	IP-3-30	3	17	IP-2-40	1	17	IP-3-66	1	17	IP-3-76	1	17	IP-3-10	1	17	IP-0-82	2	IP-0-74	3
18	IP-2-98	2	18	IP-3-72	3	18	IP-3-38	2	18	IP-2-98	2	18	IP-3-70	1	18	IP-3-78	6	18	IP-3-26	1	18	IP-0-84	1	IP-0-76	3
19	IP-3-48	1	19	IP-3-78	2	19	IP-3-52	2	19	IP-3-96	3	19	IP-3-78	1	19	IP-3-98	2	19	IP-3-30	1	19	IP-0-94	1	IP-0-78	7
20	IP-3-50	1	20	IP-3-85	2	20	IP-3-62	1	20	IP-4-08	1	20	IP-3-80	6	20	IP-4-04	2	20	IP-3-34	2	20	IP-0-98	3	IP-0-80	7
21	IP-3-52	1	21	IP-3-89	2	21	IP-3-78	3	21	IP-4-12	1	21	IP-3-86	1	21	IP-4-06	1	21	IP-3-36	1	21	IP-1-08	1	IP-0-82	7
22	IP-3-64	1	22	IP-4-07	2	22	IP-3-84	1	22	IP-4-52	1	22	IP-3-90	2	22	IP-4-08	1	22	IP-3-38	2	22	IP-2-76	1	IP-0-84	4
23	IP-3-78	1	23	IP-4-45	1	23	IP-4-02	2	23	IP-4-54	1	23	IP-3-92	1	23	IP-4-10	1	23	IP-3-52	1	23	IP-2-88	1	IP-0-86	6
24	IP-3-80	2	24	IP-4-74	1	24	IP-4-26	1	24	IP-4-64	1	24	IP-4-00	2	24	IP-4-12	1	24	IP-3-54	1	24	IP-2-94	2	IP-0-90	3
25	IP-3-96	1	25	IP-4-87	1	25	IP-5-00	1	25	IP-4-66	1	25	IP-4-08	1	25	IP-4-14	1	25	IP-3-56	1	25	IP-2-96	1	IP-0-95	1
26	IP-4-04	1	26	IP-5-24	1	26	IP-5-06	1	26	IP-4-68	1	26	IP-4-30	3	26	IP-4-18	4	26	IP-3-58	1	26	IP-3-00	1	IP-0-96	3
27	IP-4-18	2	27	IP-5-47	2	27	IP-5-18	1	27	IP-4-72	2	27	IP-4-32	3	27	IP-4-20	1	27	IP-3-82	1	27	IP-3-26	2	IP-0-98	9
28	IP-4-22	1	28	IP-5-38	1	28	IP-5-38	1	28	IP-4-76	2	28	IP-4-40	1	28	IP-4-22	1	28	IP-3-88	1	28	IP-3-28	1	IP-1-02	2
29	IP-4-36	1	29	IP-5-66	2	29	IP-5-44	1	29	IP-4-90	1	29	IP-4-50	2	29	IP-4-26	3	29	IP-3-94	1	29	IP-3-36	1	IP-1-08	2
30	IP-4-52	1	30	IP-5-69	10	30	IP-5-58	2	30	IP-4-92	2	30	IP-4-54	1	30	IP-4-28	1	30	IP-4-08	1	30	IP-3-44	1	IP-1-23	2
31	IP-4-70	3	31	IP-5-86	1	31	IP-5-54	1	31	IP-4-96	1	31	IP-4-62	1	31	IP-4-38	1	31	IP-4-38	1	31	IP-3-46	1	IP-1-24	1
32	IP-4-74	4	32	IP-5-89	2	32	IP-5-60	1	32	IP-5-14	2	32	IP-4-64	1	32	IP-4-58	1	32	IP-4-60	1	32	IP-3-52	1	IP-1-40	1
33	IP-5-44	1			107	33	IP-5-64	2	33	IP-5-16	1	33	IP-4-88	1	33	IP-4-84	1	33	IP-4-66	2	33	IP-3-58	1	IP-1-42	1
34	IP-5-50	2				34	IP-5-68	3	34	IP-5-24	1	34	IP-5-40	1	34	IP-5-00	1	34	IP-4-74	1	34	IP-3-70	1	IP-2-76	2
35	IP-5-60	1				35	IP-5-70	8	35	IP-5-26	2	35	IP-5-58	2	35	IP-5-02	1	35	IP-5-14	1	35	IP-3-72	1	IP-2-80	1
36	IP-5-66	1				36	IP-5-76	1	36	IP-5-28	2	36	IP-5-84	1	36	IP-5-22	1	36	IP-5-56	3	36	IP-4-04	1	IP-2-82	5
37	IP-5-68	1					TOTAL	127	37	IP-5-32	1	37	IP-5-62	2	37	IP-5-24	1	37	IP-5-60	1	37	IP-4-14	1	IP-2-90	2
38	IP-5-68A	1							38	IP-5-34	2	38	IP-5-60	1	38	IP-5-46	1	38	IP-5-62	1	38	IP-4-22	1	IP-2-92	4
39	IP-5-70	1							39	IP-5-48	1	39	IP-5-68	3	39	IP-5-52	1	39	IP-5-64	4	39	IP-4-34	3	IP-2-94	4
40	IP-5-72	1							40	IP-5-50	1	40	IP-5-78	2	40	IP-5-54	2	40	IP-5-66	2	40	IP-4-38	4	IP-2-96	10
41	IP-5-74	2							41	IP-5-56	1	41	IP-5-82	1	41	IP-5-58	1	41	IP-5-68	1	41	IP-4-44	1	IP-2-98	6
42	IP-5-82	1							42	IP-5-60	5	42	IP-5-48	1	42	IP-5-62	2	42	IP-5-70	6	42	IP-4-82	1	IP-3-00	1
43	IP-5-90	1							43	IP-5-64	2	43	IP-5-56	1	43	IP-5-64	2	43	IP-5-72	1	43	IP-5-18	1	IP-3-01	2
44	IP-5-98	1							44	IP-5-66	3	44	IP-5-66	7	44	IP-5-66	2	44	IP-5-74	1	44	IP-5-26	1	IP-3-10	1
		114							45	IP-5-68	1	45	IP-5-30	1	45	IP-5-68	2	45	IP-5-78	4	45	IP-5-34	2	IP-3-23	2
									46	IP-5-70	9	46	IP-5-14	1	46	IP-5-70	5	46	IP-5-86	1	46	IP-5-38	1	IP-3-24	2
114									47	IP-5-74	3	47	IP-5-92	1	47	IP-5-72	3	47	IP-5-92	3	47	IP-5-44	1	IP-3-26	3
107									48	IP-5-78	1	48	IP-5-76	2	48	IP-5-74	3		TOTAL	166	48	IP-5-48	1	IP-3-30	4
127									49	IP-5-84	1	49	IP-5-86	1	49	IP-5-78	1				49	IP-5-52	1	IP-3-34	2
188									50	IP-5-86	2	50	IP-5-70	4	50	IP-5-84	1				50	IP-5-54	1	IP-3-36	2
194									51	IP-D-162	3	51	IP-5-88	2	51	IP-5-92	2				51	IP-5-56	1	IP-3-37	2
187									52	IP-D-180	6	52	IP-5-34	1	52	IP-5-96	1				52	IP-5-60	1	IP-3-38	4
166									53	IP-D-180S	8	53	IP-5-16	1	53	IP-D-162	2				53	IP-5-64	3	IP-3-40	2
194										TOTAL	188	54	IP-5-74	1	54	IP-D-180	5				54	IP-5-66	2	IP-3-48	3
												55	IP-6-14	1	55	IP-D-180S	10				55	IP-5-68	6	IP-3-50	1
###												56	IP-D-162	3		TOTAL	187				56	IP-5-70	3	IP-3-52	5
												57	IP-D-180	7							57	IP-5-72	1	IP-3-54	1
												58	IP-D-180S	8							58	IP-5-76	3	IP-3-56	1
													TOTAL	194							59	IP-5-78	2	IP-3-58	2
																					60	IP-5-82	1	IP-3-62	2
	1277																				61	IP-5-92	2	IP-3-64	1
	1130																					TOTAL	194	IP-3-66	2
	147																							IP-3-72	4
																								IP-3-76	1
																								IP-3-78	13
																								IP-3-80	8
																								IP-3-82	1
																								IP-3-85	2
																								IP-3-88	1
																								IP-3-89	2
																								IP-3-94	1
																								IP-3-96	4
																								IP-4-04	4
																								IP-4-06	1
																								IP-4-07	2
																								IP-4-08	4
																								IP-4-10	1
																								IP-4-12	2
																								IP-4-14	2
																								IP-4-18	6
																								IP-4-20	1
																								IP-4-22	3
																								IP-4-26	4
																								IP-4-28	1
																								IP-4-36	1
																								IP-4-38	6
																								IP-4-45	1
																								IP-4-52	2
																								IP-4-58	1
																								IP-4-60	1
																								IP-4-66	3
																								IP-4-70	3
																								IP-4-74	6
																								IP-4-84	1
																								IP-4-87	1
																								IP-5-00	2
																								IP-5-02	1
																								IP-5-14	4
																								IP-5-22	1
																								IP-5-24	3
																								IP-5-38	3
																								IP-5-44	3
																								IP-5-46	1
																								IP-5-47	2
																								IP-5-50	3
																								IP-5-52	2
																								IP-5-54	4
																								IP-5-56	6
																								IP-5-58	5
																								IP-5-60	10
																								IP-5-62	5
																								IP-5-64	13
																								IP-5-66	19
																								IP-5-68	17
																								IP-5-68A	1
																								IP-5-69	10
																								IP-5-70	36
																								IP-5-72	6
																								IP-5-74	10
																								IP-5-78	10
																								IP-5-82	3
																								IP-5-84	3
																								IP-5-86	5
																								IP-5-89	2
																								IP-5-90	1
																								IP-5-92	8
																								IP-5-96	1
																								IP-5-98	1
																								TOTAL	1130

Rolf Jaeger replied to John on 08-Nov-10 12:24 PM

Hi John:

your post seems to have been clipped somewhere in the process (the Total panel did not show at all). Please repost with just a subset of your worksheet (or alternatively upload the workbook).

Also, please explain how you would like the results of the comparison be presented to you.

Best wishes,
Rolf

John replied to Rolf Jaeger on 14-Nov-10 05:57 AM

Thank you for your reply. I added here with my Excel file zip format .

Book2.zip

This is my worksheet. My range 2 is Column A to columns P. (A1,A2 to P1,P2 contains the columns header. Range1 starting from Column T1 and Column U1.
(T1,T2, U1, U2 are column Header). Is it possible to upload my excel sheet anyway here. I don't know how to upload excel file here.

I want to check range2 for which panels are not added in range1. Some panels No. are missing in range1. eg. IP-5-18 not in range1 but it is on range2. I want to add these panels to range1. It is very difficult to find out which panels are not added yet in Range1. can you give a macro can that can findout which panel need to add range1 it will be very easy for me.

Actually I need to add which panels are missing in range1 from range2 as a sorting order. I mean serial wise on the same sheet or another sheet one.

John replied to Rolf Jaeger on 14-Nov-10 05:58 AM

I added my Excel file here and waiting for your favourable reply

Rolf Jaeger replied to John on 15-Nov-10 08:34 PM

Hi John:

Please give the code listed below a try. It will list all panels appearing in your range2, but missing in your range1 in column W of your worksheet.

Hope this helped,

Rolf

Const COLUMN_OF_MISSING_PANELS As String = "W"

Sub ListMissingPanels()

Columns(COLUMN_OF_MISSING_PANELS).Clear

Dim pnlRange1 As Range, pnlRange2 As Range

Set pnlRange2 = Range(Range("A1"), Range("P64"))

Set pnlRange1 = Range(Range("T1"), Range("T" & Rows.Count).End(xlUp))

Dim r As Range

Dim p As Range

Dim pnlFound As Range

Range(COLUMN_OF_MISSING_PANELS & 1).Value = "Panels missing in Range 1"

For Each p In pnlRange2.Columns

If (-1) ^ p.Column < 0 Then 'Only look in odd columns

Dim c As Range

For Each c In p.Cells.SpecialCells(xlCellTypeConstants)

If c.Row > 1 Then

On Error Resume Next

Set pnlFound = pnlRange1.Find(c.Value)

If pnlFound Is Nothing Then

If Not IsInrange(Columns(COLUMN_OF_MISSING_PANELS), c.Value) Then Range(COLUMN_OF_MISSING_PANELS & Rows.Count).End(xlUp).Offset(1).Value = c.Value

End If

Set pnlFound = Nothing

End If

Next c

End If

Next p

End Sub

Function IsInrange(r As Range, s As String)

Dim c As Range

For Each c In r.Cells.SpecialCells(xlCellTypeConstants)

If c.Value = s Then

IsInrange = True

Exit Function

End If

Next c

IsInrange = False

End Function

John replied to Rolf Jaeger on 20-Nov-10 03:07 AM

Thanks alot. Its working.

John replied to John on 20-Nov-10 07:13 AM

JOHNS FILE.zip If possible can you please update the macro suitable for this attached excel file.

Rolf Jaeger replied to John on 20-Nov-10 01:59 PM

Hi John:

sorry can't do, for a couple reasons:

your latest Excel workbook doesn't have much in common with the workbook you described in your original post (quite frankly I am not clear at all what you are trying to accomplish with the macro I originally proposed in this latest workbook)
I am trying to help people develop their own VBA coding capabilities. When I provide extensive code I am doing so in the hope that the people I am helping begin to pick up their end and try to modify my code to their specific requirements (at least within reason), and
your final request is bordering a request for contract work; if that's what you had in mind send me e-mail at office@soarentcomputing.com.

I trust you understand my position.

Best wishes,

Rolf

John replied to Rolf Jaeger on 21-Nov-10 07:47 AM

Sir

I need to check only the PANEL NO. not only look in odd columns. That correction only I just ask you. I don't have enough knowledge of macro or VBA. But I would like to learn.

Access over 40 UI widgets with everything from interactive menus to rich charts.