concat varying number of rows of a single column to 1 cell

[Rem0ram]

Hi

I'm new to vbax.

I would appreciate if any one help me to sort my problem.

I request is to concatenate each cell of one column and have them in a single cell, if there is any repeated values then ignore that cell value

For eg. in the attached xl I have sheet EmailID where number of email id will get populated in column B, i want them all concatenated in one single cell say C2 also any duplicate ids needs to be excluded.

Best
Rem0

[iuliamihaela]

Hi,

I think I can help you, but I need some more details in order to be sure I understood correctly your request.

1. Do you need to be able to choose which column to be concatenated, or it will always be column B?
2. What type of result are you expecting? For the example given, should the output in cell C2 be "Test1@xxx.comTest2@xxx.comTest3@xxx.comTest4@xxx.comTest5@xxx.comTest6@xxx. com"?

Regards,
Iulia

[Rem0ram]

Hi

Thanks for your time.

1. Yes concat column would always be B.

2. Yes exactly same i'm expecting as the email id will get populated with a semicolon once i get this the range c2 has been refered for automail To address recipients.

Best
Rem0

[iuliamihaela]

Here is the code:

Sub Concatenate()
'
' Concatenate Macro
'
Dim LR As Long, i As Integer
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
i = 3

Columns("B:B").Select
ActiveSheet.Range("$B$1:$B$1000000").RemoveDuplicates Columns:=1, Header:=xlYes

Range("B2").Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste

LR = Cells.Find(What:="*", After:=[B1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Range("C3").Select
Call Loop1

ActiveCell.Offset(-1, 0).Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete

End Sub

Sub Loop1()

Do
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-1]C,RC[-1])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub

[Rem0ram]

Hi

Thanks a lot it saved me a day.

But, now i have to include one condition say for eg. if a cell value does not contain @ sysmbol i want to ignore those cell, is it possible?

Best
Rem0