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
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
< and > than off of a filter function
- Thread starter Lancer7
- Start date
@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
Excel Formula:=--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
Excel Formula:=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
Excel 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.
| RB prices.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 2 | 423 | 1996 TRAILTECH Combine Trailer | Details: Spring Suspension, 215/75R17.5 Tires | 21575175.00 | https://www.rbauction.com//equipment_images/2023263/large/14233278_1.jpg | Tuesday, November 28, 2023 (Day 1 of 2) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAHgAAAArCAYAAACzfkyLAAABZElEQVR42u2byxLFIAhD8/8/7V13dadKSKjJTJfFxymIWIEoiqIoiqIoiqIoiqIo+qrWn4dtf/fpbNt9jssGHsDcsUvh7nbiVsCSaKEY8I2AJRHjrcEArh8va463DbUmBi/7sPOuMmGlzjHDC5SAYQj4BNQx5KmAq8Ola6TBjYAZyY6j95bYqA4fCOA5gNdHvdcBcKudt9uB6XC7AHftn8v2hNMTq5OPejTg043/NLiAvmolsXULXMCvctVpK4AbyqFjPFiRhSrLiq1VKJc1mDXxyjJoRSJmAbjidIMFQA0YNwHuhuwA93Ry5YAZawszAcIwyJaAO0LXJO8dC9htn+fsvYwwTZ8XiyL4ELjqdXgFcADbA14fBQyI/uhg11vjvdyPYyk77u69it2CJAvfLVo4H6vttAfDcbb8F62+yqEqPzrdSaLfbFDe03EFrGp/ZMOq7Fl9YiY9qXMZqEOW7Hzr4aEfL2/VcRqTQnUAAAAASUVORK5CYII= | 8750 | 6419 | 2014*S680 | ||||||||||||
| 3 | 46 | 2014 JOHN DEERE S680 Combine | Meter Reads: 1,731 Hr* | 1731.00 | https://www.rbauction.com//equipment_images/2023510/large/13492158_1.jpg | Wednesday, April 5, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAAByUlEQVR42u2cQXIEIQwD+f+nySXX3UoAWxK0qvY4GI8b4wWGMRBCCCGEEEIIIYQQQgghhBBCaEfz91fZ9qdft70psNfRp+73rLb7pw4oA60A6gagFT7L7CYGOC07KvvgOhsct5vm6Go/ZmCGPGU/1d8YoFfa3u3Lf55xy5JVM1Nliamy+7Uhxaitqumrg3J6UKn+A1XPhuXvujIrdmTapFWXTqB3B3/as7JVDsXod3kHDkBXP6+yazHlnq4LATq/bChNWAAN0AAtBHoC9NEBfwro4ZKwADoP6BuW3sri+zrQinVogL4E6AnQdjAAdEObDgdqRiPQE6BzgVZtSXe90I4tb4A2DOwODK4+n4IdoC/MVIp+dWnXR4AWBXgHWMfavhNqgDYO8kr2dV1OdFhhAWiTQHeejV61mwA1QIdlrvEIzAD9CNBJGzNuZUf1gH/uLIdDn1JgVtfRnLYLKTdSYAboB4BO+nxL7S9frJgD/VKpofaXbwoX2j3VjxkC82z0N7FsaIlR1xZzdXAdttlPBPbWpbeWezkqA518a5LyKrKKxMTNSSZAj8eArpxtudtOWAa4+DiDoFq1H2k3IdCOPirOhKevXh2z+wP3NvJ+AgjbKwAAAABJRU5ErkJggg== | 197500 | 143701 | |||||||||||||
| 4 | 47 | 2013 JOHN DEERE S680 Combine | Meter Reads: 2,064 Hr* | 2064.00 | https://www.rbauction.com//equipment_images/2023510/large/13492160_1.jpg | Wednesday, April 5, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAABRklEQVR42u3cSxKDIBQFUfa/aTPJOJXE90NOVzmEi6aJSAFrAQAAAAAAAAAAAMAdrvcVVU/0ldGW7GcpNzf3qwY8QeiKziK3Kbc6tFvoyjeA3MLc3W40ol0dwxq5RbmnCf1L2cj7l1uT+7Gi1gH8jXZHfhNkD2/kBrv2qdBuQmd94Co7v2z7LEelzCvxX0fujNxthe7q+YYNc3MJTSxCTxA6epou+0NUbk3ulkJnzDmbAts/l9DEInS30FPGZMQidKvMxCI0oYlF6KkyE4vQY4SuWLxPLEITmliE7pKZWIRuF/oitNynC70ILXdHoat3x2TXI/fgtRyZOVa9WW1HaGIRenKOnSN2rLQLPaXTKGtP4VbDGq/vZ+QeJ7QpsIPP5eg+Xy4TJxg5Oan8fLk1VGq5w3NPFXo1/LhyC3JPFrq7HXL/zH0B0u8dYrL9qWwAAAAASUVORK5CYII= | 170000 | 176463 | 46 | 2014 JOHN DEERE S680 Combine | Meter Reads: 1,731 Hr* | 1731 | https://www.rbauction.com//equipment_images/2023510/large/13492158_1.jpg | Wednesday, April 5, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAAByUlEQVR42u2cQXIEIQwD+f+nySXX3UoAWxK0qvY4GI8b4wWGMRBCCCGEEEIIIYQQQgghhBBCaEfz91fZ9qdft70psNfRp+73rLb7pw4oA60A6gagFT7L7CYGOC07KvvgOhsct5vm6Go/ZmCGPGU/1d8YoFfa3u3Lf55xy5JVM1Nliamy+7Uhxaitqumrg3J6UKn+A1XPhuXvujIrdmTapFWXTqB3B3/as7JVDsXod3kHDkBXP6+yazHlnq4LATq/bChNWAAN0AAtBHoC9NEBfwro4ZKwADoP6BuW3sri+zrQinVogL4E6AnQdjAAdEObDgdqRiPQE6BzgVZtSXe90I4tb4A2DOwODK4+n4IdoC/MVIp+dWnXR4AWBXgHWMfavhNqgDYO8kr2dV1OdFhhAWiTQHeejV61mwA1QIdlrvEIzAD9CNBJGzNuZUf1gH/uLIdDn1JgVtfRnLYLKTdSYAboB4BO+nxL7S9frJgD/VKpofaXbwoX2j3VjxkC82z0N7FsaIlR1xZzdXAdttlPBPbWpbeWezkqA518a5LyKrKKxMTNSSZAj8eArpxtudtOWAa4+DiDoFq1H2k3IdCOPirOhKevXh2z+wP3NvJ+AgjbKwAAAABJRU5ErkJggg== | 197500 | 143701 | ||||
| 5 | 61 | CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine | Meter Reads: 60 Hr* | 60.00 | https://www.rbauction.com//equipment_images/2023597/large/13875951_1.jpg | Thursday, June 22, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAFoAAAArCAYAAAD41p9mAAABCElEQVR42u2aaxLDIAiEvf+l6QnaqRH2YXZn/BmUL0GRsFYURVEURVEURVEUdah+jG57O2PSr1KC7Aoa/UJbFuQGmhU9xwtyAs3eql4BescODDY6fBUP83HYdRnok7WPwf5m8EbQqOf/huwMugZB1xpwPqCbfK+XgabYYaQ7CNCo/LsNMqvWcQ1oZk7JvgVSQTtU7+xAlynonTnpoKVuSIM1Fipoha8DdWDSQEvdjppg24CeGCr5cEAbljkDOqC1t4/RWkdAGyQC7N6HgBYHvZbgHxZV0MyreDmD7grb6fCXa6Y5tTUN+YkPtAhW6hZC9txJ9eC5OSsLWakejS7Fwtt3b+1Rbpv3Ayzzj6mcaH2pAAAAAElFTkSuQmCC | 450 | 247 | 11484 | 2014 VERSATILE RT490 4x4 Combine | Meter Reads: 344 Hr* | 344 | https://www.rbauction.com//equipment_images/2023219/large/13843414_1.jpg | Thursday, April 27, 2023 (Day 4 of 5) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAAArCAYAAACaVXFJAAABV0lEQVR42u2cWxLDIAwDff9LuzfoNI0tW7Ca4TNRoRsD5hGBEEIIIYQQQgghhBBCCCG98oey7Xcp6j3V3ra++bJsgF1Zf+UHbOubhcUxgk7V9XjfXA5Xp+9UXa/wffLwSWA9eRe+f/hmw589MRbongRUeN/mazlDUzYyzwrhUg8wu7/e7udv810H1rf3vfWkO9NG+NVgBWABVme06gSru+63+a6KVt3TYFIGGl87qADrQLDU63QBWGeBNbF8U5X5B6xDwOrqVgGLiFW+AByABVjVuZEArDtnhVVwOa7zAZYol/UGrhQVwDIEKwAL3wmwErAASx21psCKYK1w/VrhaWCxu2FRtJowZdsMYK31Ywep8Q5SR7DYt17c3mkCVWWEpDsT+HYMvB26XlIGgnOFDsfsO8Z0nIQWnoTefDkIdzeY+TrcNtM5C+W2GYHvNpiU6Y1Tuni57weVthZb0MXS1gAAAABJRU5ErkJggg== | 24000 | 25119 | ||||
| 6 | 5029 | 2012 MASSEY FERGUSON 9560 Combine | Meter Reads: 2,257 Hr* | 2257.00 | https://www.rbauction.com//equipment_images/2022155/large/13303309_1.jpg | Tuesday, July 19, 2022 (Day 1 of 2) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAABh0lEQVR42u2cSRLDIAwE9f9Pk0uuSZyypRaop4ojiAltNseKUEoppZRSSimllFJKKaWUUkqpO1rvktn2p0J5zeoL5Xda3LIBvWqUADuzD5TfUXF3MVsBdWZ8yu+YuCRQ62ahYF6b+R0Vt+OTS219MoGm/E6L+7UhYg9J7uezHmjK77S4P28tqpch8pCatUJRfqfVxW45sgB52mf2bJVdf1pcBGh6xvsH5idiuW3gx3c80CHQAl29nGS2UwF0tt9pcRGgu7WVfZ3k1VsPVkYAfbWeQAt0+7aq9oQCLdAI0CHQAr0r0EugBfoUoHd+6yXQAn0bZoEW6G0GOARaoE8COqMItEALtEALtEAbtwXQEcy7fgpoyu/EuO2AzjyodQTaf9sBs/MpQJP+BfpwoCPRcDQEmvTrFysw0C2+OWs241h3A6DJ5Yzy77ahyfhOzMvh1duheTkqMt50zJxEHYrNnARnTqpYjjsnbKz+vTO9mNuucHki0+qafXTDuB2ApvfKVH9OOBsgcV+FpaHdNMa9fwAAAABJRU5ErkJggg== | 104000 | 80070 | 664 | 2014 VERSATILE RT490 RWA Combine | Meter Reads: 301 Hr* | 301 | https://www.rbauction.com//equipment_images/2022110/large/12988327_1.jpg | Wednesday, March 2, 2022 (Day 2 of 3) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAAArCAYAAACaVXFJAAABZElEQVR42u2cURKEIAxDvf+l2RPs7ghNGuBlxk+IlSdgAZ8HIYQQQgghhBBCCCGEEEJ5Gj8udf2jKS58G6Ea4rpVgePbDJjqJsbi5XzIVQ/7Nt/lG3JDtSPQt/nawZqpy+GtnFPe5ltGumOutuo/U14d84m+LRM9FRxKX8oWwfWtQiVYajC77vs239dQdQ1FFUEznPlHlr8VPoAFWKtgjc3BGuJuvaqBT/WdgmpXsEgZeHynoQIsfF/V5Ry7O1IWgBUClrrHccMJWA1gjTCwxmJZGjgArM6MbPVaIQ0cAlby5Lt6JR6wTGClJM1WwKGBNwHL0XNUAEYDA5Y0y0sDA5ZEHRsEAetwsCoXZZVAX+cLWOxucH+oZZkVvo2ABVgyf3aQNu8gTQWrc2cFZTcBy7EExHAWekpHDVbVwjMpg0zfCLA4kXzhSehUsJSx4Gv+2k9ZhHbEw99mAnJI6j+/7BrX8b4fTXxpCFRJq3oAAAAASUVORK5CYII= | 49000 | 28411 | ||||
| 7 | 51 | 1997 CASE IH 2188 Combine | Meter Reads: 4,883 Hr* | 4883.00 | https://www.rbauction.com//equipment_images/2023602/large/13891193_1.jpg | Thursday, July 13, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAAArCAYAAACaVXFJAAABL0lEQVR42u3cwRKDIAxFUf7/p9tN121VSAI5d8alvOjcAVRwDAAAAAAAAAAAUJPX55jZ1tNjdR2r72WX3L8KOEGsaIFb5maEZopVtXc8JjfrQjPF2qWH3Dq3m1hX2omS+cTcrw1Fi1VxzjjjHnTL/fmUd5JYT67FuYET+lPEWn1+t9xWYhnOYnKJRSxiVRgG77bTLbeVWF4ZxOSWFiv6jTOxGotV6eYQ62CxXsQi1iqxrtRArIZizZCOWMRKmeATi1i35SIWscJrIRaxltRDLGIRi1j7iDWGb4XtvhXuNM+yuoFYxCJWXD1WkDZaQRpZi3XrTda8z+qWDWd1n6LTxHp6oV4Z1M2dtml05tBkR3LjndDZYq3sjeUm/rth9RzAX182z80Ua+Vcaoen3+1z3z6mocFIUFAUAAAAAElFTkSuQmCC | 15000 | 11375 | 35 | 2014 CLAAS LEXION 780 Combine | Meter Reads: 3,497 Hr* | 3497 | https://www.rbauction.com//equipment_images/2023259/large/14273223_1.jpg | Wednesday, December 6, 2023 (Day 1 of 3) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAABV0lEQVR42u3cwXLDIAxFUf7/p91NN920TSIkIc6d8dJ+GF8GUGKvBQAAAAAAAAAAAACf8HwfJ+c+Qcfuduzuy1ty/9WAk3Orhc4eOFfm3nSzlUJ3nQ3G5N7WwZVCnzIjHJ1L6Jy2vHKdrEE0MffXC1UIXbGG7rgXieiD23L/rB5kCN1hU9h1g+vcxI3ipCpHxb3sPv+2XEIX110tGwqePaEJTWhCh0+fUWJNzSV0wr0oveXkEnrV19kJTejyH1YITehRQj+EJvQ0oV9pA6EJXZ67AqUmNKFbCB21cSQ0oVsJvQhN6GlCL0ITeprQ77aH0IQmNKEJfeKyIypfLqGPW0f7tx2hCU1oQu9qjzdWvLEyomy3czA4l9A/rh3RDsuGvlWh64T+tIOV3vrmhn10JXIq7p67Y6DKLf5y0q1C75x95BZ+2+5UoSsf7rv5cgmdvvGzR0nM/QIMxdWbuPllDQAAAABJRU5ErkJggg== | 115000 | 84939 | ||||
| 8 | 50 | 1997 CASE IH 2188 Combine | Meter Reads: 5,246 Hr* | 5246.00 | https://www.rbauction.com//equipment_images/2023602/large/13891174_1.jpg | Thursday, July 13, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAAArCAYAAACaVXFJAAABY0lEQVR42u2bQRLDIAwD+f+n3UvPbZNiSyKrmRyDICzEGFgLIYQQQgghhBBCCCGEEEKeqvfTUeanZ9pvok6TbXbw/akCyo5NB2u6vVJf14/qXIdJb9VgijEsA7hU3qkDKRIsReylAOtKOVMwt4Y+nwrqAksdbE4Hq3fqvqO9Kt+vqzzpymFwFLvWO/Fd6aoQsPrfV/kCljhfdOJvFLAAC7A6pmY1WN3lqHzjwKpAsE5IGbT1vwNYqoQhYB0IluP2BmAFgZWyGdudaQcsIVjLAKwrdQGsoBnL4TesyF4DluHMoQrwAct0VZgAF2AdnMdy3UQGrACwnGdPwDoYrAIswDotYXvnvV3+br6AFRRncboBsAALsOZ8OUFK8N7yDTjzPmSgvqVTAt9H3tJRgLWjkbtOHUzNkNwrXL17eOqb0MlX+6N8k8FK6tx//eN8SzCLPLVz7/pH+pb496Q+xZC4wW3j+wIUfEoZ21/7fQAAAABJRU5ErkJggg== | 13500 | 10237 | 36 | 2014 CLAAS LEXION 780 Combine | Meter Reads: 3,454 Hr* | 3454 | https://www.rbauction.com//equipment_images/2023259/large/14273228_1.jpg | Wednesday, December 6, 2023 (Day 1 of 3) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAABV0lEQVR42u3cSxKDIBBFUfa/aTLJ2EqEbj59bpVDfBCvCERpDQAAAAAAAAAAAABG6N/jhtz+w5HRpuzsark/VeDE3D54ZN5IcjU2VObVN1WTq6FRQvfDnhClcqsKnT22/udccgeu99OJVgjdAoVeNYl5cw65L6/5U6EMoXeajEbUZaS8sokTxRtXVzKFbsHlq+USevMe1nAl4NoTmtCELiJ05uNzlli35hL6kN7Zkt/k619Z6JWrG4QmdHhuX9gGQhN6SNzov7kJTehlQjdCE/q2HtqQg9BXCm1SSOjjVjlmSU1oQm+1bDe66kFoQm8ndCM0oSsJ3QlN6NOEjuilZ+XLJfRxww5v2xGa0IQmdFR9fLHii5UrJoWRN4OyhPbVd4HcckLP+HEz3u2w5BewL8fKdx0icu2cZOckQgc8nextt+nedqcJ3RaLPFIPuYSeln/jas7xuR9hD+2DsDaf0QAAAABJRU5ErkJggg== | 113000 | 832462 | ||||
| 9 | 44 | WHITE 5542 Combine | Details: Pickup S/N - 8122128NP, White 10 ft Draper Pickup, 9 ft Auger, Chopper, 18.4-26 … | 812212810918426.00 | https://www.rbauction.com//equipment_images/2023647/large/14162497_1.jpg | Friday, October 13, 2023 | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAFoAAAArCAYAAAD41p9mAAAA8UlEQVR42u2awQ6FIAwE+f+fxuQd38UI3S6ls4k3dWAwCpUxCCGEEEIIIYSQlcyXozrPyp2LRybT1Ver4N2GOAbWxp0m0Y6BtXF3brrTgC/MrMGVcSNHboo6G9lpF/e4L/yN11okD+FTeST3NMm3vnYQ3VW0+j4ubtmnudLU8DPY0sguohWrJET/naxaliI6qdaB6ID3ddpS9jbRivoIohfgiBZM76IGC9EJ9WREI1q76HKIptZxgOh21TtEH1gmLfWnw8hNn0d3vXZZduUFT5m/4NHbE9rs64is5jnYoxjXBzYxndwlOFxxA1SpvA3ilwcdJyEma/xtWwAAAABJRU5ErkJggg== | 600 | 48 | 282 | 2014 CLAAS LEXION 670 Combine | Meter Reads: 2,100 Hr* | 2100 | https://www.rbauction.com//equipment_images/2023257/large/14004815_1.jpg | Tuesday, July 18, 2023 (Day 1 of 2) | data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALQAAAArCAYAAADR/aKkAAABjUlEQVR42u2cQRLCMAwD8/9PlwtnoESW7GY102Mjm2zAdRPWQgghhBBCCCGEEEIIIYQQQgihHV3vq2LMT5czN1cMqZxP8/0pgGqAu0DVIf9UrqN9XaaX6FrBRbUTw7R8x/m6Da8GUKf8p+Y7yjedpLu23hlvx/uOpyvfJ/p+HKgK6FQtf4nHqoxfEedpvl+7FumugxrEbk/23Gueny5AK2JJ5VK1CPE9GOhuMFM2hOYKoAEaoAFa/vOpAuupvgA95NuZlp94ziZ0OdT3dpwUgAboW/dWvFACaICOtOuq3pQCNEDbn5or9xoANECXeO/AuQM1QAO0/cNRbJACaIC2eioe8gAaoNv4OepwgAbosv5vohYHaIC2wAzQAG0FetKCUdbR1fGzlyMA3LRyhl1v7LaLlRkADdDWwSe2Ajk5womVFq1AxQfF2T7OFLaCeQfqyS9mjjz13QVoxx+SqPdy0Hrr52sFLA30CnmqvBe+AN1pglfI8yjfE4H+Nx5HKYdvaAU9AeiKWnlyx2eM7wtM/2IdfMTsdQAAAABJRU5ErkJggg== | 1276000 | 95483 | ||||
Combine | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L4:T62 | L4 | =FILTER(A2:I3000,SEARCH(L2,B2:B3000&"_"&L2)=1,"") |
| D2:D9 | D2 | =CONCAT(IFERROR(MID(C2,SEQUENCE(LEN(C2)),1)*1,""))*1 |
| Dynamic array formulas. | ||
Upvote
0
Excel Facts
How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
It took a hour but I got it figured out haha, So I tried the new formula you suggested with the 2014*S680 but it still appears to only be sorting by the year not the model?
Upvote
0
The formula seems to be working fine. It is filtering the source data to return those rows where the year specified appears first in the B column and the specified model (S680) appears anywhere else in that same cell. Unfortunately, not all of the B column entries begin with a year, so you'll never see them.
Edit: What were your plans for finding this entry:
CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine
...what would you search for?
Edit: What were your plans for finding this entry:
CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine
...what would you search for?
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
I'm not sure what you are saying. My suggested formula with the 2014*S680 was not attempting to sort anything. It was showing how you could get the same result as your two original FILTER formulas in a single formula.
BTW, when using XL2BB you can hide irrelevant columns to make the mini sheet smaller and easier to follow in the forum, as I have done below.
Back to my first point. In the mini sheet below
I have used your original filter formula in cell L9 (except I changed Q3 to L1 since that is where I put the S680 that you originally had in Q3.
In cell V9 I have used the formula that you originally had in AA5 (again modified to suit the new ranges).
In cell L5 I used my single formula to do both filters and you see that the result in L5 and right is the same as the result in V9 and right.
| Lancer7.xlsm | |||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | K | L | M | U | V | W | ||||||||||||||||||
| 1 | S680 | 2014 | |||||||||||||||||||||||
| 2 | 423 | 1996 TRAILTECH Combine Trailer | 2014*S680 | ||||||||||||||||||||||
| 3 | 46 | 2014 JOHN DEERE S680 Combine | |||||||||||||||||||||||
| 4 | 47 | 2013 JOHN DEERE S680 Combine | 46 | 2014 JOHN DEERE S680 Combine | |||||||||||||||||||||
| 5 | 61 | CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine | |||||||||||||||||||||||
| 6 | 5029 | 2012 MASSEY FERGUSON 9560 Combine | |||||||||||||||||||||||
| 7 | 51 | 1997 CASE IH 2188 Combine | |||||||||||||||||||||||
| 8 | 50 | 1997 CASE IH 2188 Combine | |||||||||||||||||||||||
| 9 | 44 | WHITE 5542 Combine | 46 | 2014 JOHN DEERE S680 Combine | 46 | 2014 JOHN DEERE S680 Combine | |||||||||||||||||||
| 10 | 47 | 2013 JOHN DEERE S680 Combine | |||||||||||||||||||||||
| 11 | |||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L4:T4 | L4 | =FILTER(A2:I3000,SEARCH(L2,B2:B3000&"_"&L2)=1,"") |
| L9:T10 | L9 | =FILTER(A3:I3000,ISNUMBER(SEARCH(L1,B3:B3000)),"") |
| V9:AD9 | V9 | =FILTER(L9:T20,ISNUMBER(SEARCH(M1,M9:M20)),"") |
| Dynamic array formulas. | ||
Upvote
0
I Like what your formula is doing it makes it way easier combining it all into one filter, My problem and what I was meaning by "sorting" was actually "filtering" So when I copy it onto my spreadsheet it is still filtering all by "2014" not by 2014 AND S680. So in that mini spreadsheet you just sent in L4 your new formula you made, Is bringing back in M4 " 2014 John Deere S680 combine" but on mine it is bringing that back as well, but it also brings back other 2014s for example "2014 New holland CR890 combine"
Upvote
0
| RB prices.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | L | M | ||||||||||||
| 1 | Title | |||||||||||||
| 2 | 1996 TRAILTECH Combine Trailer | 2014*S680 | ||||||||||||
| 3 | 2014 JOHN DEERE S680 Combine | |||||||||||||
| 4 | 2013 JOHN DEERE S680 Combine | 46 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 5 | CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine | 11484 | 2014 VERSATILE RT490 4x4 Combine | |||||||||||
| 6 | 2012 MASSEY FERGUSON 9560 Combine | 664 | 2014 VERSATILE RT490 RWA Combine | |||||||||||
| 7 | 1997 CASE IH 2188 Combine | 35 | 2014 CLAAS LEXION 780 Combine | |||||||||||
| 8 | 1997 CASE IH 2188 Combine | 36 | 2014 CLAAS LEXION 780 Combine | |||||||||||
| 9 | WHITE 5542 Combine | 282 | 2014 CLAAS LEXION 670 Combine | |||||||||||
| 10 | 2012 VERSATILE RT490 Combine | 16 | 2014 JOHN DEERE S690 Combine | |||||||||||
| 11 | 1991 GLEANER R70 Combine | 15 | 2014 JOHN DEERE S690 Combine | |||||||||||
| 12 | GLEANER R60 Combine | 24 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 13 | 2011 GLEANER A86 Combine | 17 | 2014 JOHN DEERE S690 Combine | |||||||||||
| 14 | 2012 VERSATILE RT490 Combine | 25 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 15 | 1998 GLEANER C62 Combine | 158 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 16 | 2011 GLEANER A86 Combine | 156 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 17 | 2007 GLEANER A85 Combine | 157 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 18 | 2012 VERSATILE RT490 Combine | 91 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 19 | 2004 GLEANER R75 Combine | 92 | 2014 JOHN DEERE S680 STS Combine | |||||||||||
| 20 | 2006 GLEANER R65 Combine | 143 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 21 | 1995 GLEANER R62 Combine | 144 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 22 | 1984 GLEANER N7 Combine | 60 | 2014 JOHN DEERE S690 Combine | |||||||||||
| 23 | GLEANER C62 Combine | 292 | 2014 NEW HOLLAND CX8090 Combine | |||||||||||
| 24 | GLEANER K Combine | 38 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 25 | GLEANER M2 Combine | 31 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 26 | 2011 GLEANER S67 Combine | 27 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 27 | 2021 FENDT IDEAL 8 Combine | 28 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 28 | 2021 FENDT IDEAL 8 Combine | 152 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 29 | 2019 FENDT IDEAL 9 Combine | 153 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 30 | 2008 LEXION 570R 4x4 Combine | 162 | 2014 MASSEY FERGUSON 9560 Combine | |||||||||||
| 31 | 2019 FENDT IDEAL 8 Combine | 62 | 2014 JOHN DEERE S690 Combine | |||||||||||
| 32 | 2004 CHALLENGER 670 Combine | 31 | 2014 JOHN DEERE S690 4x4 Combine | |||||||||||
| 33 | 2017 VERSATILE RT490 4x4 Combine | 5021 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 34 | 2014 VERSATILE RT490 4x4 Combine | 5023 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 35 | 2019 FENDT IDEAL 9 Combine | 5025 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 36 | 2019 FENDT IDEAL 9 Combine | 5024 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||
| 37 | 2019 FENDT IDEAL 9 Combine | 85 | 2014 MASSEY FERGUSON 9560 Combine | |||||||||||
| 38 | 2019 FENDT IDEAL 9 Combine | 14 | 2014 NEW HOLLAND CX8080 Combine | |||||||||||
| 39 | 2019 FENDT IDEAL 9 Combine | 38 | 2014 JOHN DEERE S680 Combine | |||||||||||
| 40 | OLIVER 40 FOR PARTS ONLY Combine | 41 | 2014 JOHN DEERE S680 4x4 Combine | |||||||||||
| 41 | VERSATILE 420 FOR PARTS ONLY Combine | 40 | 2014 JOHN DEERE S680 4x4 Combine | |||||||||||
| 42 | 1999 LEXION 465 4x4 Combine | 157 | 2014 JOHN DEERE S690 RWA Combine | |||||||||||
| 43 | 2014 VERSATILE RT490 RWA Combine | 158 | 2014 JOHN DEERE S690 RWA Combine | |||||||||||
| 44 | 2020 CLAAS LEXION 8700TT Combine | 39 | 2014 CASE IH 9230 Combine | |||||||||||
| 45 | 2020 CLAAS LEXION 670 Combine | 38 | 2014 CASE IH 9230 Combine | |||||||||||
| 46 | 2015 CASE 9240 Combine | 147 | 2014 CASE IH 8230 Combine | |||||||||||
| 47 | 2014 CLAAS LEXION 780 Combine | 148 | 2014 CASE IH 8230 Combine | |||||||||||
| 48 | 2014 CLAAS LEXION 780 Combine | 135 | 2014 CASE IH 7230 Combine | |||||||||||
| 49 | 2013 CLAAS LEXION 760 Combine | 136 | 2014 CASE IH 7230 Combine | |||||||||||
| 50 | 2015 CLAAS LEXION 760TT Combine | 73 | 2014 CASE IH 8230 Combine | |||||||||||
| 51 | 2015 CLAAS LEXION 760 Combine | 110 | 2014 CASE IH 8230 Combine | |||||||||||
| 52 | 2009 CLAAS LEXION 570 Combine | 94 | 2014 CASE IH 8230 Combine | |||||||||||
| 53 | 2018 CLAAS 670 LEXION Combine | 92 | 2014 CASE IH 9230 Combine | |||||||||||
| 54 | 2012 CLAAS 770 LEXION Combine | 74 | 2014 CASE IH 7130 Combine | |||||||||||
| 55 | 2019 CLAAS LEXION 8700 Combine | 73 | 2014 CASE IH 7130 Combine | |||||||||||
| 56 | 2019 CLAAS LEXION 760TT Combine | 5007 | 2014 MACDON FD75-S 35 ft Flex Draper Header | |||||||||||
| 57 | 2019 CLAAS LEXION 670 Combine | 47 | 2014 MACDON FD75-D 40 ft Flex Draper Header | |||||||||||
| 58 | 2018 CLAAS LEXION 670 Combine | 45 | 2014 NEW HOLLAND 880 CF 45 ft Flex Draper Header | |||||||||||
| 59 | 2015 CLAAS LEXION 670 Combine | 22 | 2014 HONEY BEE 4036 36 ft Draper Header | |||||||||||
| 60 | 2012 CLAAS 760TT LEXION Combine | 48 | 2014 JOHN DEERE 640FD 40 ft Flex Draper Header | |||||||||||
| 61 | 2011 CLAAS LEXION 670 Combine | 83 | 2014 JOHN DEERE 630D 30 ft Draper HydraFloat Header | |||||||||||
| 62 | 2011 CLAAS 770TT Combine | 82 | 2014 JOHN DEERE 630D 30 ft Draper HydraFloat Header | |||||||||||
| 63 | 2011 CLAAS LEXION 770TT Combine | |||||||||||||
| 64 | 2001 CASE 2388 4x4 Combine | |||||||||||||
| 65 | 2019 CLAAS LEXION 760 Combine | |||||||||||||
| 66 | 2017 CLAAS LEXION 780TT Combine | |||||||||||||
| 67 | 2015 CLAAS LEXION 780 TT Combine | |||||||||||||
| 68 | 2013 CLAAS LEXION 780 Combine | |||||||||||||
| 69 | 2011 CLAAS 760 Lexion Combine | |||||||||||||
| 70 | 2014 CLAAS LEXION 670 Combine | |||||||||||||
| 71 | 1953 MASSEY-HARRIS 90RT Antique Combine | |||||||||||||
| 72 | 2019 CLAAS 760 Combine | |||||||||||||
| 73 | 2016 CLAAS 760 Combine | |||||||||||||
| 74 | 2019 CLAAS LEXION 780 TT Combine | |||||||||||||
| 75 | MCCORMICK SUPER 91 Combine | |||||||||||||
| 76 | 2019 CLAAS LEXION 670TT Combine | |||||||||||||
| 77 | 2017 CLAAS LEXION 670 Combine | |||||||||||||
| 78 | 2006 BYT 40 ft One-Way High Cube Combine | |||||||||||||
| 79 | 2008 CLAAS LEXION 570 Combine | |||||||||||||
| 80 | 2020 CLAAS LEXION 8700TT 4x4 Combine | |||||||||||||
| 81 | 2018 CLAAS LEXION 760TT Combine | |||||||||||||
| 82 | 2018 CASE 8240 Combine | |||||||||||||
| 83 | 2020 CLAAS 8700TT LEXION RWA Combine | |||||||||||||
| 84 | 2010 CASE 7088 Combine | |||||||||||||
| 85 | 2018 CLAAS LEXION 780TT Combine | |||||||||||||
| 86 | 2012 CLAAS LEXION 750 Combine | |||||||||||||
| 87 | 2005 CLAAS LEXION 580R Combine | |||||||||||||
| 88 | 2009 CLAAS LEXION 570R Combine | |||||||||||||
| 89 | WHITE 8650 Pull Type (Inoperable) Combine | |||||||||||||
| 90 | 2016 CLAAS LEXION 780TT Combine | |||||||||||||
| 91 | 1976 COOP G-100 15 ft Discer Combine | |||||||||||||
| 92 | 2019 CLAAS LEXION 760 Combine | |||||||||||||
| 93 | 2017 CLAAS LEXION 780 TT Combine | |||||||||||||
| 94 | 2016 CLAAS 780 Lexion Combine | |||||||||||||
| 95 | 2017 CLAAS 760 Lexion Combine | |||||||||||||
| 96 | 2016 CLAAS 670 Lexion Combine | |||||||||||||
| 97 | 2020 CLAAS LEXION 670 Combine | |||||||||||||
| 98 | 2020 CLAAS LEXION 670 Combine | |||||||||||||
| 99 | 2017 CLAAS LEXION 760TT Combine | |||||||||||||
| 100 | 2011 CLAAS LEXION 760TT 4x4 Combine | |||||||||||||
| 101 | 2020 CLAAS 670 TT Combine | |||||||||||||
| 102 | 1986 CASE 1680 Combine | |||||||||||||
| 103 | 2015 CLAAS 780 TT Combine | |||||||||||||
| 104 | 2013 CLAAS 780 Combine | |||||||||||||
| 105 | 2010 CLAAS 760 Combine | |||||||||||||
| 106 | 2015 CLAAS LEXION 670TT Combine | |||||||||||||
| 107 | 2013 JOHN DEERE S680 4x4 Combine | |||||||||||||
| 108 | 2017 NEW HOLLAND CR8.90 Combine | |||||||||||||
| 109 | 2014 JOHN DEERE S690 Combine | |||||||||||||
| 110 | 2014 JOHN DEERE S690 Combine | |||||||||||||
| 111 | 2015 JOHN DEERE S680 Combine | |||||||||||||
| 112 | 2015 JOHN DEERE S680 Combine | |||||||||||||
| 113 | 2015 JOHN DEERE S680 Combine | |||||||||||||
| 114 | 2014 JOHN DEERE S680 Combine | |||||||||||||
| 115 | 2012 NEW HOLLAND CR9090 Combine | |||||||||||||
| 116 | 2013 NEW HOLLAND CR9090 Combine | |||||||||||||
| 117 | 2014 JOHN DEERE S690 Combine | |||||||||||||
| 118 | 2012 JOHN DEERE S680 Combine | |||||||||||||
| 119 | 2013 JOHN DEERE S670 Combine | |||||||||||||
| 120 | 2012 JOHN DEERE S690 Combine | |||||||||||||
| 121 | 2013 JOHN DEERE S680 Combine | |||||||||||||
| 122 | 2010 JOHN DEERE T670 Combine | |||||||||||||
| 123 | 2014 JOHN DEERE S680 Combine | |||||||||||||
| 124 | 2012 JOHN DEERE S680 Combine | |||||||||||||
| 125 | 2015 JOHN DEERE S680 Combine | |||||||||||||
| 126 | 2015 JOHN DEERE S680 Combine | |||||||||||||
| 127 | 2013 JOHN DEERE S680 Combine | |||||||||||||
| 128 | 2012 JOHN DEERE S680 Combine | |||||||||||||
| 129 | 2012 JOHN DEERE S680 Combine | |||||||||||||
| 130 | 2013 NEW HOLLAND CR8090 Combine | |||||||||||||
| 131 | 2009 JOHN DEERE 9770 STS Combine | |||||||||||||
| 132 | 2009 NEW HOLLAND CR9080 Combine | |||||||||||||
| 133 | 2012 NEW HOLLAND CX8080 Combine | |||||||||||||
| 134 | 2012 NEW HOLLAND CR9090 Combine | |||||||||||||
| 135 | 2009 JOHN DEERE T670 Combine | |||||||||||||
| 136 | 2010 NEW HOLLAND CR9070 Combine | |||||||||||||
| 137 | 2009 JOHN DEERE 9870 STS Combine | |||||||||||||
| 138 | 2011 JOHN DEERE 9870 STS Combine | |||||||||||||
| 139 | 2009 MASSEY FERGUSON 9795 Combine | |||||||||||||
| 140 | 2009 NEW HOLLAND CR9070 Combine | |||||||||||||
| 141 | 2006 JOHN DEERE 9860 STS Combine | |||||||||||||
| 142 | 2004 JOHN DEERE 9660 Combine | |||||||||||||
| 143 | 2002 MASSEY FERGUSON 8780XP Combine | |||||||||||||
| 144 | 1997 JOHN DEERE CTS Combine | |||||||||||||
| 145 | 2002 CAT LEXION 470 Combine | |||||||||||||
| 146 | 1995 JOHN DEERE 9600 Combine | |||||||||||||
| 147 | 1981 JOHN DEERE 7721 Pull-Type Combine | |||||||||||||
| 148 | 2017 JOHN DEERE S680 Combine | |||||||||||||
| 149 | 2017 JOHN DEERE S680 Combine | |||||||||||||
| 150 | 2017 JOHN DEERE S680 Combine | |||||||||||||
| 151 | 2013 JOHN DEERE S670 Combine | |||||||||||||
| 152 | 2012 JOHN DEERE S690 Combine | |||||||||||||
| 153 | 2013 NEW HOLLAND CR8090 Combine | |||||||||||||
| 154 | 2013 NEW HOLLAND CR8090 Combine | |||||||||||||
| 155 | 2011 NEW HOLLAND CR9080 Combine | |||||||||||||
| 156 | 2010 JOHN DEERE 9770 STS Combine | |||||||||||||
| 157 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||||
| 158 | 2012 NEW HOLLAND CR9090 Combine | |||||||||||||
| 159 | 2008 JOHN DEERE 9770 Combine | |||||||||||||
| 160 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||||
| 161 | 2009 NEW HOLLAND CR9070 Combine | |||||||||||||
| 162 | 2014 NEW HOLLAND CR8090 Combine | |||||||||||||
| 163 | 2011 NEW HOLLAND CR9060 Combine | |||||||||||||
| 164 | 2011 NEW HOLLAND CR9060 Combine | |||||||||||||
| 165 | 2004 JOHN DEERE 9760 STS Combine | |||||||||||||
| 166 | 2009 NEW HOLLAND CR9060 Combine | |||||||||||||
| 167 | 2006 NEW HOLLAND CR960 Combine | |||||||||||||
| 168 | 2003 NEW HOLLAND CR940 Combine | |||||||||||||
| 169 | 2014 JOHN DEERE S680 Combine | |||||||||||||
| 170 | 2013 JOHN DEERE S690 Combine | |||||||||||||
| 171 | 2014 JOHN DEERE S680 STS Combine | |||||||||||||
| 172 | 2009 NEW HOLLAND CR9070 Combine | |||||||||||||
| 173 | 2006 NEW HOLLAND CR960 Combine | |||||||||||||
| 174 | 2009 CAT LEXION 570R Combine | |||||||||||||
| 175 | 2013 JOHN DEERE S690 Combine | |||||||||||||
Combine | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L4:T62 | L4 | =FILTER(A2:I3000,SEARCH(L2,B2:B3000&"_"&L2)=1,"") |
| Dynamic array formulas. | ||
Upvote
0
I trimmed your example down to make it a little easier to follow. I think the issue is that SEARCH is recognizing 2014, but it isn't interpreting the wildcard (*) followed by S680. You could do something like this...splitting up the two search terms, but using only one formula that has two matching arrays formed within it. One uses the same idea presented by Peter, which finds where the text string begins with the 1st search term, and the other matching array looks anywhere within the text string for the 2nd search term:
| MrExcel_20240318.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | L | M | ||||||||||||
| 1 | Title | ||||||||||||||
| 2 | 1996 TRAILTECH Combine Trailer | 2014 | |||||||||||||
| 3 | 2014 JOHN DEERE S680 Combine | S680 | |||||||||||||
| 4 | 2013 JOHN DEERE S680 Combine | 0 | 2014 JOHN DEERE S680 Combine | ||||||||||||
| 5 | CASE IH SMALL GRAIN ROT Small Grains Combine Rotor Combine | 0 | 2014 JOHN DEERE S680 Combine | ||||||||||||
| 6 | 2012 MASSEY FERGUSON 9560 Combine | 0 | 2014 JOHN DEERE S680 Combine | ||||||||||||
| 7 | 1997 CASE IH 2188 Combine | 0 | 2014 JOHN DEERE S680 STS Combine | ||||||||||||
| 8 | 1997 CASE IH 2188 Combine | ||||||||||||||
| 9 | WHITE 5542 Combine | ||||||||||||||
| 10 | 2015 CLAAS LEXION 760 Combine | ||||||||||||||
| 11 | 2009 CLAAS LEXION 570 Combine | ||||||||||||||
| 12 | 2015 CLAAS LEXION 670 Combine | ||||||||||||||
| 13 | 2014 CLAAS LEXION 670 Combine | ||||||||||||||
| 14 | 2013 JOHN DEERE S680 4x4 Combine | ||||||||||||||
| 15 | 2017 NEW HOLLAND CR8.90 Combine | ||||||||||||||
| 16 | 2014 JOHN DEERE S690 Combine | ||||||||||||||
| 17 | 2014 JOHN DEERE S690 Combine | ||||||||||||||
| 18 | 2015 JOHN DEERE S680 Combine | ||||||||||||||
| 19 | 2015 JOHN DEERE S680 Combine | ||||||||||||||
| 20 | 2015 JOHN DEERE S680 Combine | ||||||||||||||
| 21 | 2014 JOHN DEERE S680 Combine | ||||||||||||||
| 22 | 2012 NEW HOLLAND CR9090 Combine | ||||||||||||||
| 23 | 2013 NEW HOLLAND CR9090 Combine | ||||||||||||||
| 24 | 2014 JOHN DEERE S690 Combine | ||||||||||||||
| 25 | 2012 JOHN DEERE S680 Combine | ||||||||||||||
| 26 | 2013 JOHN DEERE S670 Combine | ||||||||||||||
| 27 | 2012 JOHN DEERE S690 Combine | ||||||||||||||
| 28 | 2013 JOHN DEERE S680 Combine | ||||||||||||||
| 29 | 2010 JOHN DEERE T670 Combine | ||||||||||||||
| 30 | 2014 JOHN DEERE S680 Combine | ||||||||||||||
| 31 | 2012 JOHN DEERE S680 Combine | ||||||||||||||
| 32 | 2015 JOHN DEERE S680 Combine | ||||||||||||||
| 33 | 2012 NEW HOLLAND CX8080 Combine | ||||||||||||||
| 34 | 2012 JOHN DEERE S690 Combine | ||||||||||||||
| 35 | 2013 NEW HOLLAND CR8090 Combine | ||||||||||||||
| 36 | 2011 NEW HOLLAND CR9060 Combine | ||||||||||||||
| 37 | 2011 NEW HOLLAND CR9060 Combine | ||||||||||||||
| 38 | 2004 JOHN DEERE 9760 STS Combine | ||||||||||||||
| 39 | 2009 NEW HOLLAND CR9060 Combine | ||||||||||||||
| 40 | 2013 JOHN DEERE S690 Combine | ||||||||||||||
| 41 | 2014 JOHN DEERE S680 STS Combine | ||||||||||||||
| 42 | 2009 NEW HOLLAND CR9070 Combine | ||||||||||||||
| 43 | 2006 NEW HOLLAND CR960 Combine | ||||||||||||||
Sheet5 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L4:T7 | L4 | =FILTER(A2:I3000,(SEARCH(L2,B2:B3000&"_"&L2)=1)*ISNUMBER(SEARCH(L3,B2:B3000)),"") |
| Dynamic array formulas. | ||
Upvote
0
| MrExcel_20240318.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Sheet5 | |||||||||||||||
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,372
- Office Version
- 365
- Platform
- Windows
Yes, SEARCH is recognising the wildcard followed by S680 but it was my mistake by ensuring that was found in every row by including &"_"&L2 at the end.
With 2014*S680 in L2 (& nothing in L3), try this in L4 instead
Excel Formula:
=FILTER(A2:I3000,IFERROR(SEARCH(L2,B2:B3000),0)=1,"")
Upvote
0
hmmm...I see your point, Peter...the revised formula works. I had to think a bit about this behavior. By concatenating L2 to the column B entries, you're assured of finding the search terms, so no error trap was needed. And if the equipment text string begins with the 1st search term (year) we should get a 1, provided the 2nd search term is also found somewhere in the string...and this is the problem. That 2nd search term may not exist in the original string, but it will still be found at the end of the appended string because of the &"_"&L2 term...so some false positives were found.
Upvote
0
Similar threads
- Question
- Question
