Can the MultiRow Formula be configured with dynamic rows?

[joesparty]

Hello,

I was looking at the Tool Mastery | Formula blog and wanted to know if it is possible to use it with dynamic rows.  In the attached my formula is working when comparing 2 records.  Refer to Unique ID='74', the first 2 records are correct, the 3rd is null when it should be 'Different'.

I thought about using the summarize first to see if I could find all the combinations, but was stuck on how that would help.

Cheers!

[JamesCharnley]

Hi @joesparty,

I think that the Multi Row tool should be enough for the use case. I'm not entirely sure what you're looking for because the formula is working as I'd expect. Do you mind providing a little bit more of an explanation please? In the ID 74 instance for example, row 36 customer does equal row 35 customer and so it isn't being tagged as different, and is therefore ending up as null.

One small aside, you can group by ID in the configuration too so you don't have to add the ID check in the formula.

[binuacs]

@joesparty are you looking for something like the below?

[joesparty]

@JamesCharnley thanks for the reminder of the group by! 

For ID 74 there are more than 2 customers. I would want to flag that 2 of the records are different, 2 are the same.

74 0034008699 Different
74 0013012900 Different

74 0013012900 Same

So if an ID group is flagged as Different then I don't want to review it.  ID 74 meets the condition when comparing record 1 and 2, however record 2 and 3 I would want to review because it is the same customer.   

[JamesCharnley · Accepted solution]

No problem @joesparty 

If what you're wanting to do is identify rows for further review then I think that both the multi-row logic and summarize you have could both be right, depending on which rows you're looking for. Using our ID 74 example, if you're only interested in that third row when customer is the same as the previous row, your logic seems fine and you should be able to filter those rows marked Same?

If you're interested in all the rows when the customer matches within the same ID, then I feel like your idea about the summarize should work too without having to come up with a new multi-row formula, and you can just filter to rows where count > 1 and then join back to your data to get all the rows where that customer number is duplicated within an ID.