Combine data in a single cell based on a condition

[skull_eagle]

Hi All,

I've been a big fan of this place for a while and I hope someone smarter than me can save me

I'm after a code that will do the following:
Check if Column R = "Fail"
If it does get the corresponding figure from Column Q
List all the figures into Cell "T20"
Separate by " | "

eg. 45 | 67 | 98 | 234 | etc.

I've spent many an hour on this so any help would be extremely appreciated.


Thanks

 

[JoeMo]

There is a limit to how many characters can go into a single cell (T20) so if your list in column R is very long (> approx 32,000 cells containing "fail") the output in T20 may be truncated.

Sub CombineData()
Dim lRw As Long, rng As Range, findWhat As String, fndCell As Range, firstAdr As String
Dim sOut As String, ct As Long
lRw = Range("R" & Rows.Count).End(xlUp).Row
Set rng = Range("R1", "R" & lRw)

findWhat = "fail"

Set fndCell = rng.Find(findWhat, after:=Range("R" & lRw), LookIn:=xlFormulas, lookat:=xlWhole)
If Not fndCell Is Nothing Then
    ct = 1
    firstAdr = fndCell.Address
    sOut = sOut & fndCell.Offset(0, -1).Value & "|"
Else
    MsgBox "No cells found"
    Exit Sub
End If

Do
   Set fndCell = rng.FindNext(fndCell)
    If Not fndCell Is Nothing And fndCell.Address <> firstAdr Then
        ct = ct + 1
        sOut = sOut & fndCell.Offset(0, -1).Value & "|"
    Else
        Exit Do
    End If
Loop

Range("T20").Value = Right(sOut, Len(sOut) - 1)
MsgBox ct & " values placed in Cell T20."


End Sub

 

[JoeMo]

Sorry, just noticed an error in the code I posted. Two lines above the End Sub line: replace "Right" with "Left".

 

[skull_eagle]

I cannot thank you enough Joe!

Thank you so much for taking the time to help me out, It is extremely appreciated.

Your a life saver