VBA code to Conactenate the data in a row by removing duplicates and sepearating it by comma.

Balajibenz

New Member
Joined
Nov 18, 2020
Messages
49
Office Version
  1. 2013
Platform
  1. Windows
Hi, Can someone help me with VBA code to do the below task.

I have data in A2-Z2 where in each there will be some data. this data in each cell migh be sepearted by comma. what i am looking for is to publish a out put in AB where it concatenates data from A2-Z2 by removing duplicates and seperate each one by a comma. it should ignore if there are blanks and #N/A in between. it should be done untill last row of data in column "A". below is the example.

ABCDEFGH
Development, TestProductionTest, ProductionTest, Production
#N/A​
Production
#N/A​

let us say this particular row (no:2) only has data untill H2 (hightest cell that contains data at any time would be till Z row ).

In the above case output should be Development, Test, Production as empty cells and #N/A should be ignored. thank you in advance
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want. Test with a copy of your data.
I have assumed that your sheet does not contain any formulas.

VBA Code:
Sub Concat_Unique()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  
  With ActiveSheet.UsedRange
    a = .Formula
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 2 To UBound(a)
      d.RemoveAll
      For Each itm In Split(Join(Application.Index(a, i, 0), ","), ",")
        Select Case Trim(itm)
          Case "", "#N/A"
          Case Else: d(Trim(itm)) = 1
        End Select
      Next itm
      b(i, 1) = Join(d.Keys, ",")
    Next i
    .Offset(, .Columns.Count).Resize(, 1).Value = b
  End With
End Sub

Here is my sample data (A:H) and results (col I)

Balajibenz.xlsm
ABCDEFGHI
1ABCDEFGH
2Development, TestProductionTest, ProductionTest, Production#N/AProduction#N/ADevelopment,Test,Production
3dogcat, horse, donkeycatcathorse,catdog,cat,horse,donkey
Sheet1
 
Solution

Balajibenz

New Member
Joined
Nov 18, 2020
Messages
49
Office Version
  1. 2013
Platform
  1. Windows
See if this does what you want. Test with a copy of your data.
I have assumed that your sheet does not contain any formulas.

VBA Code:
Sub Concat_Unique()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
 
  With ActiveSheet.UsedRange
    a = .Formula
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 2 To UBound(a)
      d.RemoveAll
      For Each itm In Split(Join(Application.Index(a, i, 0), ","), ",")
        Select Case Trim(itm)
          Case "", "#N/A"
          Case Else: d(Trim(itm)) = 1
        End Select
      Next itm
      b(i, 1) = Join(d.Keys, ",")
    Next i
    .Offset(, .Columns.Count).Resize(, 1).Value = b
  End With
End Sub

Here is my sample data (A:H) and results (col I)

Balajibenz.xlsm
ABCDEFGHI
1ABCDEFGH
2Development, TestProductionTest, ProductionTest, Production#N/AProduction#N/ADevelopment,Test,Production
3dogcat, horse, donkeycatcathorse,catdog,cat,horse,donkey
Sheet1
Hi Peter,

Though it took time as i have around 4k rows of data in my sheet it worked perfectly ang gave the right output. thank you so much for that.

Sorry but can you help to amend the code as I have this data in my original sheet starting from BP. (data from A2:BO2 should be ignored) and ends in DL.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try making this change. Results will then come in column DM.

Rich (BB code):
  With ActiveSheet.UsedRange
  With Intersect(ActiveSheet.UsedRange.EntireRow, Columns("BP:DL"))
 

Balajibenz

New Member
Joined
Nov 18, 2020
Messages
49
Office Version
  1. 2013
Platform
  1. Windows
Try making this change. Results will then come in column DM.

Rich (BB code):
  With ActiveSheet.UsedRange
  With Intersect(ActiveSheet.UsedRange.EntireRow, Columns("BP:DL"))
Thanks Peter (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

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
Top