Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summerize Data (Excel 97 & 2003)

    A user gets an Excel workbook from a Lab Information management system. Data provided by different analysts on the same day and the same project are displayed on different rows. We wish to summerize in such a manner that there is one row per date per project ID. A sample is attached. I am thinking this will take a VBA solution that will loop thru the data array and create the summerized version on a new sheet within the same workbook.
    Who is bored and feels the need to help?

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Summerize Data (Excel 97 & 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Chuck

    OK you say, in the attached file <<< There may be thousands of rows >>>

    But Excel has only 256 columns so how do you want to handle the case when you have more than what can fit on a single row?

    Two, or more, rows will work for you?

    Cheers... I am also from the Government... Really

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summerize Data (Excel 97 & 2003)

    See the macro in the attached workbook. I added a worksheet to store the summary in.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Do you also need code to winterize? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Wassim,
    The original table is being summerized so the result will be less rows than the original. The data categories that are list in C1, C2 etc may go up to the hard limit Exel has for columns. The user will have to work with the Excel sheet they are given (it also cannot exceed 256 columns).
    The summary should have 1 row per Date/ID that reflects the entries found for the categories. The sample has two differetn dates with two different IDs reporting on a total of 5 categories, so the summary shows 4 rows.
    Hopes this helps.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Wassim,

    Chuck stated that there will be up to 100 different items = data columns, so that shouldn't be a problem.

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Hans,
    Got your alert while responding to Wassim. Thanks I will check out your workbook.

    More importantly, NO No winterizing! I am interested in Summer only! Love Summers

    Thanks
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Hans,

    What can I say? It does just what I wanted it to.
    You never cease to amaze me.

    Now, can you do something about keeping Summer around a little longer too?

    Thanks again,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summerize Data (Excel 97 & 2003)

    > Now, can you do something about keeping Summer around a little longer too?

    Macros are free, but that is very expensive. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Hans,

    Interesting.

    As friendly, helpful, smart, etc as your are, I cannot even imagine an evil grin on you. <g>

    Have a great day.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    Hans,

    Interesting development. After I run the macro, it will not let me copy the contents of a cell. It says it cannot empty the clipboard...
    Do you encounter the same thing? The macro seems to work great. I can type new data entries and run the macro again and again, but I will not let me copy anything.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summerize Data (Excel 97 & 2003)

    I cannot reproduce the problem (I'm on Excel 2002 at the moment). Have you tried quitting and restarting Excel? Does the problem reappear then after running the macro?

    Does it help if you add the following line at the end of the macro?

    Application.CutCopyMode = False

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    I went so far as to close Excel. Start Excel and open a different workbook. I get the same message when I try to copy.
    So, I am going to reboot my PC to see if that clears it. Appears to be something OTHER than your beautiful work.

    <g>
    Chuck Reimer
    I'm from the Government and I'm here to help...

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summerize Data (Excel 97 & 2003)

    The PC reboot cleared the problem.
    Evidently, I was sitting too close to the PC and my confusion "Rubbed-Off' and affected the PC.
    All better now!

    Thanks again
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •