merging two files

[mcap]

Hi all:

I have a list of addresses, each with an ID number. I also have
another file that is a list of ID numbers that I have imported from
SPSS. The imported ID numbers contain most but not all of the ID
numbers from the address list.

What I would like to do is to match up the IDs from the imported
file to the address list so that for each case, there would be the
original ID and the imported ID. Where there wasn't an imported ID, it
should be blank in that column.

What I am trying to do is take the ID numbers of subjects who have
responded to a survey and mark them so that I can filter and delete the
addresses of those who did not respond.

Any ideas??????

Marc

 

[Dave Peterson]

I'd return the address into a different column (or columns).

If your master data is on Sheet1 and your SPSS data is on Sheet2, you could use
=vlookup() (with the key ID column in column A of each worksheet):

=vlookup(a2,sheet2!a:b,2,false)

or

=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

You could expand the range (to Sheet2!a:x) and bring back different columns (2,
3, 4,...) if the addresses are across columns.

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()))

 

[Gabor]

This one I made up for similar purposes many years ago, means you have
to really tweak it to your needs. Assumes you have the two lists in the
same workbook and there are no empty cells util the end of the list.
So wherever there is an >>>>UPDATE indicated you have to change the
code as per your workbook.

Sub Vergleich()

Dim ToList, FromList
Dim Friss, RefData
Dim RefTav
Dim StartCell

ToList = "St"
' Name of ToList <<<<<<<<UPDATE rqd. !!!
FromList = "List"
' Name of FromList <<<<<<<<UPDATE rqd.!!!
RefTav = 0
' Offset of the reference data element <<UPDATE !!!
StartCell = "A1"
' This is the starting cell on the ToList <<UPDATE !!!

Sheets(ToList).Select
Range(StartCell).Select
Sheets(FromList).Select
Range("A1").Select
' Starting Cell at FromList <<<<<<<<UPDATE !!!

For cik01 = 1 To 20000

ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, RefTav) = Empty Then GoTo 1000
'If ref. data is empty, quit
Friss = ActiveCell
RefData = ActiveCell.Offset(0, RefTav)
Sheets(ToList).Select
Range(StartCell).Select '<<<<<<<<<< UPDATE RQD.

For cik02 = 1 To 2000

ActiveCell.Offset(1, 0).Select

If ActiveCell.Offset(0, RefTav) = RefData Then
ActiveCell.Formula = Friss
ActiveCell.Offset(0, 12).FormulaR1C1 = "matched"
'<<<<UPDATE
Range(StartCell).Select
Sheets(FromList).Select
ActiveCell.Offset(0, 12).FormulaR1C1 = "matched"
'<<<<UPDATE
GoTo 100
ElseIf ActiveCell.Offset(0, RefTav) = Empty Then
Range(StartCell).Select
Sheets(FromList).Select
ActiveCell.Offset(0, 12).FormulaR1C1 = _
"not found"
'<<<<UPDATE
GoTo 100
Else
End If

Next cik02

100 Next cik01

1000 Beep

End Sub


Cheers, Gabor

 

[mcap]

Wow...thanks!!! Seems complicated as I am a total excel novice. SPSS
seems a lot easier for things this but I am working with mail merging
and other things that I prefer to use excel with. I will have a crack
at it. Thanks again!!!!!!!!

Marc