Online Now 2875

MSU Red Cedar Message Board

The largest and most active MSU Spartans board on the web

On this Board 2099
Record: 12118 (3/18/2012)

Online now 2817
Record: 10351 (3/11/2012)

Boards ▾

MSU Red Cedar Message Board

The largest and most active MSU Spartans board on the web

The Press Box

The place to ask questions to SpartanTailgate's recruiting experts

Duffy Daugherty Forum

"The Duff" is dedicated to Michigan State football recruiting discussion

Jack Breslin Forum

"The Bres" is dedicated to Michigan State basketball recruiting discussion

Wells Hall Off Topic Board

This is your pulpit to preach to the masses about everything from politics to religion

Marketplace & Ticket Exchange

The place to buy, trade or sell Michigan State tickets

Fantasy Sports Forum

For fantasy football and other fantasy sports discussion

Test/Feedback Forum

Reply

Advanced Excel Question

  • So I have a work assignment to gather weekly data from tech leads. We have developed an Excel format that is easy to use and then after I compile the different Excel sheets in a central location we run a MACROS from another sheet that data mines all of the reports I have compliled. I then send the resultant data to the analyst and my job is done.

    Here's my problem, I'm not going to get all of the leads to use the correct format, they are just too busy and management isn't behind me enough to force them. So they are going to be sending me sheets that aren't in the correct format for our MACROS to datamine, some of them will be, some of them wont be, but they'll all be fairly similar and based off from the correct template.

    Question:
    Short of copying and pasting all the data from one sheet to the correct format, which wouldn't be practical since I'd be copying and pasting formulas over different formulas, what's the best and easiest way to reformat the reports I recieve? The sheets are fairly straight forward, but each has several tabs.

    Since I'll be recieving several different formats, writing a MACROS for each of them doesn't seem practical either, what I was hoping to be able to do would be to apply the formatting from the desired template to the other sheets, is there a way to do this?

    This post has been edited 4 times, most recently by Johnny2x2x on 7/30/2012 at 7:43 AM

    Johnny2x2x ●

  • Put "=48÷2(9+3)" in cell A1 and you should be gold.

    Osmo ●

  • Johnny2x2x said...

    So I have a work assignment to gather weekly data from tech leads. We have developed an Excel format that is easy to use and then after I compile the different Excel sheets in a central location we run a MACROS from another sheet that data mines all of the reports I have compliled. I then send the resultant data to the analyst and my job is done.

    Here's my problem, I'm not going to get all of the leads to use the correct format, they are just too busy and management isn't behind me enough to force them. So they are going to be sending me sheets that aren't in the correct format for our MACROS to datamine, some of them will be, some of them wont be, but they'll all be fairly similar and based off from the correct template.

    Question: Short of copying and pasting all the data from one sheet to the correct format, which wouldn't be practical since I'd be copying and pasting formulas over different formulas, what's the best and easiest way to reformat the reports I recieve? The sheets are fairly straight forward, but each has several tabs.

    Since I'll be recieving several different formats, writing a MACROS for each of them doesn't seem practical either, what I was hoping to be able to do would be to apply the formatting from the desired template to the other sheets, is there a way to do this?

    Copy the correct format into the sheets you will be receiving., you should be able to use the 1st row and drag it down on the new sheet. Repeat for each tab.

    signature image signature image

    DMBSparty

  • It sounds like your job is horrible. Get a new job? Problem solved.

    BH Spartan

  • Try Paste Special > Formats.

    I am not absolutely sure it will work but it can't hurt to try that function.

    Spartanram ●

  • Johnny2x2x said...

    So I have a work assignment to gather weekly data from tech leads. We have developed an Excel format that is easy to use and then after I compile the different Excel sheets in a central location we run a MACROS from another sheet that data mines all of the reports I have compliled. I then send the resultant data to the analyst and my job is done.

    Here's my problem, I'm not going to get all of the leads to use the correct format, they are just too busy and management isn't behind me enough to force them. So they are going to be sending me sheets that aren't in the correct format for our MACROS to datamine, some of them will be, some of them wont be, but they'll all be fairly similar and based off from the correct template.

    Question: Short of copying and pasting all the data from one sheet to the correct format, which wouldn't be practical since I'd be copying and pasting formulas over different formulas, what's the best and easiest way to reformat the reports I recieve? The sheets are fairly straight forward, but each has several tabs.

    Since I'll be recieving several different formats, writing a MACROS for each of them doesn't seem practical either, what I was hoping to be able to do would be to apply the formatting from the desired template to the other sheets, is there a way to do this?

    How many reports do you receive? If it's a low number, and you really can't enforce your tecj leads to utilize a standard layout (which would be the BEST solution), you may just write a custom macro (or better yet, a series of VLOOKUP/HLOOKUP/SUMPRODUCT formulas) for each report you receive to pull data from them into a common layout, and from there, feed the data into your existing process. This would assume that the individual reports are each consistently formatted from week-to-week (i.e. for tech lead A's report, you can always count on data point Y residing in the column A, data point X residing in column B, etc.)

    Ya, eh?

    Escanaba

  • Contact the 247 help desk. If you sweet talk them well enough, they will share their proprietary Excel formulas with you.

    Johnny Alpamayo

  • Escanaba said...

    How many reports do you receive? If it's a low number, and you really can't enforce your tecj leads to utilize a standard layout (which would be the BEST solution), you may just write a custom macro (or better yet, a series of VLOOKUP/HLOOKUP/SUMPRODUCT formulas) for each report you receive to pull data from them into a common layout, and from there, feed the data into your existing process. This would assume that the individual reports are each consistently formatted from week-to-week (i.e. for tech lead A's report, you can always count on data point Y residing in the column A, data point X residing in column B, etc.)

    There are going to be maybe a total of 45 spread sheets a week. Probably several different formats. I didn't want to have to write seven different MACROS that I would have to apply each week to some 45 different xls. I've got an email out to the developer of the template and original VBA-MACROS that mines the template, but she's out this week. I guess I'd have to look at that code to see how specific it is to the template it mines.

    Johnny2x2x ●

  • I'm assuming you can't simply data validate the input columns your tech leads use....make it a drop down where they choose from a list?

    doc_spartan ●

  • Scrap Excel, use SQL Server and write an SSIS package for each type/layout of source data to homogenize it.

    Join Date: 06-12-2001 RCMB vBull #32 # Total Posts: 35,866

    Floyd Robertson ●

  • Floyd Robertson said...

    Scrap Excel, use SQL Server and write an SSIS package for each type/layout of source data to homogenize it.

    The Excel template and data mining MACRO are going to have to be used as it's "best practice" but basically, could I create an SSIS package to move the data from one Excel format to the preferred one?

    No experience with this, how hard is it?

    Johnny2x2x ●

  • Johnny2x2x said...

    The Excel template and data mining MACRO are going to have to be used as it's "best practice" but basically, could I create an SSIS package to move the data from one Excel format to the preferred one?

    No experience with this, how hard is it?

    You could create an SSIS package for each Excel format that imports/maps the data into a standardized SQL table, then you could export the data from SQL Server back out to your common Excel format. It wouldn't be very hard to do even without much experience, but you have to be using the Standard or Workgroup editions. The Express version of SQL Server won't allow you to save an SSIS package. Workgroup edition (in 2005 or 2008) will let you create/save package and run it manually. The Standard version will take you as far as automating it to run on a schedule (and move the processed files to another folder if you set it up to do so).

    Join Date: 06-12-2001 RCMB vBull #32 # Total Posts: 35,866

    Floyd Robertson ●

  • Thanks, the capilities you describe are exactly what I need, now I'll see if I can figure out how to implement them. Otherwise I think I am stuck dealing with each one every week.

    Johnny2x2x ●

  • Play

    Piccolo: "Nerd!"

    All Rights go to TeamFourStar and FUNimation, Toei Animation, and Akira Toriayama.

    http://www.youtube.com/v/m8c_4UZdPJs

    Silent Ninja

Michigan State
247Sports
Features
Resources
Connect

247Sports In partnership with CBS Sports