+ Reply to Thread
Results 1 to 2 of 2

Consolidate Data

  1. #1
    Registered User
    Join Date
    12-06-2016
    Location
    Hawaii
    MS-Off Ver
    2010
    Posts
    18

    Consolidate Data

    Tracking about 200 personnel with their training/certifications. Without using the generic sort function, is there a more sophisticated method to compile data automatically? I attached a sample of what I am trying to accomplish.

    There are multiple tables that use the same value to determine the status on personnel. Would like to have a separate sheet that consolidates the specified data of each personnel.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    6,381

    Re: Consolidate Data

    Adjust these formulas as required, insert sheet name when referring to ranges etc
    To be honest your spreadsheet should really reflect your original layout even if its just a small amount of data.

    I;m assuming data starts on row 3 cos thats what youve supplied as example data.
    If not change ALL the 3s in the formulas to whatever row the data starts on

    in A23
    =IFERROR(INDEX($A$3:$J$8,AGGREGATE(15,6,ROW($A$3:$A$8)/(($G$3:$G$8="Y")),ROWS(A$3:A3))-(3-1),1),"")

    in B23
    =IFERROR(INDEX($A$3:$J$8,AGGREGATE(15,6,ROW($A$3:$A$8)/(($G$3:$G$8="Y")),ROWS(A$3:A3))-(3-1),COLUMN()+6),"")
    copy across to D23

    copy these formulas down as far as row 28

    in A14
    =IFERROR(INDEX($A$3:$J$8,AGGREGATE(15,6,ROW($A$3:$J$8)/(($D$3:$D$8<=TODAY()+60)*($D$3:$D$8<>"")),ROWS(A$3:A3))-(3-1),1),"")

    in B14
    =IFERROR(INDEX($A$3:$J$8,AGGREGATE(15,6,ROW($A$3:$J$8)/(($D$3:$D$8<=TODAY()+60)*($D$3:$D$8<>"")),ROWS(A$3:A3))-(3-1),4),"")

    copy down as far as row 19

    NOTE: Make sure you format the output grids correctly, e.g. dates and numbers
    Last edited by Special-K; 04-17-2019 at 11:54 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1