Multi Row Formula?

Question

In the sample data attached below, I would like to know if I can do the following.

1.  Always remove the first instance where the Form Name Field is Sch20cB.

2.  Create a field were I assign a record ID to each record, starting with the record that has a value in the SUB EIN field, and changes when it hits the next record when there is a value in the SUB EIN field.  I created a column in the sample data for the desired end result.  Right now I would have 5 records with the same record ID, but that may not always be the case.  There could be more or less

Thanks in advance!.

Sample Data.xlsx

grossal · Accepted Answer

Hi @bsolove,

here is a way to do this:

What happens:

1) Adding a Record ID to preserve the row order

2) Filtering for "Sch20cb"

3) Skipping the first row (Sample Tool)

4) Union everything back together

5) Sort them to the original order

6) Multi-Row Formula to get the custom count

Workflow attached. Let me know if I got it right.

Best

Alex

AbhilashR · Accepted Answer

Hi @bsolove, I use the approach of join to remove the first instance of the Form Name Field 'Sch20cB', and then a combination of multi-row formula and tile tool to get the sequence right. Does this answer your question? Please mark this post as solution accepted if it does.