# Consolidate Data

1. ## 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

2. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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