Comparing data between two spreadsheets

[chadski778]

Hi,

I would like to compare data between worksheets of two different workbooks - workbook 1 is named 'SP Form' and the worksheet is 'Page1' and workbook 2 is named 'MASTER_New GTL' and the worksheet is 'Master Document'). 'SP Form' should contain more up to date data than 'MASTER_New GTL' . I would like a macro that identifies which rows are 'new' in 'SP Form' 'Page1' compared to 'MASTER_New GTL' (worksheet 'Master Document'). To do this it would have to look in columns A (SP CODE) and G (OP_MATERIAL_CODE) in 'SP Form' (worksheet 'Page1') and compare with the data in columns C and H in 'MASTER_New GTL' (worksheet 'Master Document'. Rows that are in 'SP Form' that are not present in 'MASTER_New GTL' need to be highlighted. See example below where Row number 4 is in 'SP Form' but not in 'MASTER_New GTL' so is highlighted. Ofcourse that is a small subset of data, whereas the actual spreadsheets contain hundreds of rows.

Excel Workbook
	A	B	C	D	E	F
1		'SP Form' (worksheet 'Page1')			MASTER_New GTL' (worksheet 'Master Document')
2		Column A	Column G		Column C	Column H
3	Row number	SP CODE	OP_MATERIAL_CODE	Row number	SP Code	FORM CODE
4	1	001A0001	140001974427	1	001A0769	140001012711
5	2	180C0001	140001791988	2	001A0001	140001974427
6	3	001A9915	140002352957	3	001B1681	140002024410
7	4	901L1473	140000003464	4	180C0001	140001791988
8	5	001B1681	140001791979	5	001A9915	140002352957
9	6	001B1681	140002024410	6	001B1681	140001791979
10	7	001A0769	140001012711	7	001A0769	140001013000
11	8	001A0769	140001013000	8	901L1473	140000003400
12	9	901L1473	140000003400
Master Document

Thanks

Chad

 

[Peter_SSs]

Try this with copies of your workbooks.

Check the workbook and worksheet names in the code carefully and correct to match yours where necessary. The code assumes both workbooks are open when it is run.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckData()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsSP <SPAN style="color:#00007F">As</SPAN> Worksheet, wsMN <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> aSP, aMN, aSPtemp, aMNcomb<br>    <SPAN style="color:#00007F">Dim</SPAN> lrSP <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lrMN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> aArea <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsSP = Workbooks("SP Form.xlsm").Sheets("Page1")<br>    <SPAN style="color:#00007F">Set</SPAN> wsMN = Workbooks("Master_New GTL.xlsm").Sheets("Master Document")<br>    <SPAN style="color:#00007F">With</SPAN> wsMN<br>        lrMN = .Range("C" & .Rows.Count).End(xlUp).Row<br>        aMN = .Range("C1:H" & lrMN).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> aMNcomb(1 <SPAN style="color:#00007F">To</SPAN> lrMN)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lrMN<br>        aMNcomb(i) = aMN(i, 1) & "|" & aMN(i, 6)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> wsSP<br>        lrSP = .Range("A" & .Rows.Count).End(xlUp).Row<br>        aSP = .Range("A1:G" & lrSP).Value<br>        <SPAN style="color:#00007F">ReDim</SPAN> aSPtemp(1 <SPAN style="color:#00007F">To</SPAN> lrSP, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lrSP<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Filter(aMNcomb, aSP(i, 1) & "|" & aSP(i, 7), _<br>                    <SPAN style="color:#00007F">True</SPAN>, vbTextCompare)) = -1 <SPAN style="color:#00007F">Then</SPAN><br>                aSPtemp(i, 1) = 1<br>                bFound = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">If</SPAN> bFound <SPAN style="color:#00007F">Then</SPAN><br>            Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>            .Columns("A").Insert<br>            <SPAN style="color:#00007F">With</SPAN> .Range("A1").Resize(lrSP)<br>                .Value = aSPtemp<br>                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> aArea <SPAN style="color:#00007F">In</SPAN> .SpecialCells(2).Areas<br>                    aArea.Resize(, 8).Interior.ColorIndex = 35<br>                <SPAN style="color:#00007F">Next</SPAN> aArea<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Columns("A").Delete<br>            Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[chadski778]

Thanks that looks good. I want to filter the data a little bit by removing lines that do NOT contain the text 'Ras Laffan' in cells in column I apart from row 1 which is the column heading. The 'Ras Laffan' text is usually part of a sentence.

 

[chadski778]

Thanks for your help. It's working great!

 

[chadski778]

Would it be possible to tweak the code so that the cells highlighted starts on row 3 (top 2 rows are headings), and to extend it to column N (end of data block). I tried it myself but its not working.

Thanks

 

[Peter_SSs]

Would it be possible to tweak the code so that the cells highlighted starts on row 3 (top 2 rows are headings), and to extend it to column N (end of data block). I tried it myself but its not working.

Thanks

Make the red changes and try that.

ReDim aSPtemp(1 To lrSP, 1 To 1)
For i = 3 To lrSP

For Each aArea In .SpecialCells(2).Areas
    aArea.Resize(, 15).Interior.ColorIndex = 35
Next aArea

 

[chadski778]

Thanks I finally figured that with a bit of tinkering

Thank you very much. My VBA understanding is very limited but it's getting better the more I use this forum