Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match (merge) components from 2 spreadsheets
So I have this: Sheet1 1 a roy 2 b orange 3 c yellow 4 y green 5 z blue Sheet2 1 a january 2 b february 3 c march 4 f april 5 g may ...and I want to merge to create this: Sheet3 1 a roy january 2 b orange february 3 c yellow march 4 f (blank) april 5 g (blank) may 6 y green (blank) 7 z blue (blank) Is there any way to merge these two spreadsheets without going cross-eyed? -- mandg ------------------------------------------------------------------------ mandg's Profile: http://www.excelforum.com/member.php...o&userid=34986 View this thread: http://www.excelforum.com/showthread...hreadid=548763 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match (merge) components from 2 spreadsheets
I'd do something like this:
Create a new sheet (call it sheet3) Put something in A1 (just as a header) Copy the 2 lists into column A of this new sheet (one under the other) (Don't include the headers when you copy--just the raw data) Then select that range (A1:A###) data|filter|advanced filter Copy to another location List range: (should be entered (a1:A###) copy to: B1 Check Unique records only box Now you have a list of unique codes in column B. Delete column A (we're done with it). In B1, put: On Sheet1 In C1, put: On Sheet2 (just some kind of headers) In B2, put this formula: =if(iserror(vlookup(a2,sheet1!a:b,2,false)),"",vlo okup(a2,sheet1!a:b,2,false)) In C2, put this formula: =if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlo okup(a2,sheet2!a:b,2,false)) Select B2:C2 and drag down as column A extends. mandg wrote: So I have this: Sheet1 1 a roy 2 b orange 3 c yellow 4 y green 5 z blue Sheet2 1 a january 2 b february 3 c march 4 f april 5 g may ..and I want to merge to create this: Sheet3 1 a roy january 2 b orange february 3 c yellow march 4 f (blank) april 5 g (blank) may 6 y green (blank) 7 z blue (blank) Is there any way to merge these two spreadsheets without going cross-eyed? -- mandg ------------------------------------------------------------------------ mandg's Profile: http://www.excelforum.com/member.php...o&userid=34986 View this thread: http://www.excelforum.com/showthread...hreadid=548763 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge 2 spreadsheets info to 1 | Excel Worksheet Functions | |||
How can I match two spreadsheets by patients ID? | Excel Discussion (Misc queries) | |||
How do I merge 2 excel spreadsheets containing some common info? | Excel Worksheet Functions | |||
using vlookup - how do I match 2 spreadsheets w/o same exact numb. | Excel Worksheet Functions | |||
match two spreadsheets | Excel Worksheet Functions |