Removing duplicate records when indexing (spreadsheet attached)

My title I think is self explanatory, but it may just because I have been working on this for too long. I have duplicate values and the index for the order number remains the same when they should be different, or unique.

Try Pivot Table.

I already have a Pivot Table but I was wondering if there was another way because I have to present this as a summary report eventually and was wondering if there a way to show it in a cell rather than as a Pivot?

Try this:

G7: =INDEX(B:B,SMALL(IF((A$2:A$23="Client 1")*(C$2:C$23=F7),ROW(B$2:B$23)),COUNTIF(F$7:F7,F7)))

Array Formula: Press Ctrl+Shift+Enter, not just Enter
Copy down to G9