Close

Results 1 to 8 of 8

Thread: Excel help

  1. #1
    DF VIP Member Fear345's Avatar
    Join Date
    May 2002
    Location
    entebbe
    Posts
    3,153
    Thanks
    174
    Thanked:        415
    Karma Level
    524

    Help Excel help

    Hi Guy,

    Need some excel help, please. I have multiple excel files with all the same user information that has been sent out to colleagues that need comments inputted. Now these files have come back, what is the easiest ways to combine the files instead of copying and pasting each row?

    Cheers.

  2. #2
    DF Super Moderator
    evilsatan's Avatar
    Join Date
    Jul 2004
    Location
    Essex
    Posts
    20,079
    Thanks
    1,105
    Thanked:        3,241
    Karma Level
    1541

    Default Re: Excel help

    I have found this info for you from other places so may not work but worth a shot!

    Open all of the Excel workbooks in the same instance of Excel.
    You might have to click the internal "restore" button to see the individual workbooks.
    Select all sheets you want to move by Ctrl+Clicking on the worksheet tabs.
    Right click on one of the selected tabs, and choose Move or Copy...
    In the dialog that pops up, select the destination workbook (your "Master" workbook) and then choose where to insert them.
    The (move to end) option is likely what you want, but you can always reorder them later.
    Choose Create a copy if you do not want the sheets to be removed from the first workbook
    Click OK.
    The selected worksheets will be moved or copied from the original workbook into your "Master" workbook. Just close the source workbook and do it again with the next one, until you've collected all the worksheets you care about into one large workbook. Make sure to save!

    Once you have merged the workbooks then this seems to outline how to merge sheets:
    https://support.microsoft.com/en-us/...sheet-in-excel


  3. #3
    DF VIP Member Fear345's Avatar
    Join Date
    May 2002
    Location
    entebbe
    Posts
    3,153
    Thanks
    174
    Thanked:        415
    Karma Level
    524

    Default Re: Excel help

    Capture.PNG

    I get this error.

  4. #4
    DF Super Moderator
    evilsatan's Avatar
    Join Date
    Jul 2004
    Location
    Essex
    Posts
    20,079
    Thanks
    1,105
    Thanked:        3,241
    Karma Level
    1541


  5. #5
    DF VIP Member hoponbaby's Avatar
    Join Date
    Nov 2000
    Posts
    996
    Thanks
    155
    Thanked:        218
    Karma Level
    334

    Default Re: Excel help


  6. #6
    DF VIP Member DJ OD's Avatar
    Join Date
    Jul 2001
    Location
    On da decks.
    Posts
    10,114
    Thanks
    1,008
    Thanked:        2,254
    Karma Level
    1104

    Default Re: Excel help

    Just name all the files similar and create a new file in the same format or how u want it to look then just file link to the other files.

    if it's wording, concatonate I think Is the formula to 'add' them or maybe '&'.

    Also there's the GSETDI, method which I use a lot.


    DJ OD

  7. #7
    DF VIP Member Fear345's Avatar
    Join Date
    May 2002
    Location
    entebbe
    Posts
    3,153
    Thanks
    174
    Thanked:        415
    Karma Level
    524

    Default Re: Excel help

    Each one of these a try comes up with some kind of error, so I'm guessing the master file is a mess in one way or another.
    I didn't try DJ's because fuck knows what he meant

  8. #8
    DF VIP Member pattikins's Avatar
    Join Date
    Jul 2001
    Location
    manchester
    Posts
    795
    Thanks
    251
    Thanked:        81
    Karma Level
    343

    Default Re: Excel help

    I think DJ was on the right track. It needs the indirect function for it to work. For example:
    =INDEX([Dragonlance.xlsx]Dragonlance!C:C,MATCH(A1,[Dragonlance.xlsx]Dragonlance!A:A,0))
    The highlighted text is concatenated in cells S & T and is replaced with an Indirect function:
    =INDEX(INDIRECT(S1),MATCH(A1,INDIRECT(T1),0))
    S: ="["&R1&"]Dragonlance!C:C"
    T: ="["&R1&"]Dragonlance!A:A"
    R1=Dragonlance.xlsx

    Column R could in turn be concatenated =Q1&".xlsx"
    It's then a simple case of changing the filename- Dragonlance 1, Dragonlance 2...

    Altternatively, is it possible to convert the spreadsheets to csv files. It would then be a simple matter of running a batch file
    copy *.csv surveyx.csv

Similar Threads

  1. excel help
    By bbshark in forum PC Problems
    Replies: 17
    Last Post: 1st April 2016, 02:12 PM
  2. Excel Help
    By Soulassassin in forum PC Software
    Replies: 3
    Last Post: 22nd June 2015, 09:04 AM
  3. [HELP] Excel help please
    By Fear345 in forum PC Software
    Replies: 6
    Last Post: 28th April 2015, 11:48 AM
  4. help with Excel
    By consoles in forum The Dog and Duck
    Replies: 2
    Last Post: 16th June 2011, 09:19 PM
  5. Excel XP
    By bt2k1 in forum PC Problems
    Replies: 6
    Last Post: 4th August 2007, 10:26 AM

Social Networking 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
  •