excelexcel-formulaexcel-2007

Is possible find the value not equal to one in the column and get all position in the same field?


I need to find the value not equal 1 in the column A and get all the position in the same field.
I want to get the all not equal 1 position into the B11.
I want to use the excel formula function and don't use vba or other code. How can I do ? Is possible ?

enter image description here


Solution

  • Since you are working with Excel-2007, unfortunately you won't be able to use the new TEXTJOIN() and CONCAT() functions (nor power query). However you can get quite creative with a function like CONCATENATE(), it will most likely mean at some point you'll have to do some manual 'labour'. Who wants to do manual labour? ;)

    So in this case I would prefer to go the UDF way. Below a tested example:

    Function GetPos(RNG As Range, VAL As Double) As String
    
    Dim CL As Range, ARR() As String, X as double
    
    X = 1
    For Each CL In RNG
        If CL.Value <> VAL Then
            ReDim Preserve ARR(X)
            ARR(X) = CL.Address(False, False)
            X = X + 1
        End If
    Next CL
    
    If IsEmpty(ARR) Then
      GetPos = "No hits"
    Else
      GetPos = Join(ARR, ",")
      GetPos = Right(GetPos, Len(GetPos) - 1)
    End If
    
    End Function
    

    This one takes two criteria, a range and a numeric value indicating what the cells in your range must NOT be. It will return a string value.

    Call it from your worksheet through =GETPOS(A1:A10,1) and it should return A2,A7,A9

    EDIT

    If you are fine using a helper column you could do it like so:

    Formula in B1:

    =IF(A1<>1,"A"&ROW()&",","")
    

    Formula in B11:

    =LEFT(B1&B2&B3&B4&B5&B6&B7&B8&B9&B10,SUM(LEN(B1:B10))-1)
    

    Enter through CtrlShiftEnter

    Note: If you don't want to use a helper column you'll have to use TRANSPOSE() to 'load' an array of text values but it involves manual labour and IMO you'll surpass your goal.