Excel VBA Consolidate Range

[kashif.special2005]

Hi,

I have data of previous month and current month, now I want to compare both months data with unique EmpID, and if the EmpID of current month's data is matched from Previous months EmpID then update the column with the current months data, and if the empID of current month's data is not exists in Previous months then simply add those details in previous months data, and also create two columns "EmpID" and "Columns Changed" in new sheets, with the changed columns detail.

Please find the data below of Previous month's, current month.

Previous Month Data


Sheet1

	A	B	C	D	E
EmpID	Project	Region	Without Demand	Efficiency
A	East	Yes
B	West		Yes
C	North	No
D	South		No
E	East		No
F	West	No
G	North		Yes
H	South	Yes
I	East		Yes
J	North	No

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:114px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Current Month Data


Sheet1

	I	J	K	L	M
EmpID	Project	Region	Without Demand	Efficiency
B	North	Yes
B	West		No
A	South	Yes
H	South	No
J	East	No
G	South	No
H	North	Yes
K	South	No
L	East	Yes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]FALSE[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

1st output will be like below image

Sheet1

	A	B	C	D	E
EmpID	Project	Region	Without Demand	Efficiency
B	North	Yes
B	West		No
C	North	No
D	South		No
E	East		No
F	West	No
A	South	Yes
H	South	No
I	East		Yes
J	East	No
G	South	No
H	North	Yes
K	South	No
L	East	Yes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:114px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]25[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]26[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]27[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]28[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]29[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]30[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]FALSE[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

2nd result will be like below.

Sheet1

	H	I
EmpID	Changed Column
Project,Region,Without Demand
Efficiency
Project,Region,Without Demand
Efficiency
Region

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:213px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]10[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Note:- The problem is that, the data is huge like 5000 rows and 65 columns, I showed above just random sample data.


I don't know how can I compare such a large rows and columns data row by row and update those columns only that's value have changed.


Please, please help me to achieve this task


Thanks
Kashif

 

[MickG]

So what you are now saying all 66 columns are required to be subject to the comparison check between sheet1 and sheet2, and not just the 5 in the original post, is that correct.????

If that is correct there is a possibility of up to 66 remarks in one cell in column "F", Do you agree ???
And as column "F" has the remarks I presume its not to be counted in the comparison check.

 

[kashif.special2005]

Hi MickG,

Yes, there will be same column between sheet1 and sheet2 and that is 66.

if the remarks column is exceded above character limit than we will write word "Too Many Columns Changed"

The main task is to compare the data between two sheets, than update data with the changes and consolidate.

I hope I describe the well.

Thanks
Kashif

 

[MickG]

As we are using column "F" sheet1 for the "Remarks", I assume that column "F" in sheet2 is not to be compared with column "F" sheet1, Is that correct???

 

[kashif.special2005]

Hi MickG,

Yes, basically compare all columns from sheet1 to sheet2, and in the sheet1 put remarks in the next available blank column.

Thanks
Kashif

 

[kashif.special2005]

Note:- There will be 66 columns in sheet1 and sheet1, I just randomally put 5 columns for example in the post.

Thanks
Kashif

 

[MickG]

Try this:-
NB:- With 66 columns , the remarks will be in column "BO".

[COLOR="Navy"]Sub[/COLOR] MG01Oct12
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng1
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    Lst = .Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]End[/COLOR] With
c = Rng1.Count + 1
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    .Columns(Lst + 1).ClearContents
    .Cells(1, Lst + 1) = "Remarks"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        .Cells(c, 1).Resize(, Lst).Value = Application.Transpose(Application.Transpose(Dn.Resize(, Lst).Value))
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 1 To Lst - 1
           [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, n).Value = Dic(Dn.Value).Offset(, n) [COLOR="Navy"]Then[/COLOR]
                nStr = nStr & IIf(nStr = "", .Cells(1, Dn.Offset(, n).Column), ", " & .Cells(1, Dn.Offset(, n).Column))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
            
            Dic(Dn.Value).Resize(, Lst).Value = Application.Transpose(Application.Transpose(Dn.Resize(, Lst).Value))
            Dic(Dn.Value).Offset(, Lst) = nStr
            nStr = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kashif.special2005]

Hi MickG,

Now it is working accordingly, thanks once again for your precious time and efforts, thanks once again, God may fulfill all your dreams you desire.

Thanks
Kashif

 

[MickG]

You're welcome