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

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
80
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
Solution
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.
 
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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