Combine data in a single cell based on a condition

skull_eagle

Board Regular
Joined
Mar 25, 2011
Messages
89
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
Code:
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
 
Upvote 0
Sorry, just noticed an error in the code I posted. Two lines above the End Sub line: replace "Right" with "Left".
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,910
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top