topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 2:53 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Merge or combine worksheets from the same excel workbook  (Read 9159 times)

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Merge or combine worksheets from the same excel workbook
« on: February 08, 2020, 10:09 AM »
I have 13 sheets in an excel workbook. Format xls.
I want to join, merge or combine the 13 sheets in only one.
I have tried to select all but moving or copying give not the target.
How can I do that with a free utility ?
Best Regards
 :-* :P

a worksheet merger utility from the same file or excel workbook.

Note : I have tried two utilities from vba code in commercial pages, but don't work. So I would the correct code with no problems.
Note : I am trying to find here : http://www.rondebrui...ns/sectionaddins.htm , seems not commercial.
 :-*
Note : http://www.rondebrui.../addins/rdbmerge.htm
There are two versions depending of excel version .
 :P
 Note : I obtain automation error with the addon and occurr an exception.
then open excel 2010 again and nothing....

So I need a good addin for the occassion or use well the The Ron The Bruin addon

Note : To avoid confuse Ath or any other helper I am trying to determine the exact target to get :

Consolidate in Excel: Merge multiple sheets into one

Merge several excel files in one is done well with https://products.gro...pdocs.app/merger/xls

All my worksheets have the same configuration or formulas.

The target is :
Condensing Multiple Worksheets Into One (Microsoft Excel)

Note 08.02.2020.19.50 : trying this
https://excelribbon...._Worksheets_Into_One

Note : 08.02.2020.20.01 I tried. No good result. So probably the xls files components and threrefore the worksheets have not the same structure.

I must do manually with the old method copy/paste.
Best Regards
« Last Edit: February 08, 2020, 02:02 PM by Contro »

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,612
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #1 on: February 08, 2020, 11:30 AM »
1 google search gave me enough information to find this makeuseof page (select Not Now in the red page-blocker when using a decent browser with an ad-blocker), providing not 1, but 3 step-by-step procedures to accomplish what you ask. With the standard product, so no utilities needed, only a rather simple skill: google

As you forgot to mention what Excel version you are using, I assume you have a decently modern version that has a ribbon (Excel 2007 or newer).

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #2 on: February 08, 2020, 01:10 PM »
1 google search gave me enough information to find this makeuseof page (select Not Now in the red page-blocker when using a decent browser with an ad-blocker), providing not 1, but 3 step-by-step procedures to accomplish what you ask. With the standard product, so no utilities needed, only a rather simple skill: google

As you forgot to mention what Excel version you are using, I assume you have a decently modern version that has a ribbon (Excel 2007 or newer).

Oh. I was investigating and put the results above.
I will try your link.
Best regards Ath
The version is excel 2010

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #3 on: February 08, 2020, 01:32 PM »
1 google search gave me enough information to find this makeuseof page (select Not Now in the red page-blocker when using a decent browser with an ad-blocker), providing not 1, but 3 step-by-step procedures to accomplish what you ask. With the standard product, so no utilities needed, only a rather simple skill: google

As you forgot to mention what Excel version you are using, I assume you have a decently modern version that has a ribbon (Excel 2007 or newer).

The link don't do what I ask.
Use the copy/move option that is not enough to merge sheets in one sheet.

I use a online utility Free Online xls merger that works fine to merge xls files in one file converting every  workbook in a worksheet in my example (just because each workbook only have one worksheet). So I obtain a single xls with a 13 sheets.

The difficult point is get a new workbook with only one worksheet obtained from the previous 13 worksheets.
And in this point the native excel utility is not enough.

 :-*

Note : this is the merger online to group several xls files in one single xls with worksheets created from the original workbooks : https://products.gro...pdocs.app/merger/xls




Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #4 on: February 08, 2020, 02:11 PM »
What I simply need is an utility able to combine the worksheets from a single workbook into a new one by the the method of copy/paste.
Not considering structures or data, only copying from one worksheet to the combined one by order....

 :-* :P

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #5 on: February 08, 2020, 06:38 PM »
Perhaps it is an idea to export everything into CSV files, combine these text-based CSV files into one and then import the result back into Excel again?

Still, that requires the content of each sheet to be the same as the others.

Exporting everything to CSV should be easy enough with Excel itself. You could use join software for combining these CSV files. Once done with that, you can then use the CSV import functionality from Excel itself to get one worksheet with all the data in a new Excel file with an appropriate name.

Unless there is a need to keep formulas etc. exporting to the simplest data format, using one of many free join software programs to create one big file and then importing this file onto a new Excel file, seems the best way of tackling Contro's problem. To me, at least.

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #6 on: February 09, 2020, 03:22 AM »
if all the sheets are in the same format (all columns match and all formulas are the same) then you're only copying the data (the calculations can be done in the target sheet)

The simplest way to do this for a one time job is to copy + paste - it would have been done in less time than you've spent on this thread

if you need to do this often (ugh...), then its not as simple as just finding an addin.  Every situation is different and you have to make sure the data is clean before you even start (else you just end up with garbage)


Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,612
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #7 on: February 09, 2020, 03:25 AM »
@Contro:
Ok, I'll ask the 'big' question once, and please don't repeat the confusing flurry mesh of info you gave above: What goes into this black box, and what is the expected output? (IOW, what is it to be used for)

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #8 on: February 09, 2020, 11:32 AM »
Perhaps it is an idea to export everything into CSV files, combine these text-based CSV files into one and then import the result back into Excel again?

