VBA to return only unique values from a range of comma separated words on multiple rows.

[Ananthak275]

Hi everyone,

I have the following rows of texts. My data starts on Row 2 (since there is a header). how do i get the unique values? and them pasted below the last row of that column. For example, CityName (Column A) starts in A2 and ends in A7, so i want the unique values to be pasted start on A8. While StateName starts in B2 and ends in B5, so i would want the unique states starting in b6?

1	CityName	StateName
2	Dallas. Houston, Philadelphia	California, Texas
3	Chicago	Pennsylvania, California, Texas
4	Dallas. Houston, Philadelphia	Arizona, New York
5	Houston, Philadelphia, Austin	New York
6	Austin, New York City, Los Angeles
7	San Jose
8

 

[Ananthak275]

its duplicating cells that have no commas, a single word in the cell. In my table example, its duplicating "Chicago" as well.

 

[Fluff]

Because you have cells with just a single value, how is the macro meant to know if it should be copied or not?