Returning totals in a series of tables

Happy Xmas All..........

I have a data dump which is a series of tables, the layout and headers of the tables are fairly fixed however the number of rows in each table is constantly changing. What i would like to do is return the table header, and the average turnaround for each table, preferably without amending the tables. Attached is a sample, these tables marked task C1 task C2 etc are the table headers and in the cell next to Average turnaround is the value i would like to return. I would also like to return the bottom value of each table in column C so at the end it should look like:

Task C1 434 2.52
Task C2 16 3.00
Task C3 10 2.20
Task C4 329 3.24

I do not want to amend or adjust the table as this sheet continues on for 3000 more tables, which is the reason i am looking for a VB lookup type function.

Sheet Attached..

Really appreciate any help or guidence i can get.

Thanks Everyone

Nick

see attached example (non VBA approach)

Nick, previous file had inconsistent formulas in Col A on summary sheet, please find attached updated (corrected) version:

The first:

Please Login or Register  to view this content.

Establishes the starting row position of each table.

Best to break down into component parts but first we must note that A1 holds the count of "Task" tables on the data sheet -- let's assume per my sample file that the result is 7.
-- this is a very important value as it permits us to reduce the number of expensive calcs we perform thereafter.


So

=IF(ROW()-1<=$A$1,do if TRUE, do if FALSE)

Determines which action should be undertaken based on whether current ROW - 1 <= A1 (count of tables) ... so if we're in Row 2:

ROW()-1 => 1
A1 = 7

Thus perform action for TRUE result....

TRUE action:

SMALL(IF(LEFT(Sheet1!$A:$A,4)="Task",ROW($A:$A)),ROW()-1)

This will establish the row position of Table x where x is determined by the ROW()-1 value ... ie Table 1 if we continue to use the example of the formula being in row 2.... it does this by creating an array of row numbers where the rows contain TASK in column A... it uses SMALL function to retrieve the appropriate number for this particular table .. so in Row 2 it's looking for the first table -- and thus the SMALLEST value, ie SMALL(values,1) ... in row 3 it would look for 2nd SMALLEST value, ie SMALL(values,2) -- the ROW()-1 ensure that the k argument in SMALL incremements accordingly.

If ROW()-1 > $A$1 then in theory there are no more tables to look for so we do not want to continue to use the SMALL approach... for ex if the formula were in ROW 9 then SMALL(values,8) would return an error as there are only 7 numbers in our range of values given there are only 7 rows in Col A on source sheet containing TASK -- does that makes sense ?

So if ROW()-1 > $A$1 then do FALSE action

IF(ROW()-1=$A$1+1,LOOKUP(REPT("z",255),Sheet1!$A:$A,ROW($A:$A))+1,0)

It follows that to retrieve the values we need (ie Tunaround and Days etc...) we need to know the starting position of the NEXT table... ie if we're looking at TABLE 1 we need to know where TABLE 2 starts... if we know that we can use that value to determine which cells we want to look at to return the appropriate values for TABLE 1 -- ie we know TURNAROUND will be in the row above the start of TABLE 2... however we have a problem insofar as for TABLE 7 we don't have a TABLE 8 to use to determine the correct row from which to retrieve data... so we need to do slightly different things pending the ROW()-1 value... if we're on ROW 9 (ie TABLE 8 were it to exist) we want to pretend there's a TABLE 8 so our subsequent formulas will work (Cols B:D) ... we can do this using a LOOKUP approach.... so

IF(ROW()-1-$A$1+1,Action if TRUE, Action if FALSE)

TRUE Action

LOOKUP(REPT("z",255),Sheet1!$A:$A,ROW($A:$A))+1

we use LOOKUP to find the LAST text entry in Column A on Sheet1 and we return the row position thereof -- if we excluded the +1 at the end the resulting value would be the row position containing the last cell in A containing "Average Turnaround" -- however for consistency we need this value to pretend to be the starting point of TABLE 8 so we add 1 to the result as this is where TABLE 8 would commence in terms of row position should it exist.

I'm not going to go into detail re: LOOKUP in this context as it is complex but in short it is using the Binary Search method -- and given data is unsorted it returns the last value of the same type as criteria... REPT("z",255) essentially creates a string 255 chars in length where z is character -- ie zzzzzzz (etc...) ... this ensures (in most circumstances) that you find the last text string... if we you were trying to find the last number in a range you would use a big number such as 9.9999E+307 ... there are for more advanced users than me who will discuss this method in various papers online should you wish to research further

FALSE action

0

So where ROW()-1 > $A$1+1 (ie ROW 10 --> 9 greater than 8) then simply return 0.

-----------------------------------------

So at this point we now have list of values in Col A which essentially dictate starting row position of each table we're interested in... all we need to do now is use these values as pointers as to where we should be retrieving our data from... we use INDEX to do this...

INDEX(range,row,column)

eg
INDEX(A1:D10,4,3)
Would return value of C4 (the value in Row 4 & Column 3 of the specified range)
(see XL Help for more info if required)

So to explain this:

=IF(ROW()-1>$A$1,"",INDEX(Sheet1!B:B,$A3-1,1))

Again assuming the above is in row 2 -- this says if the Table doesn't exist simply return a null value (ie ROW()-1 > $A$1), if the Table we're looking for does exist then return contents of

INDEX(Sheet1!B:B,$A3-1,1)

Given we're in Row 2 we essentially look for starting position of the NEXT table (as specified in row 3) and subtract 1 from that value... this gives us the row containing Average Turnaround for our current table -- we reference column B to return the value.

It is because we reference the Next row for the Row value in our INDEX calc that we use the LOOKUP element to estabish a starting position for TABLE 8 even though technically it does not exist... as this value permits us to establish the correct row for Average Turnaround for Table 7 which does exist.

I hope that makes sense... probably not ... I would concede that it's a non-trivial solution.