Still, that requires the content of each sheet to be the same as the others.

Exporting everything to CSV should be easy enough with Excel itself. You could use join software for combining these CSV files. Once done with that, you can then use the CSV import functionality from Excel itself to get one worksheet with all the data in a new Excel file with an appropriate name.

Unless there is a need to keep formulas etc. exporting to the simplest data format, using one of many free join software programs to create one big file and then importing this file onto a new Excel file, seems the best way of tackling Contro's problem. To me, at least.
Shades I was thinking in an ahk script.
I am doing manually.

If you export to csv you have a txt plain text.

I finally make work the rdbmerge utility to see that change everything.

It's clear that excel is a "big" program. But perhaps one that have traumatized me in the past like Autocad, microsoft word and some technical software. And of course windows xp pro traumatized me a lot.

I am applying manually from each worksheet the usual or standard copy/paste excel have with no problem.
So perhaps a little script with autohotkey may be useful for me.
I am thinking visit the code snack section.....

 :-* :P

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #9 on: February 09, 2020, 11:35 AM »
if all the sheets are in the same format (all columns match and all formulas are the same) then you're only copying the data (the calculations can be done in the target sheet)

The simplest way to do this for a one time job is to copy + paste - it would have been done in less time than you've spent on this thread

if you need to do this often (ugh...), then its not as simple as just finding an addin.  Every situation is different and you have to make sure the data is clean before you even start (else you just end up with garbage)


Target you have a good nick.
I have done manually, but if I don't put the post how we can enjoy talking about software ?

If I merely search in Father Google how can I share with human sensitive persons my inquietudes ?

 :Thmbsup: :-*

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #10 on: February 09, 2020, 11:37 AM »
@Contro:
Ok, I'll ask the 'big' question once, and please don't repeat the confusing flurry mesh of info you gave above: What goes into this black box, and what is the expected output? (IOW, what is it to be used for)
you are totally right Ath.
That's the point.
I have tried to explain to Shades. Perhaps you may accomplish there.
 :P

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,612
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #11 on: February 09, 2020, 12:05 PM »
@Contro:
Ok, I'll ask the 'big' question once, and please don't repeat the confusing flurry mesh of info you gave above: What goes into this black box, and what is the expected output? (IOW, what is it to be used for)
you are totally right Ath.
That's the point.
I have tried to explain to Shades. Perhaps you may accomplish there.
 :P
Well, all that you have done is what I explicitly asked not to do: You've added a mishmash of unrelated and useless words that say absolutely nothing about the issue you are trying to resolve. And on top of that you even used 3 posts to do that... >:(
To add insult to injury, you talk about using ahk to do something for you because Excel is 'too big' for you (to handle?).
What kind of help are you expecting?

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #12 on: February 09, 2020, 12:29 PM »
@Contro:
Ok, I'll ask the 'big' question once, and please don't repeat the confusing flurry mesh of info you gave above: What goes into this black box, and what is the expected output? (IOW, what is it to be used for)
you are totally right Ath.
That's the point.
I have tried to explain to Shades. Perhaps you may accomplish there.
 :P
Well, all that you have done is what I explicitly asked not to do: You've added a mishmash of unrelated and useless words that say absolutely nothing about the issue you are trying to resolve. And on top of that you even used 3 posts to do that... >:(
To add insult to injury, you talk about using ahk to do something for you because Excel is 'too big' for you (to handle?).
What kind of help are you expecting?
I think you are angry.
I expect simple help.
It's not my intention to make you angry.
It's not the first time, so I ask you why you like to post in my posts ?. I assume you have good reasons, but I don't understand why you want me to think in the way you do.

This is a help forum. Let someone help me please.
 :P :-*

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,612
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #13 on: February 09, 2020, 01:03 PM »
I'm trying to help solve issues/questions, because that is what I like to do, but with your questions/issues the hardest part is not finding the solution, but to get you to write down only the facts that are involved in the issue, and the exact result you want to achieve. Often you are adding a lot of words that have nothing to do with the issue, making it nearly impossible to help.



I'll retry to get the facts clear, please give consise answers to these questions:

  • What is the input? Currently I suspect there is an Excel file with several sheets
    • Do all sheets have the same columns/data?
    • Can you provide a sample file?
  • What is the output you need?
    • All sheets combined into a single sheet in a new file?
    • All sheets combined into a new sheet in the current file?
    • All sheets combined into the first sheet in the current file?
      • All sheets should remain in the file?
      • All sheets from sheet 2 should be deleted?

IainB

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 7,540
  • @Slartibartfarst
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #14 on: February 09, 2020, 04:33 PM »
Interesting...
10_512x512_A33C7E09.png

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #15 on: February 09, 2020, 07:13 PM »
FWIW the solution to your 'problem' is the program itself.  No value in using external apps like AHK, or probably even an addin, cos its something thats easily done in excel using VBA (ie a macro).

That said, unless you can give us a sample file (or at least some reasonable details) its probably going to be difficult for someone to put something together for you (and I don't currently have access to excel or I'd knock something up)

Of course any solutions offered now will be moot because you've already resolved the issue anyway

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Merge or combine worksheets from the same excel workbook
« Reply #16 on: February 11, 2020, 09:28 AM »
You can download an example file here :   https://www.donation...ex.php?topic=49517.0

 :-* :P