~ merge two spreadsheets with one sheet each into a new one based on matching ids. please advise ~

Topic

New Reply

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.

Reply | Quote

Replies

Could you attach an example file to walk you through the steps?

Steve

Reply | Quote

CBP,

You could copy all the records from say sheet#2 below the records on sheet#1.
Then google “Excel remove duplicates” w/o the quotes for several answers. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

*** THANKS SO MUCH!!

sorry i could i not explain this any better :/

i found what i needed — http://www.youtube.com/watch?v=NYX3x32CeSg

i needed to know what the word ‘consolidate’ in excel meant. this is by labels, so ‘top row’ and left column’

this is for making a map!

Reply | Quote

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?

Reply | Quote

Could you attach an example workbook, showing what you have and what you want?

Steve

Reply | Quote

first sheet is just data from A-C1 : A-C1000

the same as the sheet you want to merge with

only difference is A2:A1000 would have similar ids/labels so the id would need to match upon merge and not be duplicated (the consolidate video shows how the outcome would be)

the first post describes what the problem is though.

everything is already there in the first 2 post for an advice to the problem

Reply | Quote

i mean you’re just merging two sheets that have different label for the columns

the only column that has the same label is A1

and A2:A1000 is what should be matched and not duplicated

Reply | Quote

Did you follow the instructions that RetiredGeek posted at http://windowssecrets.com/forums/showthread//156462-merge-two-spreadsheets-with-one-sheet-each-into-a-new-one-based-on-matching-ids-please-advise?p=919609&viewfull=1#post919609

Steve

Reply | Quote

Did you follow the instructions that RetiredGeek posted at http://windowssecrets.com/forums/showthread//156462-merge-two-spreadsheets-with-one-sheet-each-into-a-new-one-based-on-matching-ids-please-advise?p=919609&viewfull=1#post919609

Steve

that is for a completely different problem though =)

it’s easier to solve this problem than
to figure out what in the world that error with the script is (which would end not working anyhow)

Reply | Quote

you’re just combing data based on the matching ids/label and duplicating them

Reply | Quote

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

Reply | Quote

I can not test in XL2013. You may need to discuss with the author of the software any issues you are having with it. The code is password protected so I will not comment or discuss any of the coding elements.

Steve

Reply | Quote

that is for a completely different problem though

I don’t see how it is different. RetiredGeek indicated how you can merge and remove duplicates, which seems to be your questions

to figure out what in the world that error with the script is

But this is NOT a question for this board, but the author of the code. The author of the code has put protection on it, so it is up to him/her to change it or offer the code to us to allow us to see and modify it.

Steve

Reply | Quote

Hi computerbaby

OK. I’ve checked out what the problem is.
You can use the merge file tool you mentioned in post#10.
The reason it doesn’t work in Excel2013 is because it creates a new workbook for the merged data, and it assumes (incorrectly) that all new workbooks have 3 sheets in them, named Sheet1, Sheet2, Sheet3.
The default setting in Excel 2013 is to create new workbooks with only one sheet in them.
So, to use that merge tool, you need to first adjust you Excel2013 so that it creates 3 sheets in a new file, rather than 1.
To do this:
1. Start Excel2013 with a blank workbook.
2. In the top-panel Ribbon, select File
3. In the left-hand displayed pane, select Options
4. Select General (left-hand pane)
5. On the right-hand pane there is a section When creating new workbooks
6. Change the dropdown value to 3 where is says Include this many sheets
7. In the bottom-right corner, click the [B]OK[/B] button

Now load the merge tool file [Khalids_Merge_Data.xls]
It should now work OK in Excel2013 (I tested it was OK with type B, and 2 files).

Please let me know if this now does what you want.

zeddy

Reply | Quote

you’re a genius!! that deserves 50 thanks!

http://www.asap-utilities.com/download-asap-utilities-free.php?file=1 doesn’t work for excel 2013 64 bit so im guessing this script didn’t work for the reason — it doesn’t work on 64 bit excel

i added 3 sheets total. i even changed the names to ‘Sheet1’ etc.

i also saved them as macro-enabled,
then i tried saving them all as 97-2003

but i keep getting

Run-time error ‘9’:
Subscript out of range

Reply | Quote

Hi computerbaby

Many thanks for your thanks!

So, you are running Excel2013 64-bit version?
Are you mad?!!! Do you work for NASA???
Are your Excel datafiles bigger than TWO THOUSAND MEGABYTES?????
Blimey! I don’t know many needing that capacity!
And anyway, Excel2013 64-bit is a bit unstable.

However, regarding your issue:
I don’t believe there should be any problem with using the merge tool in 64-bit Excel2013, provided you followed all 7 steps of my post#15. It is not sufficient to start with an empty, three sheet workbook. The merge tool creates a new workbook as part of its vba routine. It is this new workbook that the merge tool creates that must have three sheets.

Please let me know that you did steps 1 to 7.

Now, regarding ASAP Utilities. You are correct.
The current version of ASAP Utilities is a 32-bit add-in.
This will work OK on 64-bit Windows (e.g. Windows7, Windows8), BUT
64-bit edition of Excel can’t use 32-bit add-ins such as ASAP Utilities
So 64-bit-Excel-edition can’t run ASAP Utilities.

zeddy

Reply | Quote

Hi computerbaby

When you see
Run-time error ‘9’:
Subscript out of range
..I believe it is telling you that vba is trying to switch to a particular Sheet, but can’t find it.

When you start your Excel2013 64-bit, an click on New, and choose Blank workbook, how many sheets do you see in the new workbook?

zeddy

Reply | Quote