Hi Everyone,
I'm currently working on parsing out pieces of a messy Excel file and would like to get a list of deliverables under a heading.
The problem is, the deliverables heading is always in the same column but not always the same row, and each deliverables list varies in length. The deliverables sections are, however, always preceded by a NULL row and end with a NULL row. So the data looks like:
null |
Deliverables: |
Deliverable 1 |
Deliverable 2 |
null |
I would like to write a formula that looks for the "Deliverables" heading and marks each deliverable until it hits a null row. So by the time the formula is finished, it looks like:
null | False |
Deliverables: | False |
Deliverable 1 | True |
Deliverable 2 | True |
null | False |
Any help would be greatly appreciated. Thank you!
Solved! Go to Solution.
You should be able to use a Multi-Row Formula tool. If every heading is directly preceded by a null value, use this formula: !(isnull([YourData]) or isnull([Row-1:YourData])). This will show true for all of your deliverable records. Note that if your FIRST record is also a heading, you'll need to select NULL from the drop-down for "Values for Rows that don't Exist."
Hope it helps!
Hey @crunyeon! I think you are looking for the Multi-Row Formula tool. Try to add a new bool field with the following formula:
if [Row-1:Field1]="Deliverables:" || ([Row-1:NewField]=1 && !isnull([Field1]))then 1 else 0 endif
Both solutions worked perfectly. Thank you!