Creating a list based on table (harder than it sounds)

Hi all,

I am trying to derive a concise list from a table. There is 1 table per month and will be 1 list for each month.

The January table is set out as follows:

1. Job code (Vertical column A1:A29)
2. Machine (Horizontal A1:Q1
3. The data (B2:Q29)

The data comes from an input tab and will vary each month as to which Machine has worked on which job. The attached table is a sum of hours logged per machine per job.

The most iterations per month will likely be 10, so I don't mind if the list is has 6 entries with 4 blank - I have put the list in column R to the right of each table.

Thanks in advance for your help.

Mike

Hi, Manwithaplan!

I divide the solution in four parts for your understanding.

First part: [T2] : =IF(S2="","",INDEX(A$2:A$29,AGGREGATE(15,6,ROW(A$2:A$29)-ROW(A$1)/(B$2:Q$29>0),ROWS(T$2:T2))))
Second part: [U2] : =IF(S2="","",INDIRECT(TEXT(AGGREGATE(15,6,1000*ROW(B$2:Q$29)+COLUMN(B$2:Q$29)/(B$2:Q$29>0),ROWS(U$2:U2)),SUBSTITUTE(ADDRESS(1,100,,0),1,0)),))
Third part: [V2] : =IF(S2="","",INDEX(B$1:Q$1,RIGHT(AGGREGATE(15,6,1000*(ROW(B$2:Q$29)-ROW(A$1))+(COLUMN(B$2:Q$29)-COLUMN(A$1))/(B$2:Q$29>0),ROWS(V$2:V2)),3)))

Four part (Joined Text): [W2] : =IF(S2="","",T2&" - "&U2&" hours at $XX/hour - "&V2)

Check file with results. Blessings!

I think there should be a standard function in excel to do this, but this is genius!!

Thanks a lot johnmpl

Mike