Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Hello,
I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Sorry, ammended: I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 except column C which must remain the original value as in WS1 Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Hello,
I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 except column C which must remain the original value as in WS1 Thanks On Jan 29, 1:05 pm, "splat" wrote: Sorry, ammended: I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 except column C which must remain the original value as in WS1 Thanks- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Hi splat
Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
You need to change the following:
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) to ws2.Range("B" & lSrcRow).Copy _ ws1.Range("B" & lRow ) ws2.Range("D" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("D" & lRow & ":AC" & lRow) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Martin Fishlock" wrote: Hi splat Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Thanks for the reply Martin,
I get the following highlighted in yellow: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) BTW, if the data is in sheet 2, then it will also be in sheet 1. Thanks again. On Jan 29, 2:06 pm, Martin Fishlock wrote: Hi splat Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks- Hide quoted text -- Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)
should be lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row -- Regards, Tom Ogilvy "splat" wrote: Thanks for the reply Martin, I get the following highlighted in yellow: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) BTW, if the data is in sheet 2, then it will also be in sheet 1. Thanks again. On Jan 29, 2:06 pm, Martin Fishlock wrote: Hi splat Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks- Hide quoted text -- Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Thanks Tom
It was using silly test data that gave the same answer as the row number.... Splat: I also start on row 2 from the for statement and maybe you needto change it to row 1. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Tom Ogilvy" wrote: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) should be lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row -- Regards, Tom Ogilvy "splat" wrote: Thanks for the reply Martin, I get the following highlighted in yellow: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) BTW, if the data is in sheet 2, then it will also be in sheet 1. Thanks again. On Jan 29, 2:06 pm, Martin Fishlock wrote: Hi splat Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks- Hide quoted text -- Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data
Thanks guysfor the reply, it did something, but what it was, i'm not
sure. Both ws1 and 2 remain the same. It didn't bring the info from rows a - ac on ws2 over to ws1. On Jan 29, 3:37 pm, Martin Fishlock wrote: Thanks Tom It was using silly test data that gave the same answer as the row number.... Splat: I also start on row 2 from the for statement and maybe you needto change it to row 1. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Tom Ogilvy" wrote: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) should be lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row -- Regards, Tom Ogilvy "splat" wrote: Thanks for the reply Martin, I get the following highlighted in yellow: lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) BTW, if the data is in sheet 2, then it will also be in sheet 1. Thanks again. On Jan 29, 2:06 pm, Martin Fishlock wrote: Hi splat Try this. Note you do not mention about if data exists on sheet2 and not sheet1. : Option Explicit Sub mergeit() Dim ws1 As Worksheet, ws2 As Worksheet Dim lRowLast As Long, lRow As Long, lSrcRow As Long Set ws1 = Worksheets("WS1") Set ws2 = Worksheets("WS2") lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp) On Error Resume Next For lRow = 2 To lRowLast lSrcRow = 0 lSrcRow = Application.WorksheetFunction.Match( _ ws1.Cells(lRow, 1), ws2.Range("A:A"), 0) If lSrcRow 0 Then ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _ ws1.Range("A" & lRow & ":AC" & lRow) End If Next lRow End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "splat" wrote: Hello, I have info in columns A to AC in worksheet 1 and 2. I need to merge them as follows: If item number (Column A) exists in sheet 1 and 2, bring the full row across from WS2 and replace the entry in WS 1 Thanks- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consolidate data | Excel Worksheet Functions | |||
Data Consolidate | Excel Discussion (Misc queries) | |||
help to consolidate data | Excel Worksheet Functions | |||
Data / Consolidate | Excel Programming | |||
Consolidate Data | Excel Programming |