Extract data from cell

[TG3]

Hi Excel friends,
This a tricky problem and up to now I have used text to columns, then removed duplicates from each column, then copied and paste all the columns into one column then remove duplicates. Very slow.

I have a column of data elements in column A that are separated by a comma. See uploaded file.

I need to place all the individual items in all the rows (in column A) in column C with only the unique items showing.
Column A could be 30,000 rows but the unique items will < 200.

thank you for your solutions in advance
Tony

 

[aparvez007]

Dear TG3,

You can use advance filter to get the require output.

 

[lohithsriram]

Hi TG3,

Record a macro for all the actions you described. Thats why the macros are for. Click of a button should do all your actions in seconds. Please let me know, if you need any help to create a macro .

 

[SirJB7]

You can use advance filter to get the require output.

Hi, aparvez997!
I don't think so, could you please elaborate?
Regards!

 

[TG3]

Dear TG3,

You can use advance filter to get the require output.

Hi, aparvez997
Thanks for your input. yes, your suggestion does remove the redundant cells but my final objective is to have all the individual comma delimited data elements in a new column with all the duplicates removed.
Tony

 

[TG3]

Hi TG3,

Record a macro for all the actions you described. Thats why the macros are for. Click of a button should do all your actions in seconds. Please let me know, if you need any help to create a macro .

Hi Lohith
Thanks for your suggestion, I am sure a macro would complete the majority of the task but the data volume is not fixed and may run into problems. I was hoping for a formula like the fantastic ones in the Chandoo challenges (sometimes beyond my comprehension) to pull out a list of all the unique data items contained in the comma delimited list in column A.

Tony

 

[SirJB7]

Hi, TG3!

The changing data volume is not a thing to worry about. Instead of using fixed addresses hardcoded into your macro you'd use dynamic named ranges. How to?

Let us say that you have a title row at row 1 and that your data starts at row 2, then you can go to the name manager (Formula tab, Defined names group, Names Manager icon) and create a name called DataTable with this definition:
=OFFSET($A$2,,,COUNTA($A:$A)-1,COUNTA($1:$1))

In your code do something like this:

Option Explicit

Sub IWantACarlsbergAndIWantItNow()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRng = "DataTable"
    ' declarations
    Dim rng As Range
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    ' process
    With rng
      '<here goes your stuff>
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Hence, whenever you add or delete columns or rows from the worksheet, the DataTable dynamic named range definition will change accordingly and when using it within the macro it'll reflect the actual range.

Hope it helps.

Regards!

 

[TG3]

Hi, TG3!

The changing data volume is not a thing to worry about. Instead of using fixed addresses hardcoded into your macro you'd use dynamic named ranges. How to?

Let us say that you have a title row at row 1 and that your data starts at row 2, then you can go to the name manager (Formula tab, Defined names group, Names Manager icon) and create a name called DataTable with this definition:
=OFFSET($A$2,,,COUNTA($A:$A)-1,COUNTA($1:$1))

In your code do something like this:

Option Explicit

Sub IWantACarlsbergAndIWantItNow()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRng = "DataTable"
    ' declarations
    Dim rng As Range
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    ' process
    With rng
      '<here goes your stuff>
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Hence, whenever you add or delete columns or rows from the worksheet, the DataTable dynamic named range definition will change accordingly and when using it within the macro it'll reflect the actual range.

Hope it helps.

Regards!

Thanks for the update, I will try this code
Tony

 

[TG3]

Thanks for the update, I will try this code
Tony

Hi SirJB7
I created a named range "DataTable" =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))
When I run the macro I get a subscript out of range at "Set rng" What have I done wrong ?
' constants
Const ksWS = "Hoja1"
Const ksRng = "DataTable"
' declarations
Dim rng As Range
' start
Set rng = Worksheets(ksWS).Range(ksRng)
Tony

 

[SirJB7]

Hi, TG3!
Isn't your worksheet named "Sheet1" instead of "Hoja1"? Adjust the constant ksWS value properly.
Regards!