Concatenate

R

rascal9

New Member
Joined
Dec 12, 2015
Messages
9
I have read many discussions on how to CONCATENATE or not to. Using public functions or entering the data in active cell.

Right now I want it to select range D2 and combine all data in column D (not including blanks or duplicates) into Cell D2 with a delimiter (/)

Range("D2").Select
ActiveCell.FormulaR1C1 =

Raw Data

[TABLE="class: grid, width: 133"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"]Column D
[/TD]
[/TR]
[TR]
[TD="align: center"]Line
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]50
[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
</tbody>[/TABLE]

Fixed Data
[TABLE="class: grid, width: 133"]
<tbody>[TR]
[TD="align: center"]Column D
[/TD]
[/TR]
[TR]
[TD="align: center"]Line
[/TD]
[/TR]
[TR]
[TD="align: center"]10/30/50/70
[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]50
[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
</tbody>[/TABLE]

I tried applying the below function with no luck.

Code: 
Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Assuming "Line" is a header and your numbers start on Row 2, give this code a try...
Code: 
Sub GetUniques()
  Dim R As Long, Dict As Object
  Set Dict = CreateObject("Scripting.Dictionary")
    For R = 2 To Cells(Rows.Count, "D").End(xlUp).Row
      If Len(Cells(R, "D").Value) Then Dict.Item(Cells(R, "D").Value) = 1
    Next
    Cells(2, "F").Value = Join(Dict.keys, "/")
End Sub
 
Last edited:
Upvote 0
Darn, that's so much smaller than my code.
Here it is anyway

Code: 
Sub k1()
k = ""
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
For i = 3 To lastrow
    If Cells(i, 4) <> "" Then
        l = 0
        For j = i + 1 To lastrow
        If Cells(i, 4) = Cells(j, 4) Then
            l = 1
        End If
        Next j
    End If
    If l = 0 Then
        k = k & Cells(i, 4) & "/"
    End If
Next i
k = Left(k, Len(k) - 1)
Cells(2, 4) = k
End Sub
 
Last edited:
Upvote 0
rascal9 said:
I have read many discussions on how to CONCATENATE or not to. Using public functions or entering the data in active cell.

Right now I want it to select range D2 and combine all data in column D (not including blanks or duplicates) into Cell D2 with a delimiter (/)

Range("D2").Select
ActiveCell.FormulaR1C1 =

Raw Data

[TABLE="class: grid, width: 133"]
<tbody>[TR]
[TD="align: center"]Column D[/TD]
[/TR]
[TR]
[TD="align: center"]Line[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
</tbody>[/TABLE]

Fixed Data
[TABLE="class: grid, width: 133"]
<tbody>[TR]
[TD="align: center"]Column D[/TD]
[/TR]
[TR]
[TD="align: center"]Line[/TD]
[/TR]
[TR]
[TD="align: center"]10/30/50/70[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[/TR]
</tbody>[/TABLE]

I tried applying the below function with no luck.

Code: 
Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function
Click to expand...

Answering my own thread now. But before Combine into one cell im deleting duplicate first, has to be a better way to do this?!

Code: 
    ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$B$1:$B$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$C$1:$C$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$D$1:$D$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$E$1:$E$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$F$1:$F$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$G$1:$G$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$H$1:$H$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$I$1:$I$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$J$1:$J$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$K$1:$K$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$L$1:$L$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$M$1:$M$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$N$1:$N$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$O$1:$O$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$P$1:$P$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$Q$1:$Q$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$R$1:$R$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$S$1:$S$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$T$1:$T$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$U$1:$U$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$V$1:$V$20").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.Range("$W$1:$W$20").RemoveDuplicates Columns:=1, Header:=xlYes
 
Upvote 0
Rick Rothstein said:
Assuming "Line" is a header and your numbers start on Row 2, give this code a try...
Code: 
Sub GetUniques()
  Dim R As Long, Dict As Object
  Set Dict = CreateObject("Scripting.Dictionary")
    For R = 2 To Cells(Rows.Count, "D").End(xlUp).Row
      If Len(Cells(R, "D").Value) Then Dict.Item(Cells(R, "D").Value) = 1
    Next
    Cells(2, "F").Value = Join(Dict.keys, "/")
End Sub
Click to expand...


Awesome!! Didn't even see responses before i posted. Im giving this one a try and trying to open my range up to a few more columns. There are 4 columns I'm doing in total. (A,B,C,D)

Thanks alot fellas for the responses!!!
 
Upvote 0
rascal9 said:
Awesome!! Didn't even see responses before i posted. Im giving this one a try and trying to open my range up to a few more columns. There are 4 columns I'm doing in total. (A,B,C,D)
Click to expand...
Give this code a try...
Code: 
Sub GetUniques()
  Dim R As Long, C As Long, Dict As Object
  Set Dict = CreateObject("Scripting.Dictionary")
  For C = 1 To 4
    Dict.RemoveAll
    For R = 2 To Cells(Rows.Count, C).End(xlUp).Row
      If Len(Cells(R, C).Value) Then Dict.Item(Cells(R, C).Value) = 1
    Next
    Cells(2, C).NumberFormat = "@"
    Cells(2, C).Value = Join(Dict.keys, "/")
  Next
  Set Dict = Nothing
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
593
motilulla
M
F
  • Solved
How to show the nth occurance based on another column
Replies
6
Views
310
feni1388
F
M
  • Question Question
VBA changing values in multiple columns - where it should only be changing a single column
Replies
1
Views
345
Akuini
Akuini
N
  • Question Question
Loop column to check duplicates
Replies
3
Views
494
rlv01
R
J
  • Solved
Filter a listbox containing 12 columns using a single searchbox
Replies
8
Views
774
Anthony47
A

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

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