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
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).

     

     

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        May 22, 2023

        SergeiBaklan 

        Thanks Sergei.  That is useful to know.  Perhaps I should try the cloud version of Excel again!  As you will have observed, I only use named arrays to build solutions, so my experience of using Excel on-line from a couple of years ago was not encouraging.

Resources