Need advice on how to merge two Excel spreadsheets

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.
JadeComputerGalAsked:
Who is Participating?
 
jeveristCommented:
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
 
Glenn_MooreCommented:
Its not clear what you are trying to do, is it possible to upload a copy of the workbook to Experts Exchange.
0
 
RipinCommented:
Use Pivot able/Report. Then you can merge same table both sheets as you like.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Glenn_MooreCommented:
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
 
jeveristCommented:
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
 
JadeComputerGalAuthor Commented:
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
 
RipinCommented:
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
 
RobOwner (Aidellio)Commented:
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
 
RobOwner (Aidellio)Commented:
OK SORRY HADN'T REFRESHED THIS PAGE IN A WHILE :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.