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