A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I match records from two Sheets?



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 11:49 AM posted to microsoft.public.excel.newusers
sebastico
external usenet poster
 
Posts: 74
Default 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  
Old May 14th, 2010, 12:28 PM posted to microsoft.public.excel.newusers
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old May 14th, 2010, 12:47 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old May 14th, 2010, 12:56 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old May 18th, 2010, 07:39 PM posted to microsoft.public.excel.newusers
sebastico
external usenet poster
 
Posts: 74
Default 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.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:45 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.