+ Reply to Thread
Results 1 to 23 of 23

Combining columns from multiple sheets

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Unhappy Combining columns from multiple sheets

    Hi all, I am total newbie to Excel VBA but do know programmig a bit, just not any .NET/VBA stuff. I wish you can help me with this problem I have. I tried to search from old topics but was not able to find anything to suite my purposes.

    We have tool(s) that generates reports as Excel sheets. So far we have combined the data manually, but now we want to automate the procedure a bit. Eventually we want to read data directly from the original Excel files into final-report table, but that can wait. Right now we just create manually new Excel file and copy/paste data there from multiple sources. This data is put so that one source = one sheet and before making report we need to combine it to the collection sheet.

    First problem is how to copy entire column starting from some specific cell untill end of the Excel table (sheet one), and paste it to another sheet (sheet three). We do not know beforehand how many rows we have, it changes, and there may be empty cells in the middle.

    Example: We have person names starting from column A10, where A10 is title and from A11 first name can be found. Name is like: Lastname Firstname, but like I said we do not know how many names we have totally and it can be also blank. We can assume that if name is left blank but we are not at the last row of the table there is more to come. We do not need to copy titles.


    After we have copied all names from sheet1 we need to do same for the names from another sheet and put them into end of the names we just copied. So if we just copied names and last is in the cell A67, next name from another sheet comes into cell A68. This should be simple but for some odd reason the format of the name is now different. Now it is like: Fistname Lastname, so we need to change it to Lastname Firstname before we again copy the whole column and paste it into collection sheet.

    Edit: after a bit of more head banging I realized that even if name is not mandatory we do have other fields that are:
    Please Login or Register  to view this content.
    We reduce one since last row is summary and we do not want to copy that one. Now I know first and last cell in column, since first is fixed. Next I need to figure out how to select this area and copy/paste it.
    Last edited by arlu1201; 05-02-2012 at 06:21 AM. Reason: Put code tags in future.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    You can use this - change it as appropriate. This code will copy the data from column A of sheet 1 to column A Sheet 2.
    Please Login or Register  to view this content.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Thanks,
    first modified version gives me runtime error 9 and then some nag about subscrption being out of range and then it started working? Need to study a bit more.

    I actually just got myself this far:

    Please Login or Register  to view this content.
    Forgot to mention but I am using Excel 2010.

    Now I need to figure out how swap those names in that another sheet before copying them. Quite progress, last Friday I did't even know how to start VBA Editor

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    Do not use selection as a variable. It is a vba keyword.

    Also, you can get rid of the select statements.

  5. #5
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    Do not use selection as a variable. It is a vba keyword.
    Thanks, changed.

    Quote Originally Posted by arlu1201 View Post
    Also, you can get rid of the select statements.
    Any links to examples?


    I have now been able to copy all data from sheet one to combination sheet. I also copied from sheet two another set of names. My solution is ugly ad-hoc, but it does what we need. Will tweak it later on. Too shy to show it right now.

    Next thing I need is some hints about how to swap name since they are in wrong order in sheet2. I found this example:

    Please Login or Register  to view this content.
    But I can't figure out how to bold it in my solution (inside some loop I suppose). I have begin and end cells stored inside variables, but could not figure this out right away.

    Edit: Hmmm, that example does not seem to work. Tried with new Excel file, typed my name into A1, given formula to A3 and it does not work: "The formula you typed contains an error..."

    I probably need to take a break now. Well, that was one way to spent a day (and most of the evening).

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    An example to eradicate the select statements is this
    Please Login or Register  to view this content.
    It can be simplified to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Thank you a lot! Looks so much nicer than my original solution. I was brave enough to modify your example a bit to another purpose:

    Please Login or Register  to view this content.
    since this is something we do not get from original data in this case. In another sheet we have also this info. We just know these are "open" cases and need to add that info into summary sheet.

    Now any ideas how to tackle next problem? I need to copy another set of names, from another sheet, after the previous one. The problem is that I need to swap name to right format, does not matter before or after the copy. If it is more easy to modify first original data and then copy it then I will choose that way.

    Out of ideas right now, need to go to bed ... finally. Thank you arlu1201 for your help so far.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    So what is the format currently - Last name, first name? Should it be changed to First Name, Last Name or just First Name Last Name?

  9. #9
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    So what is the format currently - Last name, first name? Should it be changed to First Name, Last Name or just First Name Last Name?
    Name is like this: John Doe all in same cell. No comma, just space.
    It should be changed to Doe John, again just space, all in one cell. No commas.

    It is stupid that the tool that gives us this raw data does't differentiate last name and first name but puts it into same cell. To make this even worse another report from same tool gives you names in different order. Before I can shovel this data into pivot, I need to make sure all data looks the same.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    As long as there are just 2 names for each person, you can use this formula

    =MID(A1,FIND(" ",A1,1)+1,100) & " " & LEFT(A1,FIND(" ",A1)-1)

    You can even do the above via code and then copy to the main sheet.

  11. #11
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    As long as there are just 2 names for each person, you can use this formula

    =MID(A1,FIND(" ",A1,1)+1,100) & " " & LEFT(A1,FIND(" ",A1)-1)

    You can even do the above via code and then copy to the main sheet.
    Yep, that is the one I have tried to use. With this result:
    error.png

    What I am doing wrong here?

    I also tried via VBA, like this:

    Please Login or Register  to view this content.
    But it says that it cant find well, eh ... Find (Sub or function not defined).
    Last edited by PeteP; 05-03-2012 at 03:40 AM.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    In which cell are you typing the formula? Is there a named range there?

    You cannot use it in VBA like that. Once it works in excel directly, record the same steps of writing the formula and use that code instead.

  13. #13
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    In which cell are you typing the formula? Is there a named range there?
    It does not show in the image, but formula is copy/pasted (also tried to type it) into C1.

    Quote Originally Posted by arlu1201 View Post
    You cannot use it in VBA like that.
    Yep, it sure does seems I can't.

    Quote Originally Posted by arlu1201 View Post
    Once it works in excel directly, record the same steps of writing the formula and use that code instead.
    I may need to ask more info once I get it working directly

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    Try the formula in another cell or in a blank file (for testing). It works fine at my end.

  15. #15
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    Try the formula in another cell or in a blank file (for testing). It works fine at my end.
    Allready tried. New blank file, same error, no matter into what cell I try. Tried to save my test sheet in .xslm and .xsl formats, but did not help.

    Okey dokey, so there is something wrong in my Excel settings, I have broken it myself during this excersice or something else. Need a moment to figure out search words. But first I will ask my collegue also to try this solution.

    Edit:

    Yesh \o/ !!!

    =MID(A1;FIND(" ";A1;1)+1;100) & " " & LEFT(A1;FIND(" ";A1)-1)

    Works! We changed all , --> ; and started working. Now what?

    Edit 2: So this was some localization issue? Well, next time I hopefully remember to check this also.
    Last edited by PeteP; 05-03-2012 at 05:49 AM.

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    Record the same activity and check the code that gets created. You can then use the code in your original macro.

    While recording, remember to put the formula in the exact cells that you require it to be in.

  17. #17
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Quote Originally Posted by arlu1201 View Post
    Record the same activity and check the code that gets created. You can then use the code in your original macro.

    Ok, I tried first with my test sheet just to see how this works. In A1 a have test string and B2 was highlighted and this is end result:

    Please Login or Register  to view this content.
    Looks jibberish to me.

    Quote Originally Posted by arlu1201 View Post
    While recording, remember to put the formula in the exact cells that you require it to be in.
    I need to think this (again) a little. I know where original names are. I know where I want to copy swapped version of them. But the amount of names is not same everytime. This month we may have 200 lines, next month 255. So how I can pick exact cells during recording?

    But thanks for your help and patience. I really appreciate this a lot.

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    While recording, the only thing you require to pay attention to is where you are placing the formula. The rest about variable number of rows can be handled through code. If you give me some more details, i can help you out.

  19. #19
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    OK,
    So I have allready copied data from the Sheet1 into sheet temp (thanks again for the help). No need to modify these since they are allready the way like them to be. The title is on A1, first actual name in A2 and last name is in the A column, but index changes mothly. We need to continue this list with data from another sheet, but can not copy directly because source name is in wrong order.

    The names that are in wrong order I will copy from Sheet2, and first data is at I25, last one is where-ever depending on a month.


    Edit: If it makes this any easier. I can change the order of operations so that we copy first the names that we need to modify?
    Last edited by PeteP; 05-03-2012 at 07:19 AM.

  20. #20
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    OK, i guess now its time for a sample sheet. We managed so far without one. Upload a file (containing dummy data) which is in the exact same format as your original file.

    We need to work next on interchanging the order of the names and then appending it to the temp sheet, right?

    To Attach a File:

    1. Click on Go Advanced.
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  21. #21
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    Here we go,
    dummy version. Containing only names we need to move and fiddle with.

    Names that we can use as such are at table "Opps" starting from CK37, these we copy into table "Temp" starting from A2.

    Next we need to copy from table "End" names found from G25 onwards. Also into table" "Temp colum A and in this case starting from A13 (but that we can not assume). We also need to change these names from Paul1 Shithereen1 into Shithereen1 Paul etc.

    Need to go now to fecth kids from daycare etc. but will continue later this evening.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    05-02-2012
    Location
    Kotka, Finland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Combining columns from multiple sheets

    OK,
    I think a need to try different approach. I will try to create simple(?) loop, that goes through values from Gx ... Gn.

    With InStr I can find space between names, with len I will get needed lenghts, and then by using Right & Reft I should be able to parse names apart and join them again in correct order. Then I will copy this new string into correct destination. Rude approach but should work.

    Now I need to study VBA loops a bit.

    Edit: something like this? Notice magic numbers

    Please Login or Register  to view this content.
    Edit: changed a bit, so that we do not change original data. Better leave it alone.

    Edit: Seems to be working. I was able to create Pivot table etc. Small adjustement still needed but major parts are there. I consider this case closed. Thank you arlu1201!
    Last edited by PeteP; 05-04-2012 at 01:57 PM.

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combining columns from multiple sheets

    Am glad its solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1