HELP! Need to convert comma delimited text into rows

[tbark]

Hello -

I have 65 rows of data; each cell contains anywhere from 5 to 8 comma delimited values that need to be housed in their own rows. Here is an example of my data:

Cell A2: 0x460297gpr,9806458rtgjd,0x5641313746fd,9x44564fs4f5s4f5ds3,5x4f35sd4f3sd4f3s
Cell A3: 3xsf23ds4dff,8xf4ds23df2s,1xf2sdfs3fds534f3s,2x5df45s34f5s3fds2,5x45fds34f5ds34fd5s3,7xdfs354fds5s
Continues through cell A65+ with similar values.

Here's where I need to get to: would love if I can do this in a macro or a formula. Doing it manually is not an option since this will go into another file where rows will be added to Column A with new and updated values. Here's my desired output:

1. the values in each cell get their own row
2. i then need to de-dupe the results and only show unique values
3. all of the results need to be in one column.

can someone PLEASE help me?

 

[Rick Rothstein]

No, I did not really follow that at all. Perhaps a small example would help. Let's say you had this on Sheet1...

	A	B	C	D	E
one,two,three	B1	C1	D1
four,five,six,seven	B2	C2	D2

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 118px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

</TBODY>

What should the output on Sheet2 look like for these values?

 

[tbark]

I am so sorry - sometimes explaining these things are more clear in my head than in words

Using your example, here is what the output would look like on Sheet2 (sorry mine is not as nicely formatted as yours, but hopefully you get the gist of it!):

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]B[/TD]
[TD="class: xl64, width: 64"]C[/TD]
[TD="class: xl64, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1 [/TD]
[TD]one[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2 [/TD]
[TD]two[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3 [/TD]
[TD]three[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4 [/TD]
[TD]four[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5 [/TD]
[TD]five[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6 [/TD]
[TD]six[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7 [/TD]
[TD]seven[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[/TR]
</tbody>[/TABLE]