Merge Two Spreadsheets In To One Sheet Based On Matching Column

[exceltoohard]

hey im trying to do hobby project and i have this really big problem: im trying to merge two spreadsheets with one sheet each into a new one. they should be merged based on matching ids in column A1:A1000, so the outcome would not have duplicated ids.

im assuming this is a common need but i couldn't find anything for excel 2013

please advise. link to a good video guide would be helpful and good.

[alansidman]

Is this what you are looking to do?

Excel 2010 Tutorial Consolidating Data Microsoft Training Lesson 19.1 - YouTube (http://www.youtube.com/watch?v=NYX3x32CeSg)

[exceltoohard]

*** THANKS SO MUCH!!

that's EXACTLY what i needed

sorry i could not explain it any better

this is for making and map!

[exceltoohard]

sadly i need to request for aid again as i realised far too late that 'consolidate, from what i understand, only does numerical data...

advise please?

[exceltoohard]

it would be type B on Excel Data Matching Merger 2.1 (http://welfarestate.com/Excel-Data-Matching-Merger/) but that script doesn't seem to work on excel 2013

[alansidman]

Looking at Example B of your link, I would accomplish this either using MS Access and joining the two tables on the common ID. If you don't have Access, then you can do the same with MS Query, which is included as part of your Excel package. You will need to use SQL to make it happen. Look at this link.

Use MS Query to Treat Excel as a Relational Data Source (http://exceluser.com/formulas/msquery1_1.htm)

[exceltoohard]

so which would be easier the way for many of us that knows nothing? access came with office 2013 though i have no idea what it does or how to use it

[exceltoohard]

ok ms query from the 'data' tab seemed easy enough but when i tried to open the excel file, it gave back "no visible table" -- i'm not sure if i did something wrong or what it considers a table but the data is from A-E1 : A-E1000 so it surely is a "table"

[alansidman]

If you are interested in getting started with Access, then here is a link to a basic tutorial by one of the MS MVPs, Crystal Long.

Access Basics (http://www.accessmvp.com/strive4peace/)

With the issue you have, this may be the right time to start learning Access as it can be a great help with Excel analystics.

Alan

BTW: MSQuery wants to see tables laid out in a table format. See if change your data to an Excel "table" using the Excel wizard helps. Without seeing your data, it will be difficult to diagnose this further.

[exceltoohard]

i found out that the problem could be easily solved with vlookup, and was wondering if you were aware of that or use it much

even though access likely has so many more flexible uses, i don't think i'll need or use it ever (i'm just merging and doing one simple % calculation, nothing special)

Answer to I have a lot of data to analyze. What's the advantage of using MS Access to an SQL database and doing manual queries? - Quora (http://www.quora.com/I-have-a-lot-of-data-to-analyze-Whats-the-advantage-of-using-MS-Access-to-an-SQL-database-and-doing-manual-queries/answers/1036675) clarifies to me to that i overall don't need access

there's also ASAP Utilities for Excel - ASAP Utilities 5.0 Download this free Excel add-in - Recommended by several magazines (http://www.asap-utilities.com/download-asap-utilities-free.php?file=1) but doesn't work for excel 2013 64 bit though :/

[alansidman]

I use vlookup all the time, but from your description of the situation, it did not appear to be a solution. Remember, you did not supply a sample document and outlined your issue in writing.

Glad that you have solved your issue. Suggest you mark this as solved.