Free Microsoft Excel 2013 Quick Reference

removing duplicates in a case-sensitive manner

Hi everyone,

I don't imagine my problem is too difficult to solve, however, I can't find anything about it on google. Basically, I have a column where the entries are strings of text. I want to remove all duplicates in the column in a case-sensitive manner. Unfortunately, the remove duplicates function in excel treats, for example, "LRV" and "LRv" as duplicates when in fact these should be considered unique. Any suggestions would be very much appreciated.


Hi Everyone,

I need your help! I have a spreadsheet that contains 1-3 email addresses in a single cell separated by a semi-colin. Some of the email addresses are duplicated. Is there a formula/function that can look at the email addresses in the cell, and return the email addresses removing the duplicates?

I have attached a sample document with my desired outcome.

Thanks in advance for the help,
Julie

I'm trying to use Remove Duplicates in Excel 2007 and I want it to remove duplicates for all columns, no matter how many there might be. I think I'm close, but with this code I get errors:


	VB:
	
 
.. 
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column 
.. 
Redim colArray(1 To lastColumn) 
 
 
 ' Fill array will all columns
For i = 1 To lastColumn 
    colArray(i) = i 
Next i 
 
 
 ' Convert column number to letter
lastColumn = Chr(lastColumn + 64) 
ActiveSheet.Range("A1:" & lastColumn & lastRow).RemoveDuplicates Columns:=colArray, Header:=xlYes 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I've also tried these arguments with subscript errors:
Columns:=colArray() = Runtime error 9 subscript out of range
Columns:=(colArray) = Runtime error 5 invalid procedure call or arguemnt

Is there a special way to use arrays for this? Is there a plain old better way to remove duplicates?

Thanks

XL: How to Perform a Case-Sensitive Lookup

You can use LOOKUP functions in Microsoft Excel 97 and later to ... Article ID : 214264: Last Review : January 24, 2007: Revision : 2.1 ... In a new worksheet, type the following data: A1:

How to Perform a Case-Sensitive Lookup

You can use lookup functions in Microsoft Excel 7.0 and earlier to compare values to find an exact match, but the match will not be case-sensitive. However, you can combine the ...

XL: How to Perform a Case-Sensitive Lookup

You can use LOOKUP functions in Microsoft Excel 97 and later to compare values to find an exact match, but the match is not case-sensitive. However, you can combine the LOOKUP ...

Hello All,

Need a macro to delete the duplicate data in a row. My data is like below...

1 Peter Tom Sam Jack Peter Peter
2 Dave Tom Chris Tom Tony
3 Peter Mike Mike Sam
4 Bob Bob

I am looking for a macro to delete all these duplicates in row so that my data should look like below.

1 Peter Tom Sam Jack
2 Dave Tom Chris Tony
3 Peter Mike Sam
4 Bob

Please help.... Thanks in advance,
Lavan

Hi,
I working with Excel 2007 and would like to remove duplicates in column D, but keep the first instant.
It has to look at column A for duplicates then look at column D and remove all the duplicates, but the first one.
I need to keep the data in the other columns (A, B, C, E ...)

Original Data
Column A Column D
CH010 DUE FROM MANAGEMENT
CH010 DUE FROM MANAGEMENT
CH010 DUE FROM MANAGEMENT
CH010 SAMMY MANAGEMENT
CH010 SAMMY MANAGEMENT
CH010 AIDAN MANAGEMENT
CH010 AIDAN MANAGEMENT
CM03 SECURE MESSAGING
CM05 SECURE MESSAGING
CM05 SAFE MESSAGING
NIM010 WEAS MODEL
XAP010 OPEN FINANCIAL
XAP010 OPEN FINANCIAL
XAP010 CLOSE FINANCIAL
XAP010 CLOSE FINANCIAL

Should look like:

Column A Column D
CH010 DUE FROM MANAGEMENT
CH010
CH010
CH010 SAMMY MANAGEMENT
CH010
CH010 AIDAN MANAGEMENT
CH010
CM03 SECURE MESSAGING
CM05 SECURE MESSAGING
CM05 SAFE MESSAGING
NIM010 WEAS MODEL
XAP010 OPEN FINANCIAL
XAP010
XAP010 CLOSE FINANCIAL
XAP010

Thanks

Hi,
Can someone help me with this, I am trying to find the duplicates in a Column C and if a duplicate is found then it needs to check for the Letter "a" (same row)in the Column A. If "a" is found then it needs to clear the dupicate value (In column C).

A B C 1 2 a 1 3 4 2 5 a 4

Hi Guys,

I am building a spreadsheet to hold dates, rooms and course events for an education program. The spreadsheet has a column for each day and there may be a number of events in different rooms in the rows for each day. This works so far!

I want to add some validation in so that it will check to see if I have used the same room more than once in a day. This really means that I want to check for duplicates in a column which represents a single day. My event name in the column is made up of a code :-

PRO-INS-7-A

The last letter is the code for the room and the only significant element I need to use in the duplication test. I can modify the structure of the code if it makes things easier.

I want to do this as a global test via a button. This is because I may need to make a number of changes and then test to see if those chnages are valid.

I am sure that this can be done in Excel but I don't know how.

If you can offer any suggestions I would really appreciate it.

Cheers

Tom Mcguire

This formula will count duplicates in a list. If you have a list that says:

eggs
eggs
beef
beef
beef
chicken

the formula will return:

2
2
3
3
3
1

because thats the number of instances of each unique item.

here's the formula:

=COUNTIF($A$1:$A$10,$A$1:$A$10&"")

but i can't figure out HOW this works. can someone explain it please? Thanks!!!

when using validation, is it possible to stop duplicates in a range of cells from the same list?

thanks for your time,

atcman

Is there an eay way to remove spaces in a range of cells ?

Thank you in advance.

I am trying to highlight duplicates in a row and am using the explanation:

Highlighting Duplicate Entries

Our first task is to highlight the cells in Range1 that are duplicates. We
use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1.
Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional
Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the following
formula in the
formula text box:

=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and
select the font or
color you want your cell formatted with. Finally, click OK. Duplicate
entries in Range1 will be
formatted as you selected. For example, if "Able" occurs twice in Range1,
both
occurrences of "Able" will appear highlighted.

I am changing "A5" to the first cell in the row and still nothing happens.
what am I doing wrong?

thanks

Jim

Hi,

I need to highlight every other set of duplicates in a row. The row consists entirely of duplicates in sets of two or more. So there might be three duplicate numbers in a row followed by eight duplicates of a different number, followed by five of another and so on. I need every other duplicate set highlighted so they are instantly distinguishable. Not sure this is even possible??

Thanks

Hi,
can someone help me with this, I am trying to find the duplicates in a Column C and if a duplicate is found then it needs to check for the Letter "a" (same row)in the Column A. If "a" is found then it needs to clear the dupicate value (In column C).

A B C 1 2 a 1 3 4 2 5 a 4

Thank you,

hi
I want to remove spaces in a middle of a cell/column.

regards

P.M.SANJAY

How to eliminate duplicate in a column?
Thanks

Is it possible to remove duplicates from a range of cells? Via macro! I.e. if a document contains a list of names and data, and name appears more than once, delete than name and the associated data coluns to the right and left?

All the best Alan

I am trying to highlight duplicates in a row and am using the explanation:

Highlighting Duplicate Entries

Our first task is to highlight the cells in Range1 that are duplicates. We
use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1.
Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional
Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the following
formula in the
formula text box:

=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and
select the font or
color you want your cell formatted with. Finally, click OK. Duplicate
entries in Range1 will be
formatted as you selected. For example, if "Able" occurs twice in Range1,
both
occurrences of "Able" will appear highlighted.

I am changing "A5" to the first cell in the row and still nothing happens.
what am I doing wrong?

thanks

Jim

How do I find duplicates in a list that changes weekly. I want the adjacent
column to indicate an error if the column value is duplicated else where in
the list.

I have this code and it works fine but what I want it to do is to count the number of duplicates in a column and if I have more then one it should say "Duplicate" in another column. The code that I have now it shows duplicate on all of duplicates. For example if I have two set of duplicate it shows on both of them that they are duplicate but I want to show only one so I can delete one and keep one. Any idea on how to do that?

Sub LookUpDuplicates()

    Set ws1 = Sheets("Start")
    Set ws2 = Sheets("Compiled_3G")
    Set ws3 = Sheets("KGET_UtranCell")
    Set ws4 = Sheets("Total")
    Set ws5 = Sheets("Neigh_concatenate")
    Set ws6 = Sheets("KGET_ExternalGSMCell")
    Set ws7 = Sheets("CR_3G_add")
    Set ws8 = Sheets("CR_GSM_Add")
    Set ws9 = Sheets("CR_3G_rem")
    Set ws10 = Sheets("CR_GSM_Rem")
    Application.ScreenUpdating = False


ws2.Activate
LR1 = ws2.Cells(Rows.Count, "a").End(xlUp).Row
LR2 = ws2.Cells(Rows.Count, "a").End(xlUp).Row

    For k = LR2 To 3 Step -1
        RowNo = 0
        For n = LR1 To 3 Step -1
        If ws2.Cells(n, 1) = ws2.Cells(k, 1) Then
                On Error Resume Next
                    RowNo = RowNo + 1
                Else
                GoTo Line1
        End If
    
Line1:
        Next n
             duplicateNo = RowNo
             If duplicateNo = 1 Then
             ws2.Cells(k, 11) = ""
             ElseIf duplicateNo > 1 Then
             ws2.Cells(k, 11) = "Duplicate"
             End If
             
    Next k


        With ws2
            Columns.EntireColumn.AutoFit
             .Cells.HorizontalAlignment = xlCenter
        End With

End Sub


How to group duplicates in a column and simultaneously link to the corresponding text in another column.

Ex., What I have.
Column 1 Column 2

DAN-4153 GBD567
DAN-5608 GBD567
DAN-325 GBD1321
DAN-4178 GBD590
GBD4730

What I want.

DAN-4153
DAN-5608 GBD567 (in one row)
DAN-325 GBD1321
DAN4178 GBD4730
GBD590

Hello,

I would like to kindly ask you to find a solution to following problem:

I have an excel report where some of the lines appear twice. These two lines look exactly the same only difference is in one column - in one line there is a date written in the second line the cell is empty.

In the case the line appears twice, I would like to use a macro which looks at these duplicates and deletes automatically the one line with no text in this column and keeps the second line with a date.

I attach also an example with lines highlited that should be removed from the report.

I don't know if it is possible to solve this issue but I would be very grateful for any help.

Thank you lot of times.

Lubi

Hi,

I have a long list of numbers in a column.

How would I determine if there are duplicates, and "flag" them ?

How would I remove all the duplicates in a single, perhaps, step ?

Thanks,
B.