Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Data of 4 groups data (in 3 columns) using common unique index

    Hi all,

    I am new to the forum and hope to enlist some help with my little teaser. I have a attached an excel work book that contains two worksheets:

    1- list: This is the source date organised into four sets on data on the same worksheet, each consisting of 3 columns: Unique code, group character, detail description.

    The four groups were originally a simple matrix with the hazard codes as column headers (common unique index for all) and the individual assessment criteria down column A - I found a tiny script that turned the matrix into a list of text string, which I was able to convert into the three columns using text-column feature. I thought at this point I could do this with a pivot table, but to arrange in the table shown proved to be impossible without a lot messing around.

    Operations: P
    Origin / Consequence (these are paired): G / N respectively
    Life Cycle: L
    Event: A

    I thought that these codes may make it easier to sort the data in some way.

    2 - Table: How I would like to organise the data so that the four groups of data associated each hazard code (e.g. ME001) into an easy-to-read format shown

    I have come to the conclusion that I need a clever piece of VB to do this and this where I have reached the limits of my ability - I can modify stuff, but not create anything from a blank sheet of paper.

    I would therefore be grateful any help, assistance, ideas on how to generate this 'table' from my 'list' works sheet.

    Thank you in advance.

    Perry
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    First of all, a big Welcome to the forum!

    A good start from you by supplying a sample file!

    So, I have attached a file with my first take on your request.
    In my file, I have added two sheets:
    One sheet is used for creating a list of unique HAZIDs for processing.
    The second additional sheet is used as a temporary processing stage:

    There are two VBA routines included as Step1 and Step2 on new sheet [HAZIDcodes].
    Click button [Step 1] to create the HAZID processing list.
    Click button [Step 2] to process each HAZID into your Table.

    I have started with a clear Table.

    This should get you started.
    I have not tidied up all the formatting for the [Table] sheet.
    I have left this as an exercise for you, or for some other forum helper.

    The Table gives the data in sorted order.
    NOTE: When you look at your original file, for code ME001, column [G] has entries for items 139: and 155, but is missing the item 157: which is included in your source data on sheet [list].
    The automated VBA processing includes this item.

    Let us know how you get on with this.

    zeddy
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Feb 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thank you for the rapid response - Tried the routines and step one worked OK - Step 2 stopped at zCount (see attached png screen shot). Ran this on a MAC version of windows excel not sure if this makes a difference.

    Tidying up excel work sheets and make them look professional is not a problem for - I have problems with the clever stuff.

    Best Regards

    Perry




    Screen Shot 2013-02-21 at 21.35.48.png

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    I tested everything OK with Excel 2007 and Excel 2010, but not on a MAC Excel version.

    I can check it on a MAC this Sunday. (I am travelling tomorrow).
    Unless someone else can before then?

    You could try commenting out that particular VBA line, as I don't think it's really needed.
    But I suspect it's because of the MAC version.

    zeddy

Tags for this Thread

Posting Permissions

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