How to remove duplicates AND values that have duplicates?

How to remove duplicates AND values that have duplicates?

What I need to do is remove any duplicate values, but ALSO remove any values that have duplicates. Is there a way to do this? I know about the remove duplicates feature, but it doesn't give you the option to specify that you also want to remove the original values of the duplicates.

Note: I can do this using the filter and sort feature or the advanced filter option, but I need another way to do this WITHOUT using any of the filter options.


Example: If you have the the below list on the left including duplicates, I would need a way to get the list to the right of the 1st list which only includes values that don't have values which included duplicates from the list on the left.

1 1
2
2
3 3
4 4
5
5
6 6
7
7
8 8
9
9
10 10


Any thoughts?

Formula:

Please Login or Register  to view this content.

will give the results as you have listed them.

If you need them listed without spaces, in the next column enter:

Formula:

Please Login or Register  to view this content.

The result will be:

	A	B	C
1	1	1
2	2		1
3	2		3
4	3	3	4
5	4	4	6
6	5		8
7	5		10
8	6	6
9	7
10	7
11	8	8
12	9
13	9
14	10	10

bachfantasia,

If your raw data is in column A, beginning in cell A1 down, already sorted/grouped per your text display.

The below macro will remove the duplicates plus the original, and, shift what is left up.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDuplicates macro.

bachfantasia,

What is the meaning of ................................................ ?

This formula will reduce the list of values to only values that appear once in the list.

Formula:

Please Login or Register  to view this content.

The result is:

	A	B
1	1
2	2	1
3	2	3
4	3	4
5	4	6
6	5	8
7	5	10
8	6
9	7
10	7
11	8
12	9
13	9
14	10

You're welcome.

bachfantasia,

Did you even try my macro?