How do I delete a summary row and all information after the summary row

[mc_ryan]

My excel spreadsheet has header and footers. I started the data import on line 4, but how do I stop it at the summary row and everything after?

[Luke_C]

Hi @mc_ryan 

Without seeing the data it's hard to say, but it could be as easy as leveraging a filter tool, or if there are many records you could use a multirow formula tool to generate a flag to filter out.

[DenisZ]

Alteryx will import it all after line 4. You could then use a Sample tool to extract the first X rows if you want from that or Select Records tool to do the same task. 

Hope it helps. 

[mc_ryan]

It is a data extract and the number of rows are different every time.  I have attached a screen shot. Does that help?

[mc_ryan]

[Luke_C · Accepted solution]

Hi @mc_ryan 

Here's an example using a multirow formula to flag which rows to filter out:

[mc_ryan]

That worked! Thank you. I have a couple rows that have longer names and it creates a 2nd row, but the row is blank. Do I use the same thing to remove those? How do you know what words to use for example you made this "If [Invoice ID] = "Summary" then 1 else [Row-1:Flag] endif"?

[ShankerV]

Hi @mc_ryan 

To answer your last question, the key word is Summary in the Mutli row formula.

That you can replace by any where you want to eliminate the records below.

Breaking down the function for you, 

If [Invoice ID] = "Summary" then 1 else [Row-1:Flag] endif

When it finds the word summary, it places the 1.

Post which below for all the remaining rows the condition will fail, but else states that you need to input [Row-1:Flag]

i.e it will take and fill the previous value 

So it will give the same output even the next line is null or empty.

Many thanks

Shanker V

[Luke_C]

Hi @mc_ryan 

You'd have to look at your data to figure out the appropriate trigger for the flag, from the screenshot I saw it looked like 'Summary' in the first column kicks off the rows you don't want. This would fall apart if higher up in the data there's another record that equals "Summary". 

My example would filter out any records below the record containing "Summary". Are you referring to blank rows within your actual dataset?

[mc_ryan]

It does, but the other empty rows are randomly throughout the data like this: