evaluating duplicate inputs

Question

Hello,&nbsp;I have a database of faults itemised by codes and vehicle numbers. Column C is my vehicle number, Column D is a location reference number and Column G is a Fault description. My formula works to highlight a fault position on the vehicle which has a fault present. Typing the vehicle number into I4 matches with a vehicle Number in column C. Each position has the same formula but with a different location reference range (eg 818100-818131 or 821100-821131). However if there are multiple faults recorded on the same vehicle, this formula will not look past the first instance of matching with the vehicle number and will return a "false" reading when the location reference is out of range. Is there a way to force the formula to evaluate further down column C to find the next instances of faults on the same vehicle in different positions? An example of my formula from one of the fault positions is listed below.&nbsp;"=IFERROR(IF(AND(I4=INDEX('Restriction data'!C:C,MATCH(I4,'Restriction data'!C:C,0)),AND(INDEX('Restriction data'!D:D,MATCH(I4,'Restriction data'!C:C,0))&gt;=818100,INDEX('Restriction data'!D:D,MATCH(I4,'Restriction data'!C:C,0))&lt;=818131),ISNUMBER(SEARCH("SST LOOU",INDEX('Restriction data'!G:G,MATCH(I4,'Restriction data'!C:C, 0))))),"R-SST","SST"),"SST")"

oliverscheurich · Answer

JoeEason92&nbsp;=IFERROR(SMALL(IF(('Restriction data'!$C:$C=Tabelle1!$I$5)*('Restriction data'!$D:$D&gt;=Tabelle1!$J$5)*('Restriction data'!$D:$D&lt;=Tabelle1!$K$5)*('Restriction data'!$G:$G=Tabelle1!$L$5),ROW('Restriction data'!$F:$F)),ROW(1:1)),"")Does this return a result similar to what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.&nbsp;You can dynamically enter the search criteria in cell I5 to L5. The formula is in cell G8 and filled down.&nbsp;

joeeason92 · Answer

I tried to amend my formula using yours but I can't quite get it to give the same results. For some reason I can't upload a spreadsheet but I've edited yours to better reflect what I have and what I was trying to do. Hopefully you can see the issue I have and why the formula is not cooperating with multiple matches from the vehicle number

OliverScheurich

peterbartholomew1 · Answer

JoeEason92&nbsp;For anyone that has 365, FILTER is the relevant function.= LET(
    isVehicle, (vehicleNumber=vehicle),
    isInRange, (locationNumber&gt;=location1) * (locationNumber&lt;=location2),
    isAsFault, (faultDescription=description),
    criterion, isVehicle * isInRange * isAsFault,
    FILTER(locationNumber, criterion)
  )That returns location references rather than sheet row numbers but SEQUENCE could be used to return record numbers.

joeeason92 · Answer

I had been editing online as the reference sheet with the fault archive is updated live from another spreadsheet stored on the same cloud storage, so it was easier to display and reference each online. So I haven't had access to VBA or any command lines for this project. However if you think that is the best or only way to achieve what I'm looking for, I can edit it in an offline document and reconnect the reference data afterwards?

peterbartholomew1 · Answer

JoeEason92&nbsp;There may be some confusion here.&nbsp; As far as I know, there is nothing in the formulas I have written that will not work on-line or on any other hardware platform.&nbsp; Although it looks like code, the formulas are just regular worksheet formulas that use the Excel 365 functions LET and FILTER (I think Google sheets has also implemented the functions) .&nbsp; The use of defined names, rather than direct range references, can cause issues because I don't think Name Manager or the AFE have been implemented within Excel 365 online (if that statement needs to be corrected, I would be delighted to hear).&nbsp;&nbsp;

Forum Discussion

evaluating duplicate inputs

Share

Resources