REMOVING THE ROWS WITHOUT CLEAR IDENTIFIER

[dunkindonut7777]

Hi I find it difficult to remove the rows that are not needed in the data, these rows do not have specific basis in filtering out using filter tool. This data is sample only and it  has 2000 lines in total and I really find it difficult to remove these unnecessary rows.\

Raw data:

Number	Descr	Amount
11000-05	ARISING FROM REPURCHASE	500.00
11000	TOTAL INTERBANK	500.00
11000	TOTAL INTERBANK LOANS RECEIVABLE	500.00
11410-99	LOANS & DISCOUNTS	400.00
11410-88	LOANS & DISCOUNTS-DEMAND LOANS	600.00
11410	TOTAL L & D-DEMAND LOANS	1,000.00
11410	TOTAL L & D-DEMAND LOANS DISCOUNTS	1,000.00
11420-99	LOANS &DISCOUNTS-BILLS DISCNTED	400.00
11420	TOTAL LOANS & DISCOUNTS-BILLS	400.00
11420	TOTAL LOANS & DISCOUNTS-BILLS DISCOUNTED	400.00

Expected output:

Number	Descr	Amount
11000-05	ARISING FROM REPURCHASE	500.00
11000	TOTAL INTERBANK	500.00
11410-99	LOANS & DISCOUNTS	400.00
11410-88	LOANS & DISCOUNTS-DEMAND LOANS	600.00
11410	TOTAL L & D-DEMAND LOANS	1,000.00
11420-99	LOANS &DISCOUNTS-BILLS DISCNTED	400.00
11420	TOTAL LOANS & DISCOUNTS-BILLS	400.00

I want to remove the one with the grand total, the one I highlighted with. Is there any other way to remove it?

(I also attached the sample file). Can you help me with this one pls?

[Luke_C]

Hi @dunkindonut7777 

Here's how I'd do this:

1. Filter out empty rows
2. Remove duplicates on number column. The unique tool keeps the first instance, so the grand total for each section gets removed

[apathetichell]

You can filter for the word total in Descr - but really this is specific to your data and your data formats and you could easily drop a needed row with the total in the Descr. the blank rows are filtering for where !isempty([number])

Rethinking this- you can definitely breakdown your GL or other codes into stems (like 11000) - you can then find the last/max value in those categories and assume they are subtotals (and other descriptions with the word total in it are regular descriptions). You can then filter. Or as pointed out by @Kenda you can use multi-row formula if spacing is standard.

[Kenda]

Hi @dunkindonut7777 

Will it always be the case that the row you want to filter out will have a blank row above and below it? If this is true, you could use a Multi-Row formula tool to create a new field that will identify rows that are blank above and below then use a filter after that on that new field to get rid of those rows with the identifier. Hope this helps!

Resource on Multi-Row formula tool: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Row-Formula/ta-p... 

[atcodedog05]

Hi @dunkindonut7777 

Here is how you can do it.

Workflow:

Hope this helps : )