0
I need to merge two data sheets in excel. Each worksheet has a common column, the number, but some numbers are on one page but not the other and some are on both pages. Additionally, all of the headers on worksheet one are different than worksheet two. I need to merge the two sheets together such that worksheet two's columns are at the end of worksheet one and so that each number in column a is not repeated in any row.
linds
Posted 2014-10-14T18:36:23.947
Reputation: 11
1
I've done a good search of the rules, and haven't found any rules to stop me linking to an excel file i've uploaded with a mock up of the answer.
http://speedy.sh/PKeqD/excel-vlookups.xlsx
I have 1-->8 on Sheet1; and 1,2, and 5-->10 on Sheet2.
Here I can easily just put 1-->10 on sheet3 (though actually did copy and paste all the numbers from Sheet1 & Sheet2, into Sheet3, then did a Remove duplicates as i suggested in my comment).
Then with alot of vlookup() formulas, I merged all the cells
=IF(ISNA(VLOOKUP($A2,Sheet1!$A$2:$D$9,2,FALSE)),"",VLOOKUP($A2,Sheet1!$A$2:$D$9,2,FALSE))
Brian Folan
Posted 2014-10-14T18:36:23.947
Reputation: 416
Sorting and then careful copying and pasting is your friend – Rhys Gibson – 2014-10-14T18:52:27.660
Sometimes it takes more work to explain it, than actually to do it :)... I would create a 3rd sheet, and copy all the numbers from each of the first 2 sheets to that one. Then do a Remove Duplicates on all of them (any probably a sort to make it look nicer) Then I would to a vlookup() on that number and lookup Sheet1 and grab each of the values to Columns A-->M (or what ever), then a vlookup() on Sheet2 to Columns N-->Z (or what ever). to make it look nicer use =if(isna(vlookup()),"",vlookup())
Does this make sense to you? (else I can try to explain more) – Brian Folan – 2014-10-14T19:23:43.680
1@BrianFolan Why not post that as an answer? – CharlieRB – 2014-10-14T19:40:45.340
Didn't feel like I did enough work to put as an answer :)... posted now. – Brian Folan – 2014-10-14T19:41:46.623