Selecting rows between two string values

[logiemeister]

Hi all,

I'm trying to work out how to split the following rows between the "##############################################" markers in the table below into separate streams.

• There are about ten separate reports/tables in a single csv export.
• The sample is likely to change quite often, which means that the Select Rows tool won't work. 
• I found a query that is somewhat similar HERE, however, the worklow is very specific to the problem posted.

[F1]	[F2]	[F3]	[F4]
##############################################	[Null]	[Null]	[Null]
# Freeform Table	[Null]	[Null]	[Null]
##############################################	[Null]	[Null]	[Null]
[Null]	Visits	Unique Visitors	[Null]
Page	4492	4170	[Null]
Page	4492	4170	[Null]
[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]
##############################################	[Null]	[Null]	[Null]
# Freeform Table (2)	[Null]	[Null]	[Null]
##############################################	[Null]	[Null]	[Null]
[Null]	Visits	Unique Visitors	[Null]
Day	4492	4170	[Null]
26/08/2018	35	35	[Null]
27/08/2018	414	393	[Null]
28/08/2018	766	728	[Null]
29/08/2018	1312	1262	[Null]
30/08/2018	1526	1480	[Null]
31/08/2018	440	430	[Null]
[Null]	[Null]	[Null]	[Null]
##############################################	[Null]	[Null]	[Null]

I've trawled the community, but can't find a solution. Can anyone help with this or point me in the right direction?

Chris

[BenMoss · Accepted solution]

Here is how I would come to a solution around this.

Firstly, I would create a new column, this column will only be populated for the rows which match a table name structure. This can be done with a regex formula...

IF REGEX_Match([F1],"# .+")
THEN [F1] 
ELSE NULL() 
ENDIF

Lets call this new column 'Table Name'.

I then use the multirow formula trick commonly used to perform a 'fill down'.

This leaves me with a table which looks something like...

Then we just need to filter out the useless rows which again can be done with a regex filter.

I've attached an example workflow for you.

Ben

[ponraj]

Here is the sample workflow for your case.  Hope this is helpful. 

Workflow

[logiemeister]

Thanks for the elegant solution - regex was perfect for this use case. 

@ponraj - yours also got me to the finish line, so I've starred yours too. Thank you

Chris