Forum Discussion

JoeEason92's avatar
JoeEason92
Copper Contributor
May 21, 2023

evaluating duplicate inputs

Hello,   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 wo...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 21, 2023

JoeEason92 

=IFERROR(SMALL(IF(('Restriction data'!$C:$C=Tabelle1!$I$5)*('Restriction data'!$D:$D>=Tabelle1!$J$5)*('Restriction data'!$D:$D<=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.

 

You can dynamically enter the search criteria in cell I5 to L5. The formula is in cell G8 and filled down.

 

JoeEason92's avatar
JoeEason92
Copper Contributor
May 21, 2023

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's avatar
    PeterBartholomew1
    Silver Contributor
    May 21, 2023

    JoeEason92 

    For anyone that has 365, FILTER is the relevant function.

    = LET(
    isVehicle, (vehicleNumber=vehicle),
    isInRange, (locationNumber>=location1) * (locationNumber<=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's avatar
      JoeEason92
      Copper Contributor
      May 21, 2023
      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's avatar
        PeterBartholomew1
        Silver Contributor
        May 21, 2023

        JoeEason92 

        There may be some confusion here.  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.  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) .  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).

         

         

Resources