Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in two different workbooks
I have two workbooks. One named "last.xls" and the other name
"new.xls". Both have one sheet each with a SheetName of "Sheet1" Both sheets have the same number of columns (A thru K). Here is what need to do with the data: I want to compare "Column A" in both workbooks. If a value exists i "Column A of last.xls", but does not exist in "Column A of new.xls", want to take that entire row (the one that exists in last.xls, but no in new.xls) and copy it to a new workbook named tnnew.xls. I need t copy all of the rows in "last.xls" that do not exist in "new.xls" an copy them to the new workbook (tnnew.xls) I need the formating to remain the same in the new workbook as it is i last.xls. I would also like to copy the header row from "last.xls" t "tnnew.xls" (The header row is always row 1 Cells A thru K) I don't really know where to start with this. I've tried some VLOOKU formulas to no avail. I assume that a VBA module may be the best way t go. Any help is greatly appreciated. Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in two different workbooks
I haven't tested this code, but it should work:
Sub comparecolumns() LastOld = Workbooks("last.xls").Cells.SpecialCells(xlLastCel l).Row LastNew = Workbooks("new.xls").Cells.SpecialCells(xlLastCell ).Row LastRow = LastOld If LastNew LastOld Then LastRow = LastNew Workbooks("last.xls").Rows("1:1").Copy _ Destination:=Workbooks("tnnew.xls").Range("A1") For i = 2 To LastRow If Workbooks("new.xls").Cells(i, "A") = "" Then Workbooks("last.xls").Rows(i & ":" & i).Copy _ Destination:=Workbooks("tnnew.xls").Range("A" & i) Else: Workbooks("new.xls").Rows(i & ":" & i).Copy _ Destination:=Workbooks("tnnew.xls").Range("A" & i) End If Next i End Sub -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in two different workbooks
MSP77079,
I'm a novice, so I'm sure I did something wrong here when I tried you routine. I tried it a couple of ways. First I put your code in a new workboo (assuming that last.xls and new.xls would have to be open in Excel). Doing it this way, I get subscript out of range (when I debug, it is o the first line of code). Second time around, I put the code in a module in "last.xls". When run it then I get "Object doesn't support this property or method" (again when I run debug it is on the first line of code). Any ideas what I am doing wrong? Thanks agai -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in two different workbooks
Cells is a property of a worksheet, not a workbook. I think it should be
something like this. Change worksheet name as appropriate. Do the same in the 2nd line. LastOld = Workbooks("last.xls").Worksheets("Sheet1").Cells.S pecialCells(xlLastCell).Row On Thu, 9 Sep 2004 20:25:38 -0500, Keenman wrote: MSP77079, I'm a novice, so I'm sure I did something wrong here when I tried your routine. I tried it a couple of ways. First I put your code in a new workbook (assuming that last.xls and new.xls would have to be open in Excel). Doing it this way, I get subscript out of range (when I debug, it is on the first line of code). Second time around, I put the code in a module in "last.xls". When I run it then I get "Object doesn't support this property or method" (again when I run debug it is on the first line of code). Any ideas what I am doing wrong? Thanks again --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns in two different workbooks
Keenman,
Dose the "last.xls" has formulas? Do any of the data sets have duplicate entries within that data range? How big the data sets are? Open both "last.xls" and "new.xls". Put this code in "last.xls" general module, and save it and run the macro1 Cecil Sub Macro1() Dim LRow As Long Dim StRow As Long Dim i As Long Sheets("Sheet1").Select Sheets("Sheet1").Copy 'Change the path to suit ActiveWorkbook.SaveAs _ Filename:="C:\My Documents\ExcelFiles\tnnew.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False LRow = Range("A" & Rows.Count).End(xlUp).Row Range("L2").Formula = "=Row()" Range("M2").Formula = "=""lAst"" & L2" Range("L2:M2").AutoFill _ Destination:=Range("L2:M" & LRow), Type:=xlFillDefault StRow = LRow + 1 Range("A" & StRow).Select Windows("new.xls").Activate LRow = Range("A" & Rows.Count).End(xlUp).Row Range("A2:K" & LRow).Copy Windows("tnnew.xls").Activate ActiveCell.PasteSpecial LRow = Range("A" & Rows.Count).End(xlUp).Row Range("L" & StRow).Formula = "=Row()" Range("M" & StRow).Formula = "nEw" Range("L" & StRow & ":M" & StRow).AutoFill _ Destination:=Range("L" & StRow & ":M" & LRow), _ Type:=xlFillDefault With Range("L1:M" & LRow) ..Copy ..PasteSpecial xlPasteValues End With Range("A1:M" & LRow).Sort _ Key1:=Range("A2"), Order1:=xlAscending, _ Key2:=Range("M2"), Order2:=xlDescending, _ Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom For i = LRow To 2 Step -1 If Range("A" & i) = Range("A" & i - 1) Then Range("A" & i).EntireRow.Delete End If If Range("M" & i) = "nEw" Then Range("M" & i).EntireRow.Delete End If Next i LRow = Range("A" & Rows.Count).End(xlUp).Row Range("A1:M" & LRow).Sort _ Key1:=Range("L2"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom 'Range("L1:M1").EntireColumn.Delete 'Range("A1").Select End Sub "Keenman " wrote in message ... I have two workbooks. One named "last.xls" and the other named "new.xls". Both have one sheet each with a SheetName of "Sheet1" Both sheets have the same number of columns (A thru K). Here is what I need to do with the data: I want to compare "Column A" in both workbooks. If a value exists in "Column A of last.xls", but does not exist in "Column A of new.xls", I want to take that entire row (the one that exists in last.xls, but not in new.xls) and copy it to a new workbook named tnnew.xls. I need to copy all of the rows in "last.xls" that do not exist in "new.xls" and copy them to the new workbook (tnnew.xls) I need the formating to remain the same in the new workbook as it is in last.xls. I would also like to copy the header row from "last.xls" to "tnnew.xls" (The header row is always row 1 Cells A thru K) I don't really know where to start with this. I've tried some VLOOKUP formulas to no avail. I assume that a VBA module may be the best way to go. Any help is greatly appreciated. Thanks --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare workbooks | Excel Discussion (Misc queries) | |||
Compare columns from different workbooks | Excel Discussion (Misc queries) | |||
Compare workbooks | Excel Discussion (Misc queries) | |||
compare different workbooks | Excel Worksheet Functions | |||
how do I compare columns in two different workbooks? | Excel Discussion (Misc queries) |