Comparing two excelfile worksheets [Closed]

Report
Posts
12
Registration date
Wednesday March 3, 2010
Status
Member
Last seen
March 22, 2010
-
 THIRU -
Hi,

One more scenario I have . If I have three columns in worksheet1 of file1(book1) and 1 column in worksheet2 of file2(book2), I should get only required columns in worksheet3 with column names to a new file(if possible) or with in any file.
for example

file1
worksheet1

x y z p and so on columns
abc 10 a s
pqr 20 b t
zyx 30 c r

file2
worksheet2

x
abc
xyz
zyx
rst
tyu

to a new file
output :

x z p
abc a s
zyx c r

Note : First line are column names

Requirement :

1. matched records in one work sheet
2. Unmatched records in another work sheet


Requesting to sort out the problem ASAP

Request : Only macros needed

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Assumption:

1. Headers are on Row 1
2. Data are on Sheet1 and Sheet2 in the original workbook
3. data is to be copied on sheet1 and sheet2 of the new book

Sub SeparateData()
Dim InputWB As Workbook
Dim InputSheet1 As Worksheet
Dim InputSheet2 As Worksheet

Dim OutputWB As Workbook
Dim OutputSheet1 As Worksheet
Dim OutputSheet2 As Worksheet

Dim TempSheet1 As Worksheet
Dim TempSheet2 As Worksheet

Dim iMaxCol As Integer
Dim lMaxRow As Long

Dim lTempMaxRow As Long

    Set InputWB = ThisWorkbook
    Set InputSheet1 = InputWB.Sheets("Sheet1")
    Set InputSheet2 = InputWB.Sheets("Sheet2")
    
    Set TempSheet1 = InputWB.Sheets.Add
    Set TempSheet2 = InputWB.Sheets.Add
    
    TempSheet1.Range("1:1") = InputSheet1.Range("1:1").Value
    TempSheet2.Range("1:1") = InputSheet1.Range("1:1").Value
    
    InputWB.Activate
    InputSheet1.Select
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    'For First workbook, sheet1
    ' find maximum used columns on row 1. This is based on assumption that all used columns have a header on row 1
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    
    'For First workbook, sheet1
    ' find maximum used rows in Column A. This is based on assumption that all maximum used rows is in column A
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Cells(1, iMaxCol + 1) = "Temp Col"
    With Range(Cells(2, iMaxCol + 1), Cells(lMaxRow, iMaxCol + 1))
        .FormulaR1C1 = "=IF(ISERROR(MATCH(RC1," & InputSheet2.Name & "!C1:C1,0)), 0, MATCH(RC1," & InputSheet2.Name & "!C1:C1,0))"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells.Select
    
    If ActiveSheet.AutoFilterMode = False Then
        Selection.AutoFilter
    End If
    
    Selection.AutoFilter Field:=iMaxCol, Criteria1:=">0", Operator:=xlAnd, Criteria2:="<>"
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If (lMaxRow > 1) Then
        lTempMaxRow = TempSheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1
        Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy
        TempSheet1.Range("A" & lTempMaxRow).PasteSpecial
    End If
    
    Selection.AutoFilter Field:=iMaxCol, Criteria1:="=0", Operator:=xlAnd, Criteria2:="<>"
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If (lMaxRow > 1) Then
        lTempMaxRow = TempSheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
        Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy
        TempSheet2.Range("A" & lTempMaxRow).PasteSpecial
    End If
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(1, iMaxCol), Cells(lMaxRow, iMaxCol)).Clear
    
    
    InputWB.Activate
    InputSheet2.Select
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    'For SECOND workbook, sheet2
    ' find maximum used columns on row 1. This is based on assumption that all used columns have a header on row 1
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    
    'For SECOND workbook, sheet1
    ' find maximum used rows in Column A. This is based on assumption that all maximum used rows is in column A
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Cells(1, iMaxCol + 1) = "Temp Col"
    With Range(Cells(2, iMaxCol + 1), Cells(lMaxRow, iMaxCol + 1))
        .FormulaR1C1 = "=IF(ISERROR(MATCH(RC1," & InputSheet1.Name & "!C1:C1,0)), 0, MATCH(RC1," & InputSheet1.Name & "!C1:C1,0))"
        .Copy
        .PasteSpecial xlPasteValues
    End With
        
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells.Select
    
    If ActiveSheet.AutoFilterMode = False Then
        Selection.AutoFilter
    End If
    
    Selection.AutoFilter Field:=iMaxCol, Criteria1:="=0", Operator:=xlAnd, Criteria2:="<>"
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If (lMaxRow > 1) Then
        lTempMaxRow = TempSheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
        Range(Cells(2, 1), Cells(lMaxRow, iMaxCol - 1)).Copy
        TempSheet2.Range("A" & lTempMaxRow).PasteSpecial
    End If
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(1, iMaxCol), Cells(lMaxRow, iMaxCol)).Clear
    
    
    TempSheet1.Select
    Columns(2).Delete
            
    TempSheet2.Select
    Columns(2).Delete
    
    Set OutputWB = Workbooks.Add
    Set InputSheet1 = OutputWB.Sheets("Sheet1")
    Set InputSheet2 = OutputWB.Sheets("Sheet2")
    
    InputWB.Activate
    TempSheet1.Select
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(lMaxRow, iMaxCol)).Copy
    
    OutputWB.Activate
    InputSheet1.Select
    ActiveSheet.Paste
    
    InputWB.Activate
    TempSheet2.Select
    lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(lMaxRow, iMaxCol)).Copy
    
    OutputWB.Activate
    InputSheet2.Select
    ActiveSheet.Paste
    
    TempSheet1.Delete
    TempSheet2.Delete
    
    Set TempSheet1 = Nothing
    Set TempSheet2 = Nothing
    
    Set InputSheet1 = Nothing
    Set InputSheet2 = Nothing
    Set InputWB = Nothing
    
    Set OutputSheet1 = Nothing
    Set OutputSheet2 = Nothing
    Set OutputWB = Nothing
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Could you provide a sample file with your expected result. I am getting confused over, how matching is going to occur. You mentioned that the book has three columns and yet data is in x, y and z column ? Could you add to some share site and with expected result. Instead of 4 workbooks, you can use 4 sheets to explain how the final product with look like
You can post it here https://authentification.site or some other shared site
Posts
12
Registration date
Wednesday March 3, 2010
Status
Member
Last seen
March 22, 2010
6
ile1
worksheet1

x y z p and so on columns (field names like salesid, salesmanname, country)
abc 10 a s
pqr 20 b t
zyx 30 c r

file2
worksheet2

x
abc
xyz
zyx
rst
tyu

to a new file worksheet1 should have matched data

output :

x z p
abc a s
zyx c r

to a new file worksheet2 should have unmatched data

output :

x y z p
pqr 20 b t
xyz
rst
tyu


Note : First line are column names


Requirement :

1. matched records in one work sheet
2. Unmatched records in another work sheet


Requesting to sort out the problem ASAP

Request : Only macros needed
THE SOULUTION IS SIMPLE. COPY ENTIRE COLUMN AS U REQUIRED FROM BOOK1 THEN PASE AS SPECIALLY IN BOOK 3.

NOTE: SPECIAL PASTE MEENS IS NOT EQUEL TO NORMAL PASTE. WHILE PASTING SELECT THE CELL / RIGHT CLICK / SELCET PASTE SPECIAL / CLICK VALUES OPTION / OK

BY.
THIRUMAL

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!