Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I match records from two Sheets?
Excel 2003
Sheet1 has one column with 904 records (alphanumeric) Sheet2 has one column with 1069 records (alphanumeric) Both fields have same format (17 characters) I need a code to read each record in sheet 1, then go to sheet 2 and read the records, if the record is found then copy the record from sheet2 and paste it in sheet1 next to the record. Do the same with all records in sheet2. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I match records from two Sheets?
Hi
On sheet1, enter the following formula in B1 and copy down =IF(COUNTIF(Sheet2!A:A,A1),A1,"") -- Regards Roger Govier sebastico wrote: Excel 2003 Sheet1 has one column with 904 records (alphanumeric) Sheet2 has one column with 1069 records (alphanumeric) Both fields have same format (17 characters) I need a code to read each record in sheet 1, then go to sheet 2 and read the records, if the record is found then copy the record from sheet2 and paste it in sheet1 next to the record. Do the same with all records in sheet2. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I match records from two Sheets?
This code should do it for you. You'll need to edit it to enter the correct
sheet names, column IDs that the records are in on each sheet, and indicate the row on each sheet where the records are. To put it into your workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert -- Module then copy the code and paste it into the code module and then edit as needed. Run it from Tools -- Macro -- Macros (pre-Excel 2003) or from the [Developer] tab (2007) Sub CopyMissingEntries() 'change these Const values 'as required 'name of sheet with short list Const sheet1Name = "Sheet1" 'column short list is in Const shortListCol = "A" 'first row with list data Const firstShortRow = 2 'name of sheet with long list Const sheet2Name = "Sheet2" 'column long list is in Const longListCol = "A" 'first row with list data Const firstLongRow = 2 Dim shortSheet As Worksheet Dim shortList As Range Dim anySLEntry As Range Dim longSheet As Worksheet Dim longList As Range Dim anyLLEntry As Range Dim foundFlag As Boolean Set shortSheet = Worksheets(sheet1Name) Set shortList = shortSheet.Range(shortListCol & _ firstShortRow & ":" & _ shortSheet.Range(shortListCol & Rows.Count). _ End(xlUp).Address) Set longSheet = Worksheets(sheet2Name) Set longList = longSheet.Range(longListCol & _ firstLongRow & ":" & _ longSheet.Range(longListCol & Rows.Count). _ End(xlUp).Address) 'to improve performance Application.ScreenUpdating = False 'do the real work For Each anyLLEntry In longList foundFlag = False For Each anySLEntry In shortList If anySLEntry = anyLLEntry Then foundFlag = True Exit For ' out of inner loop End If Next If Not foundFlag Then 'add to short list shortSheet.Range(shortListCol & _ Rows.Count).End(xlUp).Offset(1, 0) = anyLLEntry 'get new short list range Set shortList = shortSheet.Range(shortListCol & _ firstShortRow & ":" & _ shortSheet.Range(shortListCol & Rows.Count). _ End(xlUp).Address) End If Next 'housekeeping Set shortList = Nothing Set shortSheet = Nothing Set longList = Nothing Set longSheet = Nothing 'announce completion MsgBox "Task Completed" End Sub "sebastico" wrote: Excel 2003 Sheet1 has one column with 904 records (alphanumeric) Sheet2 has one column with 1069 records (alphanumeric) Both fields have same format (17 characters) I need a code to read each record in sheet 1, then go to sheet 2 and read the records, if the record is found then copy the record from sheet2 and paste it in sheet1 next to the record. Do the same with all records in sheet2. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I match records from two Sheets?
I may have misunderstood your request. The code above will find entries on
sheet 2 that are MISSING from sheet 1 and add them to the list on sheet 1. You seem to want something that matches the records and brings over data to sheet 1 that doesn't yet exist. You can do that with a VLOOKUP formula rather than with code. Assuming your records on both sheets go from column A over to column G and the codes are in column A on both sheets, then beginning at row 2 (column B) you could enter this formula: =VLOOKUP($A2,'Sheet2'!$A$2:$G$1070,2,FALSE) and that will bring up matching data from column 2 of Sheet2 to Sheet1, then in Sheet1, C2 enter this: =VLOOKUP($A2,'Sheet2'!$A$2:$G$1070,3,FALSE) as you can see, it's the same formula, just changing the ,2, to ,3, Repeat the copying of the formula across to column G (or where ever your records end) changing the ,2, (or ,3,) to the next increasing value to get different column information from Sheet2. Then copy the formulas down the sheet to the end of the data on Sheet1. "sebastico" wrote: Excel 2003 Sheet1 has one column with 904 records (alphanumeric) Sheet2 has one column with 1069 records (alphanumeric) Both fields have same format (17 characters) I need a code to read each record in sheet 1, then go to sheet 2 and read the records, if the record is found then copy the record from sheet2 and paste it in sheet1 next to the record. Do the same with all records in sheet2. |
#5
|
|||
|
|||
Quote:
=IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,0)),"",VLOOKUP (A1,Sheet2!A:A,1,0))
__________________
Thanks Bala |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I match records from two Sheets?
JLathan and Roger Finally I was able to use your code (both) and works well. Many thanks "Roger Govier" wrote: Hi On sheet1, enter the following formula in B1 and copy down =IF(COUNTIF(Sheet2!A:A,A1),A1,"") -- Regards Roger Govier sebastico wrote: Excel 2003 Sheet1 has one column with 904 records (alphanumeric) Sheet2 has one column with 1069 records (alphanumeric) Both fields have same format (17 characters) I need a code to read each record in sheet 1, then go to sheet 2 and read the records, if the record is found then copy the record from sheet2 and paste it in sheet1 next to the record. Do the same with all records in sheet2. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match 2 cells and count records | Excel Discussion (Misc queries) | |||
What function should I use to pull those records that match | Excel Worksheet Functions | |||
Searching and displaying records that match criteria. | Excel Discussion (Misc queries) | |||
remove records when fields match in 2 worksheets?? | Excel Worksheet Functions | |||
Can you match records from two different worksheets | Excel Worksheet Functions |