I was given the data below and was asked to calculate the actual price column which depends on column S as well as two grouped values in column T and column V based on column R. The calculation logic is quite easy but there is a lot of data rows. I can't figure out how to do this in excel in a systemic way. Doing this manually could be cumbersome due to the large amount of data.

CodePudding user response：

My first suggestion would be to get unmerged source data.

My second: use helper columns.

Assumption: merging is based on the name column.

You get the unmerged values with the formulas:

• projectTotal unmerged: =INDEX($T$2:$T$8,MATCH(R2,$R$2:$R$8,0))
• actualTotal unmerged: =INDEX($V$2:$V$8,MATCH(R2,$R$2:$R$8,0))

INDEX returns the first value of each group.

You can either use these formulas within your formula or you really build those helper columns.

CodePudding user response：

This is what I have tried, may be there is more eloquent way to resolve this query, but here is as is,

• Formula in cell U2

=ROUND((LOOKUP(2,1/(($V$2:V2<>"")*($R$2:R2=R2)),$V$2:V2)/
LOOKUP(2,1/(($T$2:T2<>"")*($R$2:R2=R2)),$T$2:T2))*S2,2)

• LOOKUP() Function will Auto-CSE this formula for you,
• This formula works because the top left cell of a merged range holds its value,
• Lastly, try to avoid merged cells they are Excel's worst nightmare.