Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two files
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two files
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)),"",vlo okup(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())) mcap wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two files
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 -- Gabor ------------------------------------------------------------------------ Gabor's Profile: http://www.excelforum.com/member.php...fo&userid=6179 View this thread: http://www.excelforum.com/showthread...hreadid=531253 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two files
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging multiple files | Excel Discussion (Misc queries) | |||
Merging Excel Files - Option "grayed out" | Excel Worksheet Functions | |||
Merging to identical Excel files | Excel Discussion (Misc queries) | |||
merging my excel files | Excel Discussion (Misc queries) | |||
Merging two different files | Excel Discussion (Misc queries) |