Identifying last unique row

[fordfold]

So I have a table of values each made up of an odd number of rows for an invoice (used mod to filter out evens as don't care about those).

Each set of grouped values has the same invoice date, cost centre and account code and I know the number of rows for each invoice using a count.

I just need a way to call out the last row for each invoice, so rows 3 and 8 below? Not sure a multi row formula would work because there could be potentially lots of rows for each invoice.

RecordID	value	Row count	Invoice date	cost centre	account code
1	-10	3	02/02/2019	S300	FZ345
2	10	3	02/02/2019	S300	FZ345
3	10	3	02/02/2019	S300	FZ345
4	-20	5	01/01/2019	S400	AS123
5	-20	5	01/01/2019	S400	AS123
6	20	5	01/01/2019	S400	AS123
7	20	5	01/01/2019	S400	AS123
8	20	5	01/01/2019	S400	AS123

[JoshuaGostick]

HI @fordfold

If the RecordID continues all the way through the data, you could use a Sort tool to flip the data so that the highest RecordID is at the top. Then, you could use the Unique tool which will keep the first unique record which in this case will be the last because the data is flipped.

Does this make sense, and does this solve your problem?

Thanks,

Josh

[afv2688 · Accepted solution]

Hello @fordfold ,

You don't need a multi row formula, you can do it easily with the sample tool:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[Jonathan-Sherman]

Hi @fordfold,

(1) Sort by recordid in descending order

(2) Sample top 1, grouping by invoice date, cost centre and account code

(3) Sort by recordid in ascending order

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[fordfold]

Wow thanks for the quick replies guys. I think the second solution with the Sample tool is the simplest but all appear to work. So I have learnt three new techniques!

Have to say this board is a great resource for me, I've been struggling with this for ages but you allowed to get over the hurdle and learn something new at the same time.