Duplicates Pulling through from "Index" formula

**AK262007** · 04-10-2014, 12:06 PM

Hello,

The attached sheet is an example of a tracker sheet that I am trying to put together. No matter how I look at this I cant get my head around how to make this work

Basically It would work fine if all of the amounts were different. But in the cases where the retailers have been offered the same level of support YTD it is causing duplicates to pull through into the formulated tab.

I don't think I have explained this particularly well however I am hoping the attached will clear up my query.

I hope that someone can help me with a fix here.

I have highlighted in red where the duplicates are showing

PS - It does need to be formula driven

TEST Manual Bonus Tracker 2014.xlsxAppreciate your time and help

**Pete_UK** · 04-10-2014, 12:29 PM

Put this formula in cell Q2 of the Tables sheet:

=RANK(P2,P$2:P$153)+COUNTIF(P$2:P2,P2)-1

then copy down to Q153. Then in the Actual sheet you can have this formula in B2:

=IF(INDEX(TABLES!B:B,MATCH($A2,TABLES!$Q:$Q,0))="","",INDEX(TABLES!B:B,MATCH($A2,TABLES!$Q:$Q,0)))

This can then be copied across to P2, and then use the Format Painter to copy the format from row 3 to row2, then copy the formulae on row 2 down your table.

Hope this helps.

Pete

**AK262007** · 04-11-2014, 04:09 AM

Pete. You are a star.

Thank you very much !

Duplicates Pulling through from "Index" formula

LinkBack

Thread Tools

Rate This Thread

Display