Macro: Sheet 1 populates Sheets 2 & 3 with unique records only. (stuck)

[FonzAre138]

I did a search through the forum and did not easily run into what I am trying to do, which I think is hopefully simple but I haven't done anything on multiple worksheets with qualifying statements that only return certain values.

Here is the idea behind what I am trying to do (See Attachment):

Sheet 1: Data
Sheet 2: Looking at a column STATE on Sheet 1 for 'NY' and only bringing in only unique records and specific fields into selected columns.
Sheet 3: Looking at same STATE column on Sheet 1 for 'CA' this time and bringing in only unique records and specific fields into different columns.

I am not getting past where I am only returning one of the records that may have been duplicated into the separate Sheets.

The blank fields with no data from Sheet 1 will be manually entered on both Sheets 2 and 3.


17838


Any suggestions?
Thanks!

[Geetha Gupta]

1. you concatenate every cell in each row's data into a cell at the end of the row.
2. now there will be a column of concatenated results.
3. now check for unique entries in the concatenated column and delete rows with duplicate entries.
4. the remaining rows will all have only unique entries.
5. copy each row into the sheet named as in the state column, into the last row in the destination sheet.

[Bob Phillips]

Use a pivottable. Adding a count of State, and add State to Report Filter and you get a view like that, and you can filter by state.

[FonzAre138]

I think I have it figured it out now.

Geetha's mention of concatenation gave me an idea and it simplified my overthinking.

I made a new sheet.
On that sheet I concatenated each row to eliminate the unique records, with commas (State column first).
Then I broke that back out into columns again.
Then in my Sheet 2, I just did a vlookup on the state column being "NY" and created the table by selecting the fields I wanted with state "NY".
Then in Sheet 3, I did the same thing but used "CA" and selected the fields for that table.
I had to tidy up a lot of the formatting but it looks like it came out ok.

Thanks for the input.