?
Solved

Need advice on how to merge two Excel spreadsheets

Posted on 2007-07-24
9
Medium Priority
?
1,206 Views
Last Modified: 2012-06-27
I have an Excel spreadsheet that has all the information I need for a project, other than one piece. That piece of information is on a separate spreadsheet. Column A on spreadsheet one has the same values as column A on the spreadsheet two, though not in the same order, and there are many rows on spreadsheet two that aren't required on spreadsheet one. Column A is the only one where the two spreadsheets contain common values.

I am not proficient with Excel and haven't been able to figure out how to merge the two. I need a way to have column A on both spreadsheets matched so the values from column F will appear on spreadsheet one, in the correct rows.

Apologies if this doesn't make sense, and please let me know if more information is needed.
0
Comment
Question by:JadeComputerGal
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 19556836
Its not clear what you are trying to do, is it possible to upload a copy of the workbook to Experts Exchange.
0
 
LVL 7

Expert Comment

by:Ripin
ID: 19556844
Use Pivot able/Report. Then you can merge same table both sheets as you like.
0
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 19556872
To upload the file:
Just login to: http://www.ee-stuff.com/

And follow the directions there.
You will need the URL of the question you are working on.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 38

Expert Comment

by:jeverist
ID: 19557694
Hi JadeComputerGal,

>  need a way to have column A on both spreadsheets matched so the values from column F will appear on spreadsheet one, in the correct rows

You can use either VLOOKUP() or a combination of INDEX/MATCH for this.  If your data in Sheet2 is in range A1:F1000, copy either of these formulas in row 2 of any empty column in Sheet1 and fill down for all rows to return the vaule of column F in Sheet2:

K2:

=VLOOKUP(Sheet1!A2,Sheet2!$A$2:$F$1000,6,FALSE)

- OR -

L2:

=INDEX(Sheet2!$A$2:$F$1000,MATCH(Sheet1!A2,Sheet2!$A:$A,0)-1,6)

You can change range 'Sheet2!$A$2:$F$1000' to match your data.

Jim
0
 

Author Comment

by:JadeComputerGal
ID: 19560951
Many thanks to those who responded, but I'm just lost even with the formulas that Jim posted. Even after modifying them, I can't get accurate results. I uploaded the workbook to https://filedb.experts-exchange.com/incoming/ee-stuff/4141-VM_only_accounts.zip if anyone is interested in taking a look.

I've moved some of the columns around since I posted the question, and I think I might be able to better explain now what I need because I think there's a better way to do it. To explain what I'm trying to do...Our company has thousands of mail files, some of which are used only for voice mail. Sheet2 is all the mail files, and Sheet1 is the voice mail only mail files. I was able to write views (in Notes) to get all the data on both sheets, but I couldn't get all the data out of a central source, so I had to do two separate sheets.

All the data I need, and far more, is on Sheet2, so basically what I need to do is remove from Sheet2 any row that doesn't have a corresponding entry on Sheet1, based on matching of the column labeled Mail File. That way, I'll have a sheet that has only the voice mail mailboxes with the size of each mailbox.

I hope this makes more sense, and I so much appreciate any help!

Jade


0
 
LVL 7

Expert Comment

by:Ripin
ID: 19563871
You can to use VBA. take a look at this http://www.mvps.org/dmcritchie/excel/delempty.htm
There are some usefull code samples to get you started.
0
 
LVL 38

Accepted Solution

by:
jeverist earned 2000 total points
ID: 19565070
Jade,

>  remove from Sheet2 any row that doesn't have a corresponding entry on Sheet1

We can do that using a form of the VLOOKUP() formula above and then removing all lines that are not found on Sheet1.  Here is the formula used in Column H on Sheet2 to check if the Mail File exists on Sheet1:

=VLOOKUP(C2,Sheet1!$D$2:$F$1000,1,FALSE)

Then, using an AutoFilter on Sheet2, we can select all of the rows with '#N/A' in column H and delete those rows to leave only the ones wth Mail Files on Sheet1.  Here's your file that shows how this is done:

https://filedb.experts-exchange.com/incoming/ee-stuff/4155-Q_22717176---VM-Only-Accounts.zip 

Jim
0
 
LVL 43

Expert Comment

by:Rob
ID: 19572507
Hi,

have a look, i downloaded and added the formula COUNTIF to sheet2

https://filedb.experts-exchange.com/incoming/ee-stuff/4164-VM-only-accounts.zip
0
 
LVL 43

Expert Comment

by:Rob
ID: 19572511
OK SORRY HADN'T REFRESHED THIS PAGE IN A WHILE :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question