Returning a name to a summary sheet

Hi All

I need some help with a formula that would return a contract name to a summary sheet however it is complicated due to the way the data is laid out.

I am hoping either a formula can do this but a pivot table would be ok if this can be done on multiple ranges.

On the summary sheet I have two columns for each BU (Contract and Overhead). If in columns F,O or X it contains a “C” then it would need to go to column D of the summary sheet or if it contains a “O” then it would need to be returned to column E.

The problem I have is that I have empty rows in the data which need to be ignored.

On the attached example I have populated what I need – I have done this manually.

Can anyone help at all?

I've assumed that you couldn't (or didn't want to) change the layout of the data in the BU sheets, so I've used columns A, J and S in those sheets as helper columns to give each record a unique identifier. Each of those columns has a starting value for the C and O status in rows 13 and 14, so in A13 and A14 these are both set to zero, and then J13 contains this formula:

=COUNTIF(F:F,K13)+A13

which can be copied into J14 and both can be copied into S13/S14. I've used this formula in A17:

=IF(F17="","-",F17&"_"&COUNTIF(F$17:F17,F17)+IF(F17="C",A$13,A$14))

which has been copied down to row 40, then that block of formula can be copied across into J17 and S17 to give the unique references (coloured blue, to help identify them more clearly). This has been done in both BU sheets.

In the Summary sheet I pasted the values that you had in the two tables across to the right for comparison, then used this formula in D5:

=IFERROR(INDEX('BU1'!$C:$U,MATCH(LEFT(D$4)&"_"&ROWS($1:1),INDEX('BU1'!$A:$T,,MATCH(ROWS($1:1)-1,'BU1'!$13:$13)),0),MATCH(ROWS($1:1)-1,'BU1'!$13:$13)),"")

and a very similar formula in E5:

=IFERROR(INDEX('BU1'!$C:$U,MATCH(LEFT(E$4)&"_"&ROWS($1:1),INDEX('BU1'!$A:$T,,MATCH(ROWS($1:1)-1,'BU1'!$14:$14)),0),MATCH(ROWS($1:1)-1,'BU1'!$14:$14)),"")

The only change (shown in red) is that you want to look at row 14 instead of row 13.

Similar formulae can be used in G5 and H5 - the difference here being that you need to look in the BU2 sheet rather than the BU1 sheet.

These formulae were then copied down to row 33.

Hope this helps.

Pete

Hi

Thank you both for replying. Both formulas and examples you provided worked just how they should – thank you!

@Newdoverman – sadly I couldn’t rearrange the data. I am not the owner of the spreadsheet, just been asked to take a look and so I couldn’t change it. In the main model it has lots of other tabs and rows which feeds other models so it wouldn’t be easy to change.

@Pete – thank you for this. I like the concept and I couldn’t think of how to lay it out.

Im going to try and replicate this now in my main model