May 01 2021 05:36 PM
May 01 2021 05:36 PM
I'm trying to automate the categorization of Bank statement, using manually written Parameters.
I've gotten it to work, but my approach needs separate helper columns for every new row.
Is there a way to do it, with 3 fixed helper columns or I'm open to a completely new approach,
as long as I get my desired output.
I've attached the sample file. It's SELF-EXPLAINATORY.
Also, I've just decided to work this Automation model, do you see any major issues with my parameter model?
@Sergei Baklan @Peter Bartholomew @Detlef Lewin @Riny_van_Eekelen
May 01 2021 09:06 PMSolution
@Nishkarsh31 Perhaps like in the attached file? Not very elegant, but it seems to work.
May 02 2021 12:18 AM
I applied it in a table format. It worked like a charm. I want to add a FILTER formula to filter the descriptions that matched no filter. Can you help me out with that? Here's the updated file. I tried myself, I'm going wrong with the argument.
May 02 2021 01:20 AM
@Nishkarsh31 I dare to say that this is not possible without splitting the description column in its separate components. While you can search for the names "pathak" and "Sanjay" in a large text string, you can not determine which names were not found, as Excel doesn't know "Suman" is a name and that "IMPS" is not. So, break-up the Description column into 6 columns where each column hold the same attribute (i.e. type, name, reference number, etc.) Then the filtering should become easier, although I don't really understand what it is that you want to filter.
May 02 2021 05:56 AM
Since I wanna automate the segregation, I need to know which all entries didn't get matched to the parameters. It worked in the previous formula with individual helper columns. Are you sure it can't be done? I'm attaching a new file where the formula worked.
May 02 2021 07:21 AM
I think the problem has moved on since looked at it this morning (then moved to a phone that had been turned to junk by an overnight iOS update). It is not always possible to return results as a spilt range, much as I would like it to be so. In this case, I think it is possible, since the ...IFS() family of functions split the criteria range from the criterion values (a scalar or lifting to an array).
In general terms, the way of recovering arrays from formulas and functions that work row by row is to use the LAMBDA function and recursive formulae, but that is still work in progress.
May 02 2021 07:25 AM
@Nishkarsh31 Sorry, perhaps I'm missing the point. You obviously want to extract information from the description column so that you can summarise the debit ans credits in various ways. Rather than trying to create all sorts of complicated formulae, focus on splitting up the description column in stead, and then summarise the data with pivot tables, for instance.
While I'm writing this, I see the @Peter Bartholomew has responded. Perhaps he can help you further.
May 02 2021 07:28 AM
Yes, we've figured out a way to do this using sumproduct. We now are stuck at a filter argument, to filter the descriptions which aren't matched. I'm attaching the new file. Please give it a look. I've exhausted my brain over this.
One solution as suggested by @Riny_van_Eekelen sir, is to split the description columns into separate columns using a formula. If that's our only way out. I'm on board, I just wanna get it done now.
In that case though, I checked some of the data entries would have 5, some 4 separate columns. How do we tackle that?
May 02 2021 07:32 AM