Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

Combining worksheets

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
kardypaine - 06 Mar 2008 21:50 GMT
I want to take multiple Excel worksheets and combine the data in them,
keyed off of a particular column entry (an id #).  For instance, if I
have a worksheet with columns for i.d.# and name, and another
worksheet with columns for i.d.# and age, I would like to be able to
combine them so that the age column of data is added to the first
worksheet.  Obviously, if each worksheet contained the same list of
i.d.#'s, then I could just sort and cut and paste.  However, the
second sheet might contain extra i.d. numbers and/or not have i.d.
numbers that the first sheet has.

For instance, Worksheet 1:

1  Dave
2  Sally
3  Mark
4  Jack

Worksheet 2

1  35
2  24
4  29

I want to combine these and end up with

1  Dave  35
2  Sally  24
3  Mark
4  Jack  29

Does this make sense?   Is it possible to do this?  Thanks!
Pete_UK - 06 Mar 2008 23:34 GMT
Yes, you can do this, but you said that you might have IDs in Sheet2
which are not in Sheet1, so in that case it is probably better to
combine the data from the two existing sheets into a third sheet.

First of all, obtain a combined list of unique IDs. To do this, copy
the first list of IDs into column A of the new sheet. You also need to
have a header, so if you don't have one then insert a new row 1 and
put "ID#" in A1. Then copy the list of IDs from the second sheet
immediately below the list from Sheet1, so that they are all now in
column A. Then highlight all the IDs, including the header, and click
on Data | Filter | Advanced Filter, and in the panel that pops up you
should click on Unique Records Only, Copy to another location, and
enter C1 as the destination. Click OK, and you will have your unique
list in column C. You can delete columns A and B, and you might like
to sort the unique IDs (now in column A). Put "Name" in B1 and "Age"
in C1 as headers, and then these formulae in the row below:

B2:    =IF(ISNA(VLOOKUP(A2,Sheet1!A:B,2,0)),"",VLOOKUP(A2,Sheet1!A:B,
2,0))

C2:    =IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,
2,0))

Notice that the first formula is getting data from Sheet1 and the
second one from Sheet2. All you need to do now is copy the two
formulae down columns B and C of the new sheet for as many rows as are
necessary. If any of the data is missing for a particular ID, then you
will have a blank cell there.

Hope this helps.

Pete

> I want to take multiple Excel worksheets and combine the data in them,
> keyed off of a particular column entry (an id #).  For instance, if I
[quoted text clipped - 27 lines]
>
> Does this make sense?   Is it possible to do this?  Thanks!
kardypaine - 07 Mar 2008 15:30 GMT
Pete -

That is a great help.  Thanks!
Pete_UK - 07 Mar 2008 15:54 GMT
You're welcome - thanks for feeding back.

Pete

> Pete -
>
> That is a great help.  Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.