Stop a value repeating accorss multiple rows

[dave_282]

HI all,
I have query I’m building. I have an outstanding value amount I have split by invoice number and I’ve added those up with the following to get the total amount outstanding by the individual SL code with the following.

=First(Sum([Outstanding Value] In([Sl Code])))

that’s bringing up the value needed correctly, such as in the case of AAA607 of £7.00.
My question is, is there a formula or function I can use so that the value only appears in one row by sl code, instead of repeating for each invoice number? I’ve tried forall but that didn’t make any difference. I’m sure there’s a way to do it as I remember doing it years ago, any help would be appreciated. Thank you.

[MarkP]

You could wrap an If round it to give the logic:
If InvoiceNumber = Min(InvoiceNumber) In (SI Code) Then YourValue

That way, it should present the £7.00 only against 895236 in your example.

Leave your table as is, add the extra column with the above logic to make sure it works then swap it in to your highlighted column above.

[dave_282]

@MarkP

f InvoiceNumber = Min(InvoiceNumber) In (SI Code) Then YourValue

Brilliant, that worked perfectly, thank you very much

[dtolley]

You can also use the Previous function to compare the SI Code of the current row to that in the previous row.