Named Range Based on Conditional Formatting

[Dale Katz]

Greetings all,

I have been working on a project that will come to a close when I can figure out a solution to this final obstacle. I am applying Conditional Formatting -> Highlight Cell Rules -> Duplicate Values to column A. I then Sort by Cell Color with the colored cells at the top of the sheet. I then move only those colored cells from Sheet1 to Sheet2.

Here is the dilemma and please bear in mind that I am very new to VBA so thank you in advance for your patience.

Each time I use this template/workbook, the number of colored cells will change. So I had a thought but am not quite sure as to the syntax or even if it is the best approach.

I thought I would apply the conditional formatting and then somehow name the range of cells that is now highlighted red and instead of trying to move each row by selecting them manually, I would simply have VBA code that moves the named range based on whether or not the cells were red.

Can anyone help with this task?

Peace and respect,
Dale Katz
dale@iceportal.com

 

[Hui]

Dale

Firstly, Welcome to the Chandoo.org Forums

Can you please post a sample file
Please include a sample of the final result as well

 

[NARAYANK991]

Hi Dale ,

Can you explain the objective of this exercise ?

I tried out what you have posted , and I end up with the following :

All the colored cells are at the top of the data range , but the point here is that they are all duplicates , which means if you now move these cells to another sheet , the data that is left behind on the original sheet does not include even the first instance of the duplicates ; what is left is those items which were unique to start with.

I cannot think of an application which would need to do this , since if your data has duplicates , what you would want to do would be to remove the duplicates alone , not remove all instances of duplicates.

For example , if your data is say the numbers 1 , 3 , 2 , 2, 1 , 5 , if you remove duplicates , you are left with the numbers 1 , 3 , 2 , 5.

In the process you have described , you would be left with only 3 and 5.

If you really want the numbers 1 , 3 , 2 and 5 , then what you can do is , select your data range in column A , say A2 through A100 , and then use the following formula to CF the cells :

=COUNTIF($A$2:$A2,$A2)=1

This will color all the distinct cells leaving the duplicates uncolored ; now when you move the colored set to another sheet , that sheet will have all unique values.

If you want that only the duplicate instances are to be colored , so that they can be removed , use the formula :

=COUNTIF($A$2:$A2,$A2)<>1

Narayan

 

[Dale Katz]

Greetings Narayan and thank you very much for your reply. Imagine that I took 2 files and ran them through a 3rd party software application that matched them field by field. The result set provided by this software will have some anomalies that must be further processed by human eyes. These anomalies are denoted in the column titled group. The numbers in that column indicate the group number and the records possessing the same group number are the anomalies.

I have applied Highlighting of Duplicates to those records based on the Group column. When I sort them in descending order, causing the highlighted records to group at the top of the sheet, I then need to move ONLY those records over to Sheet2 and delete them from Sheet1. I would like to move them based on the highlight color.

Please keep in mind that the Group column will still have values, although ONLY the records that share a value will be moved (hence the duplicates choice in highlighting).

I realize that it does not seem like anyone would need to do what we are doing, however, once ALL of the duplicates are moved to Sheet2, the code will rename Sheet2 to "Dup Group" and human eyes can then determine which of the duplicates is truly a match and which is a false-positive.

Thank you again so much for your assistance with this task.

Dale

Hi Dale ,

Can you explain the objective of this exercise ?

I tried out what you have posted , and I end up with the following :

All the colored cells are at the top of the data range , but the point here is that they are all duplicates , which means if you now move these cells to another sheet , the data that is left behind on the original sheet does not include even the first instance of the duplicates ; what is left is those items which were unique to start with.

I cannot think of an application which would need to do this , since if your data has duplicates , what you would want to do would be to remove the duplicates alone , not remove all instances of duplicates.

For example , if your data is say the numbers 1 , 3 , 2 , 2, 1 , 5 , if you remove duplicates , you are left with the numbers 1 , 3 , 2 , 5.

In the process you have described , you would be left with only 3 and 5.

If you really want the numbers 1 , 3 , 2 and 5 , then what you can do is , select your data range in column A , say A2 through A100 , and then use the following formula to CF the cells :

=COUNTIF($A$2:$A2,$A2)=1

This will color all the distinct cells leaving the duplicates uncolored ; now when you move the colored set to another sheet , that sheet will have all unique values.

If you want that only the duplicate instances are to be colored , so that they can be removed , use the formula :

=COUNTIF($A$2:$A2,$A2)<>1

Narayan

 

[NARAYANK991]

Hi Dale ,

See the attached file. It has a macro called ExtractColoredRecords which does the following :

1. Identifies the last row of input data in column A on Sheet1

2. Sets up the Conditional Formatting for highlighting duplicates using an orange color

3. Sorts the range so that the colored records are at the top

4. Copies the colored records to Sheet2

5. Deletes the colored records from Sheet1

See if this is what you wanted.

Narayan