< and > than off of a filter function

[Lancer7]

Hello, I have a table built with the formula =FILTER(A3:I3000,ISNUMBER(SEARCH(Q3,B3:B3000)),""). This formula looks at A Column for a keyword in a big table and brings me back a smaller table based on that keyword. In this smaller chart there is one column of numbers and I am trying in another cell to make a formula =IF(AND(T5:T3000<=2000,T5:T3000>=1500),AH5:AH3000/AD5:3000,""). I understand that this formula might have multiple issues but I'm not a master and kind of just work through them until they work. But my problem right now is I believe its not reading the column T5:T3000 as a number. Even when I simplify the formula to IF(AND(T5<9999999),AH5/AD5,"") It comes back as false and leaves a blank cell even thou the number is only 1700. As far as I can see the cells are formatted as numbers so Im wondering if its not working because Im trying to pull a number off a =FILTER formula

 

[Peter_SSs]

for my preference it would maybe be easier to convert the column of original data as I will be using it for many other tables as well.

Change the formula in column D from

=TEXTJOIN("",TRUE,IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))

to

=CONCAT(IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))*1

 

[KRice]

I believe it's the D column, Peter...referencing the text in C. I was going to suggest...

=TEXTJOIN("",TRUE,IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))+0

to coerce a number from the text, but your CONCAT offering is shorter.

 

[Peter_SSs]

I believe it's the D column, Peter...referencing the text in C.

Yes, thanks for that. I've fixed in my previous post now.

I was going to suggest...

=TEXTJOIN("",TRUE,IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))+0

to coerce a number from the text, but your CONCAT offering is shorter.

I had considered making a similar small change to the OP's formula, but TEXTJOIN("",TRUE,... is exactly the sort of thing CONCAT is for.

 

[Lancer7]

Yes! That is it, I'm not really sure why the TEXTJOIN didn't work I thought it was to separate numbers and text, And not really sure what the CONCAT does but I'm going to do some googling so I know for the future, Thank you!

Change the formula in column D from

=TEXTJOIN("",TRUE,IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))

to

=CONCAT(IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))*1

 

[Lancer7]

I believe it's the D column, Peter...referencing the text in C. I was going to suggest...

=TEXTJOIN("",TRUE,IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))+0

to coerce a number from the text, but your CONCAT offering is shorter.

Thanks for the help!

 

[KRice]

I'm not really sure why the TEXTJOIN didn't work

TEXTJOIN would work, but you need one minor adjustment. TEXTJOIN returns a text string, even if the text string appears to be a number. So to coerce the text that appears to be a number into an actual number, the easy thing to do is to perform a simple mathematical operation on it. I suggested adding a 0...which will then return a numeric value. Peter's CONCAT formula also returns text, and in that version, the text is multiplied by 1 (you could also add 0)...and again, you'll get a numeric value.

 

[Lancer7]

TEXTJOIN would work, but you need one minor adjustment. TEXTJOIN returns a text string, even if the text string appears to be a number. So to coerce the text that appears to be a number into an actual number, the easy thing to do is to perform a simple mathematical operation on it. I suggested adding a 0...which will then return a numeric value. Peter's CONCAT formula also returns text, and in that version, the text is multiplied by 1 (you could also add 0)...and again, you'll get a numeric value

Ok perfect good to know!

 

[Peter_SSs]

@Lancer7
The TEXTJOIN (or CONCAT) are text functions so they return text values not numerical values. There is a hint that this has happens as you can see your values in col D are left-aligned which is the default for text values while numerical values by default are right-aligned.

Those text values that look like numerical values can be converted into actual numbers by adding zero (+0) as Kirk did with your formula or multiplying by 1 (*1) as I did with my formula (& you were already doing this inside your TEXTJOIN function - see yellow highlight below - because MID is also a text function & your formula needed to try to convert those text digits to real digits) or by applying a double negative (--) like this

=--CONCAT(IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))

It looks like in your samples that you second filter is to look for a year value in column R (originally column B). I would say that your current filter formula might be a bit 'risky' because there are other digits in col B that could give you a wrong data line. For example if a col B value happened to be
"2020 VERSATILE RT20148 Combine"
then this line would show up in the results if Q3 was "RT20148" and AA3 was "2014"
It appears to me that if there is a year value in column B it comes right at the start? If that is the case then you could try this formula to do both filters at once

=FILTER(A3:I3000,SEARCH(AA3&"*"&Q3,B3:B3000&"_"&AA3&Q3)=1,"")

Or another possibility, still assuming that the year would be the first thing in col B, would be to put the year and model(?) information both into Q3 like this 2014*S680 and then use this slightly simpler filter formula

=FILTER(A3:I3000,SEARCH(Q3,B3:B3000&"_"&Q3)=1,"")

BTW, if we need to do more in this thread it would be a great help if you could give us any small sample data and/or formulas with XL2BB so we can easily copy for testing.

 

[Lancer7]

@Lancer7
The TEXTJOIN (or CONCAT) are text functions so they return text values not numerical values. There is a hint that this has happens as you can see your values in col D are left-aligned which is the default for text values while numerical values by default are right-aligned.

Those text values that look like numerical values can be converted into actual numbers by adding zero (+0) as Kirk did with your formula or multiplying by 1 (*1) as I did with my formula (& you were already doing this inside your TEXTJOIN function - see yellow highlight below - because MID is also a text function & your formula needed to try to convert those text digits to real digits) or by applying a double negative (--) like this

=--CONCAT(IFERROR(MID(C3,SEQUENCE(LEN(C3)),1)*1,""))

View attachment 108204

It looks like in your samples that you second filter is to look for a year value in column R (originally column B). I would say that your current filter formula might be a bit 'risky' because there are other digits in col B that could give you a wrong data line. For example if a col B value happened to be
"2020 VERSATILE RT20148 Combine"
then this line would show up in the results if Q3 was "RT20148" and AA3 was "2014"
It appears to me that if there is a year value in column B it comes right at the start? If that is the case then you could try this formula to do both filters at once

=FILTER(A3:I3000,SEARCH(AA3&"*"&Q3,B3:B3000&"_"&AA3&Q3)=1,"")

Or another possibility, still assuming that the year would be the first thing in col B, would be to put the year and model(?) information both into Q3 like this 2014*S680 and then use this slightly simpler filter formula

=FILTER(A3:I3000,SEARCH(Q3,B3:B3000&"_"&Q3)=1,"")

BTW, if we need to do more in this thread it would be a great help if you could give us any small sample data and/or formulas with XL2BB so we can easily copy for testing.

Sounds good! I tried using that XL2BB once and gave up haha. But I will get it figured out as it makes it way easier for everyone helping. I will take a look at those formulas tommorow after work hopefully! thanks again for now

 

[Peter_SSs]

I tried using that XL2BB once and gave up haha.

If you still have problems with XL2BB, check the suggestions in this thread.

I will take a look at those formulas tommorow

Cheers.