Forum Discussion
I can't find a formula to spill range with multiple conditions. URGENT !!!
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
Nishkarsh31 Perhaps like in the attached file? Not very elegant, but it seems to work.
8 Replies
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.
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?
PeterBartholomew1
Nishkarsh31 Perhaps like in the attached file? Not very elegant, but it seems to work.
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.
Riny_van_EekelenNishkarsh31 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.
