Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
I have a worksheet that needs to be updated using data from another worksheet. I need to write a macro that compares data from column A in the first worksheet and updates column A in the next worksheet.The layout of both sheets are very similar, column headings are the same. However, column A in both sheets contains unmatched data e.g. Worksheet1 A1 Roses A2 Daisies A3 Lillies A4 Daffoldils A5 Tulips Worksheet2 ( This will be updated and contain the macro) A1 Roses A2 Daisies A3 Asters A4 Daffoldils A5 Sunflowers Does anyone have any tips or code for this macro. Many thanks. -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
What do you want to do after you find a difference?
If you've only got one column in each sheet and you want a list that has exactly one entry (no matter which sheet they came from), you could just copy the cells to one column (include one header row). Then use data|Filter|advanced filter to extract the unique entries. Debra Dalgleish shares some instructions: http://www.contextures.com/xladvfilter01.html#FilterUR You could record a macro when you do it manually. =========== If you have multiple columns in each sheet, you could get this combined list of unique entries and then use a bunch of =vlookup()'s to retrieve various columns from each sheet. You may want to read Debra Dalgleish's notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) Carlie wrote: I have a worksheet that needs to be updated using data from another worksheet. I need to write a macro that compares data from column A in the first worksheet and updates column A in the next worksheet.The layout of both sheets are very similar, column headings are the same. However, column A in both sheets contains unmatched data e.g. Worksheet1 A1 Roses A2 Daisies A3 Lillies A4 Daffoldils A5 Tulips Worksheet2 ( This will be updated and contain the macro) A1 Roses A2 Daisies A3 Asters A4 Daffoldils A5 Sunflowers Does anyone have any tips or code for this macro. Many thanks. -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
I'm not sure if a vlookup would be suitable as the workbook that needs updating is a template with approx. 24 field headings and 60 string values in column A ( the data between b4:z63 has been cleared) In this template there are also several other worksheets e.g graphs/charts that will be populated once the template is running with the macro. In the other workbook the headings are exactly the same as the template however, column A is not an exact match with column A in the template. (Cells b10:z63 contains data to be updated in the template) After the main workbook searches for a match it should copy the entire row of data and paste it into the template. If a match is found with blank data it should also copy and paste. But if a match is not found it should skip that row and continue searching until another match is found. I am not a programmer but I have tried a bit of VBA below. please help as it does not run properly. Dim LMarketV As String Dim LColumn As Integer Dim LFound As Boolean 'Open P1 Country Workbook Workbooks.Open Filename:= _ "G:\Eworking\IC\TPS\Lehman POINT\Input templates\P1.ms.country.csv" 'Retrieve market value to search for LMarketV = Sheets("P1.ms.country").Range("B9").Value 'Start at column B LColumn = 2 LFound = False While LFound = False 'Encountered blank cell in row 2, resume search If Len(Cells(2, LColumn)) = 0 Then Resume Next Exit Sub 'Found match in row 2 ElseIf Cells(2, LColumn) = LMarketV Then 'Select values to copy from "P1.ms.country" worksheet Sheets("P1.ms,country").Select Range("B10:Z63").Select Selection.Copy 'Paste onto "Template V2" workbook Windows("Template v2.xls").Activate Cells(4, LColumn).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False LFound = True 'Continue searching Else LColumn = LColumn + 1 End If Wend End Sub -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
You have two workbooks that need to be compared.
WorkbookM (master) and workbookT (template) And you want each cell in A4:A63 in sheet1 of workbookM to look for a match in sheet1 of workbookT. If a match is found, what happens to each column of that matching row? If it's empty, is it replaced with values from workbookM? If it's filled in, is it replaced with values from workbookM? If there is no match, where would this row from workbookM be placed in WorkbookT? At the bottom A64? And when you say column A doesn't match exactly, does that mean that there is never an identical value in the column or that some of the values don't match? Carlie wrote: I'm not sure if a vlookup would be suitable as the workbook that needs updating is a template with approx. 24 field headings and 60 string values in column A ( the data between b4:z63 has been cleared) In this template there are also several other worksheets e.g graphs/charts that will be populated once the template is running with the macro. In the other workbook the headings are exactly the same as the template however, column A is not an exact match with column A in the template. (Cells b10:z63 contains data to be updated in the template) After the main workbook searches for a match it should copy the entire row of data and paste it into the template. If a match is found with blank data it should also copy and paste. But if a match is not found it should skip that row and continue searching until another match is found. I am not a programmer but I have tried a bit of VBA below. please help as it does not run properly. Dim LMarketV As String Dim LColumn As Integer Dim LFound As Boolean 'Open P1 Country Workbook Workbooks.Open Filename:= _ "G:\Eworking\IC\TPS\Lehman POINT\Input templates\P1.ms.country.csv" 'Retrieve market value to search for LMarketV = Sheets("P1.ms.country").Range("B9").Value 'Start at column B LColumn = 2 LFound = False While LFound = False 'Encountered blank cell in row 2, resume search If Len(Cells(2, LColumn)) = 0 Then Resume Next Exit Sub 'Found match in row 2 ElseIf Cells(2, LColumn) = LMarketV Then 'Select values to copy from "P1.ms.country" worksheet Sheets("P1.ms,country").Select Range("B10:Z63").Select Selection.Copy 'Paste onto "Template V2" workbook Windows("Template v2.xls").Activate Cells(4, LColumn).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False LFound = True 'Continue searching Else LColumn = LColumn + 1 End If Wend End Sub -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
If a match is found from column A in WorkbookM the entire row is copied and pasted into the matching row of WorkbookT. If there are empty cells (not in column A) between B4:Z63 in workbookM they are also copied and pasted into workbookT Additionally, if there isn't a match I would like to copy and paste at the bottom in workbookM A64. Finally, there are approx 10 values that do not match in column A. -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing columns
This copies the whole row (instead of B:Z). But it wouldn't be a problem to
change if it mattered: Option Explicit Sub testme() Dim WksM As Worksheet Dim WksT As Worksheet Dim RngM As Range Dim RngT As Range Dim DestCell As Range Dim res As Variant Dim myCell As Range 'change as required--and make sure the workbooks are open Set WksM = Workbooks("book1.xls").Worksheets("Sheet1") Set WksT = Workbooks("book2.xls").Worksheets("sheet1") Set RngM = WksM.Range("a4:a63") Set RngT = WksT.Range("a4:a63") Set DestCell = WksT.Range("a64") For Each myCell In RngM.Cells If myCell.Value = "" Then 'do nothing Else res = Application.Match(myCell.Value, RngT, 0) If IsNumeric(res) Then 'found a match myCell.EntireRow.Copy _ Destination:=RngT(res) Else 'no match myCell.EntireRow.Copy _ Destination:=DestCell 'get ready for next non-match Set DestCell = DestCell.Offset(1, 0) End If End If Next myCell End Sub Carlie wrote: If a match is found from column A in WorkbookM the entire row is copied and pasted into the matching row of WorkbookT. If there are empty cells (not in column A) between B4:Z63 in workbookM they are also copied and pasted into workbookT Additionally, if there isn't a match I would like to copy and paste at the bottom in workbookM A64. Finally, there are approx 10 values that do not match in column A. -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=532090 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
Comparing columns | Excel Discussion (Misc queries) | |||
Comparing Columns | Excel Worksheet Functions | |||
comparing two columns? | Excel Discussion (Misc queries) | |||
Comparing columns | Excel Programming |