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,148
    Thanks
    173
    Thanked:        412
    Karma Level
    520

    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.
    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  2. #2
    DF Super Moderator
    evilsatan's Avatar
    Join Date
    Jul 2004
    Location
    Essex
    Posts
    19,915
    Thanks
    1,084
    Thanked:        3,165
    Karma Level
    1533

    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:
    [Only registered and activated users can see links. ]


  3. #3
    DF VIP Member Fear345's Avatar
    Join Date
    May 2002
    Location
    entebbe
    Posts
    3,148
    Thanks
    173
    Thanked:        412
    Karma Level
    520

    Default Re: Excel help

    [Only registered and activated users can see links. ]

    I get this error.
    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  4. #4
    DF Super Moderator
    evilsatan's Avatar
    Join Date
    Jul 2004
    Location
    Essex
    Posts
    19,915
    Thanks
    1,084
    Thanked:        3,165
    Karma Level
    1533

    Default Re: Excel help

    How about this?
    [Only registered and activated users can see links. ]


  5. #5
    DF PlaYa hoponbaby's Avatar
    Join Date
    Nov 2000
    Posts
    981
    Thanks
    152
    Thanked:        209
    Karma Level
    268

    Default Re: Excel help

    Can you consolidate them [Only registered and activated users can see links. ]

  6. #6
    DF Super Moderator DJ Overdose's Avatar
    Join Date
    Jul 2001
    Location
    On da decks.
    Posts
    9,921
    Thanks
    944
    Thanked:        2,135
    Karma Level
    1127

    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
    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

  7. #7
    DF VIP Member Fear345's Avatar
    Join Date
    May 2002
    Location
    entebbe
    Posts
    3,148
    Thanks
    173
    Thanked:        412
    Karma Level
    520

    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
    [Only registered and activated users can see links. ]
    [Only registered and activated users can see links. ]

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

    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 BigBrand in forum PC Software
    Replies: 8
    Last Post: 28th April 2015, 11:03 AM
  2. Excel Help
    By Conkers in forum PC Software
    Replies: 2
    Last Post: 9th February 2010, 10:57 AM
  3. Excel XP
    By bt2k1 in forum PC Problems
    Replies: 6
    Last Post: 4th August 2007, 10:26 AM
  4. MS Excel Help
    By ss30 in forum PC Software
    Replies: 2
    Last Post: 2nd January 2007, 01:10 PM

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
  •