Excel help

Thread: Excel help

  1. Fear345's Avatar

    Fear345 said:

    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. evilsatan's Avatar

    evilsatan said:

    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. Fear345's Avatar

    Fear345 said:

    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. evilsatan's Avatar

    evilsatan said:

    Default Re: Excel help

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

     
  5. hoponbaby's Avatar

    hoponbaby said:

    Default Re: Excel help

    Can you consolidate them [Only registered and activated users can see links. ]
     
  6. DJ Overdose's Avatar

    DJ Overdose said:

    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. Fear345's Avatar

    Fear345 said:

    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. pattikins's Avatar

    pattikins said:

    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