Create new sheet from existing sheet and remove duplicates

[jamesbrown008]

Hi all

I have a worksheet called "Master" with some id's, dates and user names.

Example included below:

All I want to do is copy columns A B and C to a new worksheet called "User List" but remove all duplicates. A row is considered a duplicate if the name (Column C) and date
(Column B) are the same on two or more rows.


For example:

In the screenshot you see the following values for 4/3/11 (In reality there are many more rows and many more dates)


          Column A     Column B     Column C
Row A           1           4/3/11           James
Row B 2 4/3/11 Steve
Row C 3 4/3/11 Fred
Row D 4 4/3/11 James
Row E 5 4/3/11 Steve
Row F 6 4/4/11 James
Row G 7 4/4/11 Fred

In this example I would want the script to only copy

Rows A, B, C, F and G because D and E are duplicates.

As previously mentioned - I don't need to copy the whole row, just the values in Columns A, B and C

Any ideas?

Thanks in advance

James

 

[kunal_shrivastava]

HI,
'Use Remove Duplicates in Data Tab. And select column B and C and then OK
Regards,
Kunal Shrivastava

 

[Peter_SSs]

What Excel version?

BTW, rows are usually labelled with numbers, columns with letters.

 

[jamesbrown008]

For example:

In the screenshot you see the following values for 4/3/11 (In reality there are many more rows and many more dates)


          Column A     Column B     Column C
Row 1           1           4/3/11           James
Row 2           2           4/3/11           Steve
Row 3           3           4/3/11           Fred
Row 4           4           4/3/11           James
Row 5           5           4/3/11           Steve
Row 6           6           4/4/11           James
Row 7           7           4/4/11           Fred



In this example I would want the script to only copy

Rows A, B, C, F and G because D and E are duplicates.

As previously mentioned - I don't need to copy the whole row, just the values in Columns A, B and C

Any ideas?

Thanks in advance

James

 

[jamesbrown008]

What Excel version?

BTW, rows are usually labelled with numbers, columns with letters.

Thanks Peter - I just fixed the original post (what can I say - not got my head screwed on today)

Excel 2007

Need to do it using a VBA macro as I am automating the whole process

 

[Peter_SSs]

I just fixed the original post (what can I say - not got my head screwed on today)

Fixed the table, but not the description:

In this example I would want the script to only copy

Rows A, B, C, F and G because D and E are duplicates.

Test this in a copy of your workbook. It assumes there is not a sheet called 'User List' already in the workbook. It is just doing in code what Kunal suggested (and removing any excess columns).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyRemoveDuplicates()<br>    ActiveSheet.Copy After:=ActiveSheet<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .Offset(, 3).EntireColumn.Delete<br>        .RemoveDuplicates Columns:=Array(2, 3), Header:=xlNo<br>        .Parent.Name = "User List"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>