Weighted average in table with unique ID

Hi,

I have a table with unique identifier, and few columns (Job Run Length 1,2,..; Job Coverage 1,2,...).
I need to create a new table, that merges all the rows by their unique ID, while summing the Job Run Lengths and presenting the weighted average for the Job Coverage.

If it's too complicated to do so without coding, I would like to stay with same table, only with weighted average for the similar Job Id's Coverage.
In the below example, the last 2 rows (in theory there might be more than 2) should either merge to one with Job RL1 total of 11,137, weighted Coverage1 according to (7,281*143+3,856*76)/(7,281+3,856) - same for the next 2 columns, or stay in the same 2 rows only with updated weighted average for Job Coverage 1 in both rows.

Capture.PNG

Thank you all

Welcome to the board! My thought was to create a second table that automatically updates and combines your duplicated values with the proper weighting. You can do so by using the following formula in A2 of your auto-table:

=IFERROR(INDEX('Active Table'!$A$2:$A$6,MATCH(0,COUNTIF($A$1:$A1,'Active Table'!$A$2:$A$6),0)),"")

This formula should be array-entered with Ctrl + Shift + Enter instead of Enter, then filled down. It will return only unique values. Then, in B2:

=IF($A2="","",SUMIF('Active Table'!$A$2:$A$6,$A2,'Active Table'!B$2:B$6))

And C2:

=IFERROR(IF($A2="","",SUMPRODUCT('Active Table'!B$2:B$6,'Active Table'!C$2:C$6,--('Active Table'!$A$2:$A$6=$A2))/B2),0)

These should be filled down and can be copied over to D2/E2 for Job RL 2 and so on for other jobs. The attachment should make things much clearer; give it a try, see if it looks plausible:

You also need to clarify if the merged cells are only for adjoining rows. I assume this is the case, but there is also a b1 higher up your data. The question you posed described the data as unique ids, hence the doubt

Dav

Hi,

First of all thank you for your support. The answer is no, the Unique ID's can return in multiple rows, not necessarily one after the other. Also the file size (length) is not fixed. It can once contain 200 rows, and the time after 300 rows, hence the formulas should cover the whole range.

I've tried unsuccessfully to implement the attached above example, so according to the advice I'm attaching the file. The table on the left is the data table, and I also prepared an output table on the right.

Please help,

Thanks,
Arthur