+ Reply to Thread
Results 1 to 35 of 35

Best way to copy data

  1. #1
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Best way to copy data

    Hi,

    (I have attached an example spreadsheet). I would like to know whether there is any easy way to copy the data from each countries months sheets data eg. 'paid' 'cancelled' 'total' onto the 'retention' sheet. So I would have 'Total' 'Paid' 'Cancelled' from each pivot on each sheets data all filled in on this retention sheet. Is there a formula or a way to copy without going through manually as this would take some time!

    Cheers
    Attached Files Attached Files
    Last edited by isolation123; 01-24-2015 at 03:10 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Best way to copy data

    For other forummembers see the related question of isolation123.

    http://www.excelforum.com/excel-char...ml#post3966771
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Quote Originally Posted by oeldere View Post
    For other forummembers see the related question of isolation123.

    http://www.excelforum.com/excel-char...ml#post3966771
    Hi, Yes it relates to this, however its a different question and I've set it up slightly differently to that one aswell and created a new sheet to report the totals on!
    Last edited by isolation123; 01-24-2015 at 04:03 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    I id a test for Switzerland (row 253)
    For the Total try this...
    =COUNTA(INDIRECT(LEFT($A253,5)&TEXT(N$1,"mmmyy")&"!I:I"))-1=COUNTIF(INDIRECT(LEFT($A253,5)&TEXT(N$1,"mmmyy")&"!I:I"),$B254)
    for the PAID, try this...
    =COUNTA(INDIRECT(LEFT($A253,5)&TEXT(N$1,"mmmyy")&"!I:I"))-1=COUNTIF(INDIRECT(LEFT($A253,5)&TEXT(N$1,"mmmyy")&"!I:I"),$B254)

    NOTE, looking at a few of your sheets (SwitzDec14 and SwitzNov14), your layouts are NOT the same. For this to work, all columns on all sheets, need to match/line up. On these 2 sheets, Dec14 Paid? is colimn I, but in Nov14 is it column K. Fix this 1st
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Best way to copy data

    I found that the easiest way to get data out of this strange "organization" of date on multiple worksheets was to create on each worksheet a Pivot Table out of all the data on the worksheet deleting blank columns without column titles that are not permissible. I also added column titles to columns that lacked titles.

    Then all the Pivot tables were copied to the Clipboard and then pasted on mass onto a new worksheet. Excess title rows were deleted and then a new Pivot Table created from this data.

    This didn't take long but I think that the data is rather too disorganized to easily automate the re-organization. I would be happy to be proven wrong.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Hi, thanks for your reply! What cell did you put the formula in? as its coming back as 'FALSE' (i have moved all columns so they match) Thanks

  7. #7
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Hi ' Newdoverman', thanks for your help! I really need the 'retention' sheet filled in with the headers & countries as they are already, so ideally i think i need some kind of count that adds up the totals from each existing pivot table.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    Quote Originally Posted by isolation123 View Post
    Hi, thanks for your reply! What cell did you put the formula in? as its coming back as 'FALSE' (i have moved all columns so they match) Thanks
    I used row 253 for that

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Best way to copy data

    This may give you some ideas. I manually created a compiled copy of all the pivot tables that I created on each worksheet. I then created a pivot table on the Retention worksheet from which values were extracted using SUMIFS.

    If you could standardize the data format on all the worksheets, it certainly would be easier to work with.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Hi 'Newdoverman' thanks thats great, however i do not need 'Renewal date' on the pivot, I would like the months on each sheet if possible, also someone will be going into each sheet and for example changing a unpaid to paid - so will this update?

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Best way to copy data

    Please Login or Register  to view this content.
    Try it (on a dummy of your sheet) and you will know the answer.

  12. #12
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Hi, Sorry I am beginner/intermediate at best with Excel!

    I have attached a new sheet - I have created a pivot for each sheet. I would like to be able to fill in the retention sheets numbers from each months sheets. All i need is 'Total' 'paid' and 'cancelled' for each countrys month, Surely there is a way to copy this over without going through each sheet?

    Thanks appreciate the help!
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data pls help!

    Please help with above post, really struggling with this! any help much appreciated thanks

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Best way to copy data

    You are running into why it is not a good idea to have data fragmented the way that you do. It becomes very very difficult to deal with. The Renewal date that I gave you in the example Pivot Table was to eliminate the need to address every worksheet for every country. The renewal dates are actually correspond with the worksheets that you have for each month. When the data was manually compiled (this is more or less the way your data should be in the first place) a Pivot table was created from it even though a Pivot table was not needed to extract the data that you want. It was only done to show you that it could be done.

  15. #15
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Hi, yes I I did not create the originals unfortunately, so is there no way to automatically fill in the retention sheet? Or do I have to do it manually?

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Best way to copy data

    Make sure the format of all sheets is the same.

    After that you can use the macro, which is offered you in the earlier threat.

    The link to the threat is (for the benefit of other members) in #2.

  17. #17
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Thanks oeldere! I will try it later. Does anyone else have any suggestions please

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    I dont know where my head was when I posted those formulas in post #4, my apologies

    Take a look at the attached, I have corrected my formulas and put them in for switzerland - also adjusted the columns for that country.
    Another point to note is that you need to try and keep your month name in the sheet name the same length (keep things standard). You heve Dec/Nov/Oct/Aug, but then you have July/June
    Attached Files Attached Files
    Last edited by FDibbins; 01-25-2015 at 01:37 PM.

  19. #19
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Best way to copy data

    To fill in the Retention worksheet automatically is next to impossible, as I see it. There are so many different numbers of columns. There are many worksheets that have columns that don't exist in the other worksheets and the order of the columns a is really random. After 4 hours of just trying to get a consistent column presentation on each worksheet, I just gave up.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    NDM, agreed, thats why I posted this in post #4...
    NOTE, looking at a few of your sheets (SwitzDec14 and SwitzNov14), your layouts are NOT the same. For this to work, all columns on all sheets, need to match/line up. On these 2 sheets, Dec14 Paid? is colimn I, but in Nov14 is it column K. Fix this 1st
    Also noted that there is a lack of uniformity in the naming of sheets

    If these are fixed, then my suggestions may help

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    NDM, agreed, thats why I posted this in post #4...
    NOTE, looking at a few of your sheets (SwitzDec14 and SwitzNov14), your layouts are NOT the same. For this to work, all columns on all sheets, need to match/line up. On these 2 sheets, Dec14 Paid? is colimn I, but in Nov14 is it column K. Fix this 1st
    Also noted that there is a lack of uniformity in the naming of sheets

    If these are fixed, then my suggestions may help

  22. #22
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    The switzerland formula you done is nearly what I'm looking for!, I have attached another test in this I have named each sheet with 7 digits so they all match length & I have moved the 'Country' and 'Paid?' columns to J & K, so is it now possible to do a count on the retention sheet like before but for these new sheets for country and paid as they are in the same columns in every sheet now?

    Or is there not some way of doing a vlookup/count to count country and paid in each months columns as Columns J & K have all the data I need so I can ignore the other columns as i can get country and paid status in those columns

    Thanks
    Attached Files Attached Files
    Last edited by isolation123; 01-25-2015 at 03:52 PM.

  23. #23
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Best way to copy data

    I #16 I posted

    Please Login or Register  to view this content.
    newdoverman also made that suggestion.

    FDibbins also made that suggestion.

    I wonder (and probaby YOU can explain to us), why you stick with your format.

    Since we already explained to you, it will make a solution very difficult.

    Please reply.

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    You have shortened teh country name to 2 characters, it was previously 5.
    This is fine (maybe 3 would have been better, although 5 was OK too, it was the month length that was the problem)

    What you now also need to do is adjust the LEFT() formula within my fomrulas. I had ...
    COUNTA(INDIRECT(LEFT($A233,5)
    you need to adjust it to...
    COUNTA(INDIRECT(LEFT($A233,2)

    ...or to however many letters you decide to use

  25. #25
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    HI, the format has to remain as i do not control this it gets sent to me this way unfortunately. however i really only need the data from columns J and K in each months sheet so this must be possible? thanks

  26. #26
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Quote Originally Posted by FDibbins View Post
    You have shortened teh country name to 2 characters, it was previously 5.
    This is fine (maybe 3 would have been better, although 5 was OK too, it was the month length that was the problem)

    What you now also need to do is adjust the LEFT() formula within my fomrulas. I had ...
    COUNTA(INDIRECT(LEFT($A233,5)
    you need to adjust it to...
    COUNTA(INDIRECT(LEFT($A233,2)

    ...or to however many letters you decide to use
    Hi thanks, so would the formula now be -
    =IFERROR(COUNTA(INDIRECT(LEFT($A253,2)&TEXT(C$1,"mmmmyy")&"!k:k"))-1,"") as this isnt working?

  27. #27
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    If you are applying that to the file you uploaded, dont forget that th1 1st month you have for switzerland is Jun...

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    253
    Switzerland Total
    0
    0
    0
    0
    0
    4
    6
    0
    3
    7
    8
    4
    254
    Paid
    4
    3
    3
    4
    3
    1
    255
    Cancelled
    256
    % paid
    #REF!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    100.0%
    50.0%
    #VALUE!
    100.0%
    57.1%
    37.5%
    25.0%

  28. #28
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    deleted duplicate post

  29. #29
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Quote Originally Posted by FDibbins View Post
    If you are applying that to the file you uploaded, dont forget that th1 1st month you have for switzerland is Jun...

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    253
    Switzerland Total
    0
    0
    0
    0
    0
    4
    6
    0
    3
    7
    8
    4
    254
    Paid
    4
    3
    3
    4
    3
    1
    255
    Cancelled
    256
    % paid
    #REF!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    100.0%
    50.0%
    #VALUE!
    100.0%
    57.1%
    37.5%
    25.0%
    AH Yes thats worked, however - '=IFERROR(COUNTA(INDIRECT(LEFT($A253,2)&TEXT(C$1,"mmmyy")&"!k:k"))-1,"")' on the other sheets with more than one country this will not, is there any way to match the country with the 'paid' 'total' figures?

  30. #30
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Just thinking - I could maybe do a sub total on each months sheet then go through manually and pull through each countries months paid total figures etc. anyone know an easier way would be v helpful. thanks

  31. #31
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    Quote Originally Posted by isolation123 View Post
    AH Yes thats worked, however - '=IFERROR(COUNTA(INDIRECT(LEFT($A253,2)&TEXT(C$1,"mmmyy")&"!k:k"))-1,"")' on the other sheets with more than one country this will not, is there any way to match the country with the 'paid' 'total' figures?
    Explain "more than one country" please? This formula is designed to find a sheet that matches the country and the date?

  32. #32
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    Quote Originally Posted by isolation123 View Post
    AH Yes thats worked, however - '=IFERROR(COUNTA(INDIRECT(LEFT($A253,2)&TEXT(C$1,"mmmyy")&"!k:k"))-1,"")' on the other sheets with more than one country this will not, is there any way to match the country with the 'paid' 'total' figures?
    Explain "more than one country" please? This formula is designed to find a sheet that matches the country and the date?

  33. #33
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    for example it didn't work for Sweden as it pulled through the total amount all countries that sheet eg nov14. If you could perhaps fill in one of the other countries totals on the test spreadsheet for me I can see if it works, thanks

  34. #34
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to copy data

    There is no country on that sheet name. Like I said, the formula was designed for sheet names that have country/date. If you want the sheets with only dates included, we will need to modify the formula

  35. #35
    Forum Contributor
    Join Date
    03-04-2008
    MS-Off Ver
    Office 2007/2010
    Posts
    127

    Re: Best way to copy data

    Quote Originally Posted by FDibbins View Post
    There is no country on that sheet name. Like I said, the formula was designed for sheet names that have country/date. If you want the sheets with only dates included, we will need to modify the formula
    Hi, is this now possible to modify then? thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Saving copy of data and then running macro on all active worksheets in saved copy?
    By Science87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-09-2014, 11:56 AM
  2. [SOLVED] Copy data with 4 cell interval and copy range of data like vlookup with structured output
    By Daydreams in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2013, 04:08 AM
  3. Enter data, copy formulas, copy values, paste data, delete data
    By alf40 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2013, 07:23 PM
  4. Copy/Paste range of data excluding certain rows and copy to another workbook
    By HoerbigAdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 02:51 PM
  5. Macro to copy Dyanmic lists then copy sheet and clear the original data
    By twiggywales in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 01:06 PM

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