Filter function (not VBA)

[c_skytte]

This has nothing with VBA to do!!

I'm really struggling in how to filter data from a huge imported CSV file and I hope some of you might be able to help me out!

Basics:
I got 2 tabs; "Sheet1" and "StockTable_".

The main idea is to let the user choose and:
1. Select up to 8 Headers between approx. 70 different Headers from the Table "StockTabel_" in dropdowns in range = (D4:K4)

2. Select which column to search in = (E1), only based upon selected headers in range (D4:K4)

3. Searchstrings = (G1) and (H1) and only make this search in the selected column = (E1)


The filter I am using in D5 (which is working!) = FILTRER(StockTable_;(ER.TAL(SØG(G1;StockTable_[ItemName])))*(ER.TAL(SØG(H1;StockTable_[ItemName])));"ingenting")
…and in English something like this: =FILTER(StockTable_,(IsNumber(Search(G1,StockTable_[ItemName])))*(IsNumber(Search(H1,StockTable_[ItemName]))),"Nothing found")

The 2 problems:
I cannot isolate the output to ONLY show the columns selected in range (D4:K4)! The output show all the columns from StockTable_!

I have no idea in how to let the “value” = [ItemName] be the variable coming from E1… the filter just fails. Should it be done by use of CONCATENATE?

For overview:

30753

30752

30754

/C

[georgiboy]

Hi c_skytte,

What version of Excel are you currently running?

Are you able to create a mocked up version of the spreadsheet without sensitive data for upload here?

It will make the getting help process better for you and us.

[c_skytte]

Version:
Microsoft® Excel® til Microsoft 365 MSO (Version 2208 Build 16.0.15601.20540) 64-bit

I'll check if that couldn't or wouldn't be possible.
(I'm not world champ in importing and handling data ... but I'll make a copy of the CSV and reduce the lines to a minimum)

[georgiboy]

We would not need the CSV import file I doubt, just the working file that you have taken screenshots of above so that we don't have to try and recreate the data and dropdowns etc...

[c_skytte]

file uploaded ... hope it's done correctly.

/C

[georgiboy]

Right then, I have had my 365 hat on and come up with the below and attached:

=LET(
hm,MATCH(E1,StockTable___2[#Headers],0),
fh,TOROW(MATCH(D4:K4,StockTable___2[#Headers],0),2),
CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH(G1,INDEX(StockTable___2,, hm))))+(ISNUMBER(SEARCH(H1,INDEX(StockTable___2,,hm)))),"ingenting"),fh))

[p45cal]

I didn't notice that georgiboy had posted a single cell formula until just now. I've done similar, it behaves slightly differently (missing headers among the 8, blank values in G1 and/or H1).
Although the Search In value in cell E1 has data validation restricted to the 8 headers chosen, it can be any field of the source table.
As an alternative, I've also converted mine to a lambda name so it behaves like a regular function (see cell D18) which gives hints on what belongs in each argument:

30758

[c_skytte]

Hi again,

I just had a quick check-up on the solutions from the both of you... and it looks great!

The reason why I've made 2 search-cells (G1+H1) is that I'd like to make some wildcard searching, as the ItemNames unfortunately never have been written the same way. Which means that a type called "Ventiltavle" also could be named "Ventil tavle" and I like to show both of the records ... sorry if I left that out in the beginning, but would that be possible?

Thanks

[georgiboy]

In the formula I have edited for you, it will bring in results from both of the search boxes. It will also allow you to change the column being searched.

[c_skytte]

I got it now!


I've changed your code by one figure ... replaced the "+" with a "*"




=LET(
hm,MATCH(E1,StockTable___2[#Headers],0),
fh,TOROW(MATCH(D4:K4,StockTable___2[#Headers],0),2),
CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH(G1,INDEX(StockTable___2,, hm))))+(ISNUMBER(SEARCH(H1,INDEX(StockTable___2,,hm)))),"ingenting"),fh))



to this:




=LET(
hm,MATCH(E1,StockTable___2[#Headers],0),
fh,TOROW(MATCH(D4:K4,StockTable___2[#Headers],0),2),
CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH(G1,INDEX(StockTable___2,, hm))))*(ISNUMBER(SEARCH(H1,INDEX(StockTable___2,,hm)))),"ingenting"),fh))



Now it works as intended or I had in mind!


And I'm still very impressed in how you guys (georgi and p45cal) are working! The prompt way you just are putting your codes together = just great!
THANKS! :bow:

[georgiboy]

I see, have you tested it with the *?

In a filter formula, the + operator is used to combine multiple criteria with the OR logic, and the * operator is used to combine multiple criteria with the AND logic.

Due to the above if you use * the results will have to equal both "Ventiltavle" & "Ventil tavle" at the same time (nothing will load)
If you use + then the results will be "Ventiltavle" OR "Ventil tavle" (so both will load)

[p45cal]

In the attached, always a wildcard search. If G1 and H1 are blank the formula returns nothing.
This time the named lambda function FilterMyTable is in cell D5 (and the longhand lambda is in cell D18, which can be deleted)

re:
the ItemNames unfortunately never have been written the same way. Which means that a type called "Ventiltavle" also could be named "Ventil tavle" and I like to show both of the recordsYou could do this by entering say, enti, in just one of the boxes.

[georgiboy]

Updated the attachment for wildcard search, this does away with the whole *+ thing anyway.

[c_skytte]

Hi again,

It works fine! (but I haven't checked the wildcard function ...yet)

---

As it is now I look into 1 column as per defined in C2.



=LET(
hm,MATCH(C2,StockTable_[#Headers],0),
fh,TOROW(MATCH(A7:H7,StockTable_[#Headers],0),2),
CHOOSECOLS(FILTER(StockTable_,(ISNUMBER(SEARCH(C4,INDEX(StockTable_,,hm)))) *(ISNUMBER(SEARCH(C5,INDEX(StockTable_,,hm)))),"ingenting"),fh))



But would it be possible to extend the filterand have the result shown for the given values (C4 and C5) in 2 columns (C2 andC3) even though the one of the columns would be blank or have an differentvalue?
If it looked like this:
30789

...or is this impossible?

Thanks!
/C

[georgiboy]

Like on the attached?


=LET(HeaderMatch1,MATCH(E1,StockTable___2[#Headers],0),
HeaderMatch2,MATCH(E2,StockTable___2[#Headers],0),
ForHeaders,TOROW(MATCH(D5:K5,StockTable___2[#Headers],0),2),
Filt1,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch1))))),ForHeaders),
Filt2,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch2))))),ForHeaders),
IF(COUNTA(E1:E2)=0,"No Search Columns",IF(AND(E1<>"",E2=""),Filt1,IF(AND(E2<>"",E1=""),Filt2,VSTACK(Filt1,Filt2)))))

[c_skytte]

Hi Georgi,
Close but not quite.

The searchstring is a combination of the values in G1 and G2.

The result of values (strings) from G1 + G2 shoud be found in either E1 or in E2 or in both.

I dont know if it makes any sense?

---

I'll try to explain again:

E1 = Normb.1 (column)
E2 = Normb.2 (column)

G1 = M6 (search value)
G2 = 10 (search value)


Results in:





ItemNumber





ItemName





Normbetegnelse2





Normbetegnelse1





64648
socket head cap screw
M6x10



65465
socket head cap screw
M6 x 100
M6x100


45485
Hex cap screw

M6 x 10












--- --- ---

By the way.
I guess these lines:


Filt1,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch1))))),ForHeaders),
Filt2,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch2))))),ForHeaders),

just have to be expanded with both criterias?
like


Filt1,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH(("*"&G1&"*")*("*"&G2&"*"),INDEX(StockTable___2,,HeaderMatch1))))),ForHeaders),

Thanks...!

[georgiboy]

I am getting a little confused now, you want to search both columns for both search strings?

[c_skytte]

Yes please.

I know it sounds a bit strange, but as some either have copied or even cut the values from the original column to another, I cannot ensure that all searches are correct unless it's done both.

So I hope the LET-command could be expanded?

[georgiboy]

Below is the updated formula, let me know how you get on:

=LET(HeaderMatch1,MATCH(E1,StockTable___2[#Headers],0),
HeaderMatch2,MATCH(E2,StockTable___2[#Headers],0),
ForHeaders,TOROW(MATCH(D5:K5,StockTable___2[#Headers],0),2),
Filt1,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch1))))+(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch1))))),ForHeaders),
Filt2,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch2))))+(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch2))))),ForHeaders),
IF(COUNTA(E1:E2)=0,"No Search Columns",IF(AND(E1<>"",E2=""),Filt1,IF(AND(E2<>"",E1=""),Filt2,VSTACK(Filt1,Filt2)))))

[Aussiebear]

@ Georgiboy, NASA uses shorter code to launch rockets.....:devil2:

[georgiboy]

So when NASA launches rockets, they're literally taking the phrase 'shorter code' to new heights. :rofl:

[Aussiebear]

You are too clever my friend.

[c_skytte]

Hmm...
I'm not getting the results I thought I would.

The current "filter" results in a bunch of data that hasn't any of the searchstrings included.
So maybe the VSTACK fails?
...and by checking the Fx (Functions arguments) NameValue #4 and #5 returns the error #VALUE! in red.

I'd like if the AND-thing (the * and not +) in the lines Filt1 and Filt2 could be used... by that I ensure that both criterias are fulfiled in the result.

Would you like me to upload the current file?

[georgiboy]

Would you like me to upload the current file?

I think it would help at this stage, maybe add some examples in different tabs as to what you expect to see based on the search columns and search strings and I can try to get to the examples.

Will you always be searching similar (Normbetegnelse1 & Normbetegnelse2) columns for potentially duplicated/ missplaced data or will you be searching two different columns for different sets of search strings?

We can make two search columns to look for one value and stack the results if thats the goal, think i am finding the searching two different columns for two of the same search strings a bit strange. For example: if one search column was 'ItemName' and one was 'ItemNumber' you would not want to search both columns for say the two search strings "HUS" & "454" as one would be for the ItemName and the other for ItemNumber.

[c_skytte]

Okay then, but first things first!
The design has changed a lot and it's actually a NEW file! ...the previous one was met with applause and thumbs up, which I hereby forward to you! Thanks alot!

So I’ve attached the file I'm currently working on.



the columns in which the search is done, could or might change from time to time.
I'd like both search strings to be represented in the outcome
If I search M6 in C4 and 30 in C5 in the columns Normbetegenelse1 and Size, I only want hits where M6 and 30 are represented in either Normbetegenelse1 or Size.
That means if Normbetegenelse1 is blank and Size = "M6x 30" (and vice versa), then this takes part in the result.
But if Normbetegenelse1 is blank and Size = "M6 x 20" (and vice versa), then it should not be shown in the result


In the current filter, I get results as Itemnumber 14501 and 16052 even though M6 or 30 isn't represented.

Therefore I think that the * (which I think means AND) would do the trick in the filter-function and include both search strings in the result?

[georgiboy]

When I opened the file you have just uploaded the searches were set to 'm6' & '20'

The 'Normbetegnelse2' value for:
14501 = 1220x1072 & 1220x660
16052 = NCS 1020 R 90 B

As it is searching both search strings for both columns it has found the 20 within the Normbetegnelse2 values, is this not the desired outcome?

If you change your search to say 'X20' or 'X 20' then those results will not show - this is the nature of a wildcard search.

You can also swap the + for a * and see how that changes the result.

[c_skytte]

When I opened the file you have just uploaded the searches were set to 'm6' & '20'

The 'Normbetegnelse2' value for:
14501 = 1220x1072 & 1220x660
16052 = NCS 1020 R 90 B



I know... but M6 needs to be there too.

If I change + to * it just returns #CALCULATE

[georgiboy]

Like on the attached?

[c_skytte]

EXACTLY! Bullseye! :clap:
Thank you!

[georgiboy]

Happy to help, glad we got there in the end :thumb