Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Making complex rolling list

    I don't know if you can do this in excel without VBA (which I don't want to try because I won't be around to support it) I will try to explain this as best I can to see if what I want is possible in Excel or not.

    I have 14 different part numbers which are all tested after they are run through a kiln at different settings. I would like to be able to dump all of their kiln data, test results and part numbers into one list. Then I would like to be able to pull the last 30 batches for any given part number (note not last 30 days because some parts don't run for months at a time, just the last 30 batches run) and put the kiln data and test data into columns so that i can do some math with them. I figured out how to do this by date, but like I said above I can't use dates. I am trying to make the work I do easier for the next guy after I leave this job.

    PS. As this is the first time I found this website I have already solved quite a few other spreadsheet issues thanks for having such a good resource online it is much appreciated.

    Thanks,
    Ed

    Example of data dumped:



    Batch # Disk Type Firing Cycle Test Data
    83001 AV6NG 989 11.19
    83002 AV3RG 1026 5.41

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,269
    Thanks
    46
    Thanked 248 Times in 228 Posts
    Ed,

    Welcome to the forum.

    From what are you dumping kiln data (.txt, .doc, .pdf, .xlsx)? Could a simple sort with a key (date) and a second sort key (Some type of part identifier) give you the most recent batches? In a sort, Dates do not necessarily mean the last 30 days. If you post a sample dump file and a sample file displaying the format and header you want it dumped into, it would make you request easier to accomplish. A brief explanation of the numbers you posted and which ones would be used in the calculations you spoke of would help determine what cell format to use for the columns.
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    processengineer (2014-07-21)

  4. #3
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,721
    Thanks
    7
    Thanked 236 Times in 224 Posts
    Assuming "Disk Type" is the part number and "Batch" is always sequential, sort by "Disk Type" and then "Batch".

    cheers, Paul

  5. The Following User Says Thank You to Paul T For This Useful Post:

    processengineer (2014-07-21)

  6. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    231
    Thanks
    0
    Thanked 11 Times in 11 Posts

    Sorting data for futher analysis

    Ed

    Attached is a sample of what I think you are looking for.

    However, it may be better to Pivot Table the Data and use the Pivot Table Filters to extract the desired results. By example use the page for the part number.

    Hope this can help.

    Regards,

    TD
    Attached Files Attached Files

  7. The Following User Says Thank You to duthiet For This Useful Post:

    processengineer (2014-07-21)

  8. #5
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry for the long time between responses, I had to install a Compressor that isn't working. Attached is a sample of the data as I've entered it for now. Currently all data is entered by hand. I am going to try to work with a pivot today, had not thought about that thanks.
    Attached Files Attached Files

  9. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does this do what you want?
    Select the "Disk Type" from Z1 (it pulls from the list in Col AI)

    It pulls the 30 highest batch numbers (or gives a blank if <30 are in the list) for the chosen disk type[Col z]
    It gets the row with that batch num and that disk type [Col Y] and then pulls things from the column [The items in AB1:AG1 are the column that matches the heading]

    Steve
    Attached Files Attached Files

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    processengineer (2014-07-24)

  11. #7
    New Lounger
    Join Date
    Jun 2014
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Does this do what you want?
    Select the "Disk Type" from Z1 (it pulls from the list in Col AI)

    It pulls the 30 highest batch numbers (or gives a blank if <30 are in the list) for the chosen disk type[Col z]
    It gets the row with that batch num and that disk type [Col Y] and then pulls things from the column [The items in AB1:AG1 are the column that matches the heading]

    Steve
    Thank you Steve,

    This got me what I needed, much appreciated.

Posting Permissions

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