Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mandg
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
merge 2 spreadsheets info to 1 JulesVern Excel Worksheet Functions 1 April 23rd 06 06:36 AM
How can I match two spreadsheets by patients ID? usmleboy Excel Discussion (Misc queries) 3 January 6th 06 11:09 PM
How do I merge 2 excel spreadsheets containing some common info? SOS Excel Worksheet Functions 1 November 7th 05 06:02 AM
using vlookup - how do I match 2 spreadsheets w/o same exact numb. klondike47 Excel Worksheet Functions 1 February 6th 05 12:01 AM
match two spreadsheets Socialism Excel Worksheet Functions 1 December 10th 04 06:59 AM


All times are GMT +1. The time now is 12:56 AM.

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

About Us

"It's about Microsoft Excel"