Can I nest the IF or any other function inside of the FILTER function to return maybe 1 instead of GBP in column J and return LON instead of LONDON in column B?
Can I nest the IF function inside of the Filter Function?
466 Views Asked by Ilyas Esa At
1
There are 1 best solutions below
Related Questions in EXCEL
- in R, recovering strings that have been converted to factors with factor()
- How to reinstall pandoc after removing .cabal?
- How do I code a Mixed effects model for abalone growth in Aquaculture nutrition with nested individuals
- How to save t.test result in R to a txt file?
- how to call function from library in formula with R type provider
- geom_bar define border color with different fill colors
- Different outcome using model.matrix for a function in R
- Creating a combination data.table in R
- Force specific interactions in Package 'earth' in R
- Output from recursive function R
Related Questions in IF-STATEMENT
- in R, recovering strings that have been converted to factors with factor()
- How to reinstall pandoc after removing .cabal?
- How do I code a Mixed effects model for abalone growth in Aquaculture nutrition with nested individuals
- How to save t.test result in R to a txt file?
- how to call function from library in formula with R type provider
- geom_bar define border color with different fill colors
- Different outcome using model.matrix for a function in R
- Creating a combination data.table in R
- Force specific interactions in Package 'earth' in R
- Output from recursive function R
Related Questions in EXCEL-FORMULA
- in R, recovering strings that have been converted to factors with factor()
- How to reinstall pandoc after removing .cabal?
- How do I code a Mixed effects model for abalone growth in Aquaculture nutrition with nested individuals
- How to save t.test result in R to a txt file?
- how to call function from library in formula with R type provider
- geom_bar define border color with different fill colors
- Different outcome using model.matrix for a function in R
- Creating a combination data.table in R
- Force specific interactions in Package 'earth' in R
- Output from recursive function R
Related Questions in FILTERFUNCTION
- in R, recovering strings that have been converted to factors with factor()
- How to reinstall pandoc after removing .cabal?
- How do I code a Mixed effects model for abalone growth in Aquaculture nutrition with nested individuals
- How to save t.test result in R to a txt file?
- how to call function from library in formula with R type provider
- geom_bar define border color with different fill colors
- Different outcome using model.matrix for a function in R
- Creating a combination data.table in R
- Force specific interactions in Package 'earth' in R
- Output from recursive function R
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular # Hahtags
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
As per our discussion in the comments above, it would be more efficient to manipulate the results of the
FILTER()
function by nesting it inside of another function, rather than attempting to manipulate the entire data set. One such function that would work in this situation is theSWITCH()
function:SWITCH()
looks at each value in the filtered array and replaces any matching values found with their corresponding results. Matching values and results are entered in pairs and can include up to 126 pairs in total. The final optional argument is the default value to be returned if no match is found, which in this case is the original value present in the filtered array. Also,SWITCH()
only performs an exact match, so you can't use wildcards or comparison operators.The above-mentioned formula is the quick and easy method, which works with the sample data you provided; however, it could be prone to errors if "GBP" and/or "LONDON" are present in more than one column. Furthermore, if you're actual data set contains multiple city names in the "Address" column (ie: Birmingham, Glasgow, Liverpool, etc.), managing the list of values and their corresponding results within
SWITCH()
can become quite cumbersome. As such, it would be more appropriate to identify the specific columns in the filtered array that you wish to manipulate and perform the applicable action on each individual column:In this example,
INDEX()
was used to identify the entire "Address" column by omitting the row_num argument and setting the column_num to 2, andLEFT()
was used to simply return the first 3 letters of each value. The "Currency" column was identified in the same manner with column_num 4, andSWITCH()
was used to replace the currency codes with a numeric value. This method works fine if your data set only contains a limited number of currency codes. I didn't specify the final default value argument in this case, soSWITCH()
will return#N/A
if a new code is present (ie: "CAD"), and you'll know the formula needs to be updated with an additional pair of values and results.HSTACK()
is then used to create a new array consisting of the filtered array, plus the new "add" and "cur" columns (7 columns in total), andCHOOSECOLS()
is used to replace the original column 2 with the new column 6, as well as 4 with 7. That's it!On a side note, I'm not a fan of using entire column references like
A:E
,A:A
andE:E
as this will significantly reduce the overall performance of the workbook (you'll likely notice a lag or hesitation when inputting new values in the referenced columns). Structured tables are ideal because the column references are dynamic and expand automatically when new records are added. However, if you prefer standard range references, I highly recommend using a reasonable number of rows that extends far enough below your existing data to allow for growth. For example: if your dealing with only a few hundred rows of data,A2:E1000
,A2:A1000
andE2:E1000
would be adequate, and it's far more efficient thanA1:E1048576
(which is whatA:E
is referencing).Cheers!