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.
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.
Its not clear what you are trying to do, is it possible to upload a copy of the workbook to Experts Exchange.
Use Pivot able/Report. Then you can merge same table both sheets as you like.
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.
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.
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!
- OR -
L2:
=INDEX(Sheet2!$A$2:$F$1000
You can change range 'Sheet2!$A$2:$F$1000' to match your data.
Jim
> 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!
- OR -
L2:
=INDEX(Sheet2!$A$2:$F$1000
You can change range 'Sheet2!$A$2:$F$1000' to match your data.
Jim
ASKER
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
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
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.
There are some usefull code samples to get you started.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
OK SORRY HADN'T REFRESHED THIS PAGE IN A WHILE :)