Excel Formula

[kipper19]

Looking for some help here
In cell A1 I have the number 2, and cell B1 I have the name John (B)
In cell A2 I have the number 3, and cell B2 I have the name Jim
In cell A3 I have the number 4, and cell B3 I have the name June (B)

I'm looking for a formula to find names with the (B) after them then copy the number and name to a different cell etc

 

[Michael M]

Maybe like this

Excel 2007
	A	B	C
1	2	John(B)	John(B) 2
2	3	Jim
3	4	June(B)	June(B) 4
Sheet1

Cell Formulas
Range	Formula
C1	=IF(RIGHT(B1,3)="(B)",B1&" "&A1,"")
C2	=IF(RIGHT(B2,3)="(B)",B2&" "&A2,"")
C3	=IF(RIGHT(B3,3)="(B)",B3&" "&A3,"")

 

[kipper19]

Michael, yes that would work but I am looking at scanning lots of data in the column B etc, then when it finds names with the (B) copies the names etc in order in other cells B17, B18 etc,

 

[Michael M]

I think you'll need to use VBA for this !!!

 

[kipper19]

Ok, thanks anyway Michael for your response

 

[Michael M]

Are you happy with a VBA solution ???

 

[kipper19]

Michael, yes certainty if you maybe able to help

 

[Michael M]

Try

Sub MM1()
Dim lr As Long, r As Long, x As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 6
For r = 1 To lr
    If InStr(Range("B" & r).Value, "(B)") Then
        Range("A" & r & ":B" & r).Copy Range("A" & lr + x)
        x = x + 1
    End If
Next r
End Sub

 

[YasserKhalil]

May be

Sub Test()    Dim arr     As Variant
    Dim i       As Long
    
    arr = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = LBound(arr, 1) To UBound(arr, 1)
        If InStr(arr(i, 2), "(B)") > 0 Then
            arr(i, 3) = arr(i, 2) & " " & arr(i, 1)
        End If
    Next i
    Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value = arr
End Sub

 

[FranzV]

Hi kipper,

you can do this with a single array formula.

={IFERROR(INDEX(YourRange,SMALL(IF(NOT(ISERROR(SEARCH("(B)",YourRange))),ROW(A1:A11),""),ROW(A1:A11))),"")}

Using Ctrl+Shift+Enter

YourRange refers to the range of the original list (In your example: B1:B11).
ROW(A1:A11)is used to get an array that contains numbers from 1 to n (n being the number of rows from your list, 11) {1,2,...,n}

I will try to explain how the formula works by separating arguments in different lines and inserting coments in green. It may help to start from the inside and work your way out.

=
{IFERROR( this will avoid getting a bunch of #N/A's after retrieving the values you are looking for
INDEX( this will return the contents of the cells you want
YourRange,
SMALL( this will be used to select only the rows where "(B)" is present in the correct order
IF( this will evaluate each element of your TRUE/FALSE array
NOT( this will convert your array so that TRUE means that "(B)" is present
ISERROR( this will turn all numers into FALSE and all #N/A's into TRUE, leaving an array where TRUE means that "(B)" is not present
SEARCH( this will return the place where "(B)" can be found in each cell or #N/A if it cannot be found
"(B)",
YourRange
)
)
),
ROW(A1:A11), this will turn every TRUE to the row number of the cell
"" and every FALSE to a blank value
),
ROW(A1:A11) this will make SMALL() retrieve the row numbers of the TRUE cells in the proper order
)
),
""
)}

Hope it helps. Regards,

Franz