Delete duplicates with lower case and upper case taken into account?

**Tinwelende** · 06-16-2013, 06:08 AM

Here's an example of cells that I've got:

aaEeppsgsgDsds
AaEePpsgsgDsds
aaEePpsgsgdsds
aaEePpsgsgdsds
AaeePpSgsgdsds
AaEeppSgsgdsds
AaEePpSgsgdsds
AaEePpSgsgDsds
aaEePpsgsgdsds
AaEeppSgsgdsds

Is there a formula I can use (NOT a macro and not a sorting button or anything) that I can put into a new column that deletes duplicates? Maybe it's possible to put formulas in a new column and only get one copy of each original cell, and at the same time taking upper case and lower case into account? The cells need to be identical even down to the upper and lower case letters for them to be deleted.

The cells will contain formula and I need the formulas to be transferred into the new cells as well, so no matter what letters I change in the first cells, the new set of columns will have to re-sort and delete new duplicates that may occur.

**oeldere** · 06-16-2013, 06:37 AM

You don't put in a file, so we can't test it.

sumif($a$1:$a1,$a1)

then select on the values with the result 1.

or select on the values > 1 and delete them.

**Kevin UK** · 06-16-2013, 07:19 AM

Hi Tinwelende

Perhaps something along the lines of the attached file. The formula's are array formula's (CSE to commit).

**Tinwelende** · 06-16-2013, 07:38 AM

That works PERFECTLY, Kevin! Thank you so much!

I need this to work for 16 384 rows. If I dragged the formula down, it didn't work for the next cells. What did I do wrong?

Is there also a way of having it say how many percentages of the original cells were of a certain type?

Example:

AaeePpSgsgdsds 30 %
AaEeppSgsgdsds 25 %

and so on...?

**Kevin UK** · 06-16-2013, 07:57 AM

You will need to extend/adjust the ranges to suit your data and remember it is an array formula (CTRL + SHIFT + ENTER to commit, then copy down).

**Tinwelende** · 06-16-2013, 08:28 AM

I don't get it to work. Maybe I'm changing the wrong data. The formula in the first cell of formulas is:

=OMFEL(INDEX($A$2:$A$11;PASSA(0;FREKVENS(OM(EXAKT($A$2:$A$11;TRANSPONERA($C$1:C1));PASSA(RAD($A$2:$A$11);RAD($A$2:$A$11));"");PASSA(RAD($A$2:$A$11);RAD($A$2:$A$11)));0));"")

(in Swedish).

From what I understand I need to change all the "11" into "16385", but when I do, that first cell becomes blank and the results appear after the 11:th row... I'm clearly doing something wrong...

**Tinwelende** · 06-17-2013, 11:35 AM

I've finally learned how array formulas work, so I think it's time to give this another shot!

I just have one last question before I think I'll get it to work:

If I want the range to be Q2 through Q16385 and the results to end up in AB2 and downwards - How should I then put the formula?