View Full Version : concat varying number of rows of a single column to 1 cell
Rem0ram
12-18-2012, 05:54 AM
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
12-18-2012, 07:53 AM
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
12-18-2012, 08:20 AM
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
12-19-2012, 06:55 AM
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
12-19-2012, 02:16 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.