Extracting rows between two designated rows with variable space between.

[Manij1]

Hello all,



I am having a hard time extracting rows above rows designated with an asterisk, and then looping this action so that I can assign every piece of data to a number. For instance, the first rows above *FIFO should be extracted until it meets the row *COGS with an asterisk and all should be designated as 1 (in a new field), then the next set of rows until it meets *R&D should be designated as 2, and so on. In the larger data set, (not here but similar to the one posted) there are a variable amount of rows in between the rows designated with an asterisk. Additionally, is there a way to only target between rows with ONE asterisk? As there may be rows that have more than one asterisk. I would like to do this loop until the entire data set has been parsed.

[BrandonB]

Use a formula tool to create a new column. Use the formula IIF(Contains([Description],"*"), 1, 0) and set the data type as int64. Then use a running total tool where you create the running total on this new field that you have created. Every time it hits a * it will increment one number, thereby starting the next group. 

[Jonathan-Sherman]

Hi @Manij1,

Hopefully this is what you were looking to achieve:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[BrandonB]

 

[BrandonB]

Also, if you want to just extract where the left most character is an asterisk, you can change the formula to the following:

IIF(Left([Description],1) = "*", 1, 0)

This will just look at the first character and ensure that it is an asterisk rather than a contains because you mentioned that there could be multiple asterisks in the field. 

[Manij1]

Thanks Brandon for the help.

Your method did get me closer to my idea of a solution, however I need to be able to differentiate between rows that have just one asterisk and more than one asterisk. The Boolean picks up every row that contains the asterisk. 

[BrandonB]

I think that this one should help you out, assuming that the asterisk is always in the first position. Notice how in the screenshot below the counter stays at 0 unless the asterisk is the very first character. Workflow attached

[Manij1]

Hello Johnathan,

Thank you for the response, however your workflow only exploits the null values of the data, and therefore its not able to differentiate between rows with one asterisk and multiple asterisks. I have attempted to give you a mock up for the data, but my apologies as I am not able to post the real data due to it being sensitive. Is there a way to differentiate between rows with one asterisk versus multiple asterisks?

[BrandonB · Accepted solution]

There is also a formula called regex_countmatches() that can be used for counting the number of times a character or characters pops up. See the formula and screenshot below as an example: 

REGEX_CountMatches([Description], "\*")

[Manij1]

Yes, but in regards to other rows that have multiple asterisks as the first character, how would we differentiate between, say *COGS and **COGS?