Previous Topic Next Topic

Comparing two excelfile worksheets

Closed
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 - Mar 8, 2010 at 09:59 PM
 THIRU - Sep 1, 2010 at 06:02 AM
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 responses

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 15, 2010 at 09:20 AM
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
Answer 2 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 12, 2010 at 08:52 AM
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
0
Answer 3 / 4
kish1975 Posts 12 Registration date Wednesday March 3, 2010 Status Member Last seen March 22, 2010 7
Mar 12, 2010 at 06:57 PM
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
0
Answer 4 / 4
THIRU
Sep 1, 2010 at 06:02 AM
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
0

Similar discussions

Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
VBA Compare 2 sheets and output difference to 3rd sheet
jmah -
TrowaD -

3 responses
Macro to compare 2 sheets and copy differences
ehusar -
AirMaple -

2 responses
macro to compare two excel sheets
annajins -
Aks -

6 responses
How to compare two Excel sheets with varying data
BWIRyan -
TrowaD -

15 responses