move duplicate cells to top of list

[calvinb]

I have a worksheet with 7 columns (C to I). I paste a large table from the internet into these columns and some of them are duplicates.

I would like to be able to run a macro to find the duplicate cells (in Column C) and move the duplicate rows (including the original row) from Columns C to I to the top of the list, moving the existing data down to make room. I want to be able to move just the rows in the 7 columns, not the entire row. I would also like to change the color of the duplicates to red but that is optional!

Can anyone tell me how to do this?

 

[N1GHTEYES]

Could you add an eigth column? If so, you could do this easily without need for coding.

Name the whole of the data in C. Let's say you call it "Fred".
You can do this dynamically using the offset function if the size of the internet data is not always the same. If you are not sure how, look up the Offset function and "naming ranges". If you're still not sure, come back and ask.

Let's further suppose that the first row contains headers, so your actual data starts in row 2.

Finally assume that column J is available.

So, in J2, enter {=sum(if(C2=Fred,1))} as an array formula. In other words, when you enter it, do not hit Enter, instead use Ctrl+Shift+Enter simultaneously. That will casue the {} to appear - you do not type these.


Now copy this down column J to the last row for which you have valid data.

The values in column J will now show the number of times the value in column C on that row is repeated throughout the whole data in column C. So, all you need to do is sort on column J and the duplicates will automatically be moved to the top.

To colour the duplicate rows red, use conditional formatting - look it up. Again, if you're not sure how, come back and ask.

Tony

 

[calvinb]

I want to do it with coding. Thanks for your suggestion though.

 

[N1GHTEYES]

OK. In that case, start by recording a macro as you do a representative sample of the data shifting by hand. That will show you how to copy / paste / insert rows etc.
----------------------------------------------------------------

To know whether to move a row, you need to know if a cell contains a duplicate value and to do that you need to be able to read the contents of a cell. To do that, you can use the Cells() function in code. Look it up. You can also address an entire range using :

Range(cells(r1,c1),cells(r2,c2)).
------------------------------------------------------------------

So, write out the logic of what you want to do, then use the information above to convert it to code which does it.

If you get stuck, come back here with the code you have tried but does not work.

Tony

 

[calvinb]

I'm not getting anywhere with this!!
I did the record macro thing and here is the results:

Sub Macro4()
'
' Macro4 Macro
'
    Sheets("Import").Select
    Range("C4:I13").Select
    Selection.Copy
    Sheets("Data").Select
    Range("C4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

I want to check the selection in "Import" for duplicates and then move the duplicates and the originals to the "Data" sheet.

I can't figure out how to check for duplicates in Range("C4:I13")

 

[calvinb]

I'm crossposting this to MrExcel forum.