SOLVED

I can't find a formula to spill range with multiple conditions. URGENT !!!

%3CLINGO-SUB%20id%3D%22lingo-sub-2314458%22%20slang%3D%22en-US%22%3EI%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI'm%20trying%20to%20automate%20the%20categorization%20of%20Bank%20statement%2C%20using%20manually%20written%20Parameters.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI've%20gotten%20it%20to%20work%2C%20but%20my%20approach%20needs%20separate%20helper%20columns%20for%20every%20new%20row.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIs%20there%20a%20way%20to%20do%20it%2C%20with%203%20fixed%20helper%20columns%20or%20I'm%20open%20to%20a%20completely%20new%20approach%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Eas%20long%20as%20I%20get%20my%20desired%20output.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI've%20attached%20the%20sample%20file.%20It's%20SELF-EXPLAINATORY.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EAlso%2C%20I've%20just%20decided%20to%20work%20this%20Automation%20model%2C%20do%20you%20see%20any%20major%20issues%20with%20my%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eparameter%20model%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2314458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314568%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20the%20attached%20file%3F%20Not%20very%20elegant%2C%20but%20it%20seems%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314637%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314637%22%20slang%3D%22en-US%22%3E%3CP%3EI%20applied%20it%20in%20a%20table%20format.%20It%20worked%20like%20a%20charm.%20I%20want%20to%20add%20a%20FILTER%20formula%20to%20filter%20the%20descriptions%20that%20matched%20no%20filter.%20Can%20you%20help%20me%20out%20with%20that%3F%20Here's%20the%20updated%20file.%20I%20tried%20myself%2C%20I'm%20going%20wrong%20with%20the%20argument.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314698%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3BI%20dare%20to%20say%20that%20this%20is%20not%20possible%20without%20splitting%20the%20description%20column%20in%20its%20separate%20components.%20While%20you%20can%20search%20for%20the%20names%20%22pathak%22%20and%20%22Sanjay%22%20in%20a%20large%20text%20string%2C%20you%20can%20not%20determine%20which%20%3CEM%3E%3CSTRONG%3Enames%3C%2FSTRONG%3E%3C%2FEM%3E%20were%20not%20found%2C%20as%20Excel%20doesn't%20know%20%22Suman%22%20is%20a%20name%20and%20that%20%22IMPS%22%20is%20not.%20So%2C%20break-up%20the%20Description%20column%20into%206%20columns%20where%20each%20column%20hold%20the%20same%20attribute%20(i.e.%20type%2C%20name%2C%20reference%20number%2C%20etc.)%20Then%20the%20filtering%20should%20become%20easier%2C%20although%20I%20don't%20really%20understand%20what%20it%20is%20that%20you%20want%20to%20filter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315075%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20problem%20has%20moved%20on%20since%20looked%20at%20it%20this%20morning%20(then%20moved%20to%20a%20phone%20that%20had%20been%20turned%20to%20junk%20by%20an%20overnight%20iOS%20update).%26nbsp%3B%20It%20is%20not%20always%20possible%20to%20return%20results%20as%20a%20spilt%20range%2C%20much%20as%20I%20would%20like%20it%20to%20be%20so.%26nbsp%3B%20In%20this%20case%2C%20I%20think%20it%20is%20possible%2C%20since%20the%20...IFS()%20family%20of%20functions%20split%20the%20criteria%20range%20from%20the%20criterion%20values%20(a%20scalar%20or%20lifting%20to%20an%20array).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%20terms%2C%20the%20way%20of%20recovering%20arrays%20from%20formulas%20and%20functions%20that%20work%20row%20by%20row%20is%20to%20use%20the%20LAMBDA%20function%20and%20recursive%20formulae%2C%20but%20that%20is%20still%20work%20in%20progress.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20523px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277354i50B9FD956A0F0519%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315076%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3BSorry%2C%20perhaps%20I'm%20missing%20the%20point.%20You%20obviously%20want%20to%20extract%20information%20from%20the%20description%20column%20so%20that%20you%20can%20summarise%20the%20debit%20ans%20credits%20in%20various%20ways.%20Rather%20than%20trying%20to%20create%20all%20sorts%20of%20complicated%20formulae%2C%20focus%20on%20splitting%20up%20the%20description%20column%20in%20stead%2C%20and%20then%20summarise%20the%20data%20with%20pivot%20tables%2C%20for%20instance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20I'm%20writing%20this%2C%20I%20see%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3Bhas%20responded.%20Perhaps%20he%20can%20help%20you%20further.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315087%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20find%20a%20formula%20to%20spill%20range%20with%20multiple%20conditions.%20URGENT%20!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315087%22%20slang%3D%22en-US%22%3EHi%20sir%2C%20I%20was%20trying%20different%20arguments%20in%20filter%20function%20to%20get%20it%20done.%20But%20none%20worked.%3CBR%20%2F%3EI%20agree%20with%20you.%20I've%20decided%20to%20split%20the%20description%20column%20into%20different%20columns.%3CBR%20%2F%3EThank%20you%20so%20much%20for%20the%20idea.%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%20I%20don't%20wanna%20use%20a%20pivot%20table%2C%20I%20need%20things%20to%20be%20automated%20and%20don't%20wanna%20refresh%20at%20every%20new%20entry.%20I've%20decided%20to%20use%20a%20combination%20of%20LEFT%2CMID%20to%20make%206%20helper%20columns%2C%20and%20use%20match%20function%20to%20automate%20the%20segregation.%3CBR%20%2F%3EDo%20you%20think%20that%20would%20work%3F%20Can%20you%20help%20me%20out%20with%20that%3F%3C%2FLINGO-BODY%3E
Contributor

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  

8 Replies
best response confirmed by Nishkarsh31 (Contributor)
Solution

@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_Eekelen 

@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.

 

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.Capture.PNG

 

@Riny_van_Eekelen 

@Nishkarsh31 

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.

image.png

@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.

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?
@Peter Bartholomew 

Hi sir, I was trying different arguments in filter function to get it done. But none worked.
I agree with you. I've decided to split the description column into different columns.
Thank you so much for the idea.

However I don't wanna use a pivot table, I need things to be automated and don't wanna refresh at every new entry. I've decided to use a combination of LEFT,MID to make 6 helper columns, and use match function to automate the segregation.
Do you think that would work? Can you help me out with that?