Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Remove Duplicates From Single Cell

I have a column that has duplicate information in it.

An exapmle of one of the cells is:

I want a vba macro to go thru the column and delete the duplicates in each cell.

So this one will be:

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Split Phone Numbers Into Single Column & Remove Duplicates
I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:


The results on the next spreadsheet need to be:


I am looking for a way to automate this process.

View Replies!   View Related
Remove Duplicates Within A Cell
Below is a typical example of the contents of one of my cells (of which I have around 500 cells):

263,330,335,430,431,435,640,700,748,750,752,800,807,901,916,917,937,944,954,953,962,266,2038,2054,20 56,2057,2058,357, 591, 800, 802, 748, 423, 801, 570, 955, 747, 940, 800, 748, 918, 800,730,579,728,307,310,577,717,939,958,713,
332,613,640,661,690, 800, 613, 332, 434, 575, 593, 904, 943, 648, 946, 947, 2079I'd like to remove duplicate entries from this cell (per cell) e.g. "800" appears several times.

We can distinguish between each entry by the comma - but how can I get Excel to look inside one specific cell at text and remove these?

View Replies!   View Related
Remove Certain Duplicates ....
Is it possible to scan through column A for duplicates if found delete the row that has not got any data in either column D E or F? If Duplicates are found and neither have any data in D E or F Delete all but one of the duplicates.

View Replies!   View Related
Remove Duplicates
I have a sheet that looks like this.
JOE D.02082008/28/199 H PAnnual Increase06/05/2006
JOE D.02082008/28/1995 H PReclassification06/11/2007
JOE D.02082008/28/1995 H PReclassification02/11/2008

The name is in Column F the employee Number is the 020820 and we want to only have the latest date in the last column.

So out of this data above. The bottom one with the 2/11/2008 is the one we want to keep.
And delete those other 2.

The next group has duplicate names 10 of them. But only want to keep the most recent.

View Replies!   View Related
VBA Code To Remove Duplicates
Private Sub Dupe_Remover()
'29/06/2005 by nhunter

Application. ScreenUpdating = False
Dim R1 As Range
Dim drow As Integer
Dim lastitem As String

Set R1 = ActiveCell


If Trim(ActiveCell) = "" Then
Goto procend
End If............

View Replies!   View Related
Remove Duplicates Values
I receive a customer list with over 1.000 customers. Each customer have it's own customer ID and some customers have more then one dept / case.

I now wonder how I can find duplicates if the customer ID is the same in the customer ID field and remove any duplicates?

View Replies!   View Related
Remove Duplicates From A Column
how can i remove duplictes from a colum. For example:

Column C:

A;B;C;A;D;E;A;G;B *(A is three times here and B two times!)
F;G;H;I;F*(F is here two times)

I would like to have the duplicates removed from each cell in column C so that each member is unique in the cell. The example would then look like:

Column C: (after removing the dupplicates A and B in first cell and F in second cell)


View Replies!   View Related
Remove Duplicates In Column
I would like to remove duplicates in column D, but keep the first instant.
It was to look at column A for duplicates then look at column D and remove all but the first one.

Column A Column D
XAP010 OPEN FINANCIAL ........................

View Replies!   View Related
Remove Duplicates From Col.A And Merge Col.B
i have a wordlist containing about 0.5 Million words in col.A and Millions of meanings in Col.B. Now in Col. A there are many duplicates. I would like to make one cell of all duplicates in col.A and put their meanings together in B with seperator ";". Its very important that there are no duplicates in Col. B.

For example:
Col. A Col.B
w1 m1;m2;m3
w1 m1;m2;m5
w2 m23;m24
w2 m50

Now after running the macro the list should look like:

Col.A Col.B
w1 m1;m2;m3;m5
w2 m23;m24;m50

The words in col. A are alphabetically ordered and all the duplicates are followed by each other.


View Replies!   View Related
Remove The Duplicates Rows
I have a customer database that is sorted by phone number. How do I remove duplicate rows.

View Replies!   View Related
How To Remove Only Unique And Keep Duplicates
I have been trying for a day now to figure out how to remove the unique cells and keep the duplicates only. I have tried a macro but it does not seem to work. Has any one run into this before. I have attached a sample what I would like to do is remove all non-duplicates from column A. The only macro I could find was this:

View Replies!   View Related
Conditionally Remove Duplicates
Attach is a Demo –Sample file

The formatting etc is same for real file – Note that the real file will have up to 65000 rows of data like this.

My Problem is to remove conditionally some duplicate rows - however my difficulty trying to thinking about this is that I cant use a unique value comparison as the columns I to L they all are X s so when a X is present in columns I to L need to be conditional on comparing values in other columns

What is needed is to repeat remove duplications in each of the four columns I to L (titled E1, E2, E3, and E4 ) when there is a match of string in columns F, G and M – but don’t delete if on same row there is a value in columns O to S (E5 to E9).

In the demo file – I have highlighted in yellow the rows that would be removed if the code works.

IF there is an “X” in column “I” (E1) and the string in column F (personnel number) is the same as row below it, and the string in column G (person name) is same as below it, and the string in Column M (Team number) is same as row below it, and there are NO values in columns O to S (E5 to E9) on same row – Then delete the duplicate/s – retain only one.

Repeat for columns J, K and L (E2, E3 and E4)

View Replies!   View Related
Remove Consecutive Duplicates Only
I'm having a "challenge" in Excel.

I've got two columns in Excel with data in it.

Column A is unique data and consists out of date and time and Column B is data which indicates the state of an application.

So every task the application is performing gets logged every minute so Column B is showing duplicates.

Now what I want to do is to remove the duplicate actions but only if they are subsequent. So an action can happen several times a day but only once in an hour. But if I use the AdvancedFilter with the Unique Records only, it removes all the duplicates also from later on the day and that is not the intention.

So I would need a macro for it. My problem is that I somewhat know how it should work, but I don't know the syntax to write it. So it should be something like If the data on the next line is the same as the current, then remove the next line, if not go to the next line and check again.

View Replies!   View Related
Remove The Duplicates Values
We are having a problem merging our databases. We are using excel to streamline databases to be included in our GiftWorks database of donors. However we are having the problem of many duplicates with multiple addresses, contact info etc. I have come to find that there is no way other than the "bitch work" method of going thru by hand in the GiftWorks database to find duplicates. However we can make a smartlist and export to excel and then perhaps merge the new info with this one. I was just wondering if there is anyway to sort duplicates and merge their information by first name/last name.

View Replies!   View Related
Remove Duplicates Of Multiple Columns
I have searched the many forum solutions for duplicates however the ones I found relate to a single column. However what I would like to be able to do is starting from row 6 is to remove duplicates of multiples columns of a single worksheet, namely columns M, O, P, Q and R. Note some cells in each column are blank.

To clarify - it would firstly check for duplicates in column M, keeping just one of the rows of the duplicated value and removing entiely the row/rows of where the duplications appeared. Then go through each of the other four columns. of the worksheet doing the same for each of the other four columns.

Then after the duplicates are removed from all five columns provide a tally at the bottom of the worksheet of how many times a value now appears in the cells of each of repective columns.

View Replies!   View Related
Remove Duplicates And Delete Cells...
I am using the following code to grab installed software on a remote computer through a macro in Excel 2007. I don't have the entire code I'm using as the majority of it works, this section here though is where I'm having problems.

View Replies!   View Related
Sort & Remove Duplicates In Combobox
I am currently using the following to populate the choices in my ComboBox:

For Each rngX In Range("Watchlist_Source_Menu").Cells
If Len(rngX.Value) > 0 Then
ComboBox2.AddItem rngX.Value
End If

The range "Watchlist_Source_Menu" is p4:p104. It works fin but if there are duplicates in that range it duplicates the choices in the ComboBox. Is there a way that I can only show unique choices and show them alphabetized in the combo box upon intializing the userform?

View Replies!   View Related
Update Main Record And Remove Duplicates
I have a spreadsheet with a list of items for a company inventory. For each item there is a row that has all the details related to the item. There is also a "sub-row" for each item that only has the item number and current costs and pricing for the item.

I have a 2-part task that I am having trouble with:

First: I have to essentially update the "main row" with the updated cost and pricing info from the "sub-row" wherever one would exist and...

Second: Delete the duplicate row for the item (the one with less info).

My production file has thousands of rows, but I have created a sample file if anyone can help me with getting my task accomplished. I am new to these forums, so I'm not sure how I could attach the sample file to this post, but if someone could tell me how to do that I will provide it. In the mean time I will just insert a screenshot.

View Replies!   View Related
Advance Filter Isn't Working - To Remove Duplicates
I'm having an issue where advnace filter isnt working and I need to get all of the unique entries.

I don't know if this has anything to do with it but I got some erros saying the numbers were being pu as text so I changed the cell tpe to a custom with 00000000 because the unique number I need to filter is 10 digits long.

As another alternative I would paypal some one a few bux if they can help me just compare two lists and make a report with the number of netries that show up in one list and not the other and vice versa.

View Replies!   View Related
Remove Duplicates In A Dynamic Range Using Excel 2007
I am using the follwoing code which works as it is supposed to except that it does not remove the duplicate entries, almost as though the code skips that step. How can I fix this?

View Replies!   View Related
Remove Duplicates Leaving Highest Price Remaining
I have a a spreadsheet for work that has 7,000 lines.

All are french wines. with 7 or 8 columns like Price, Wine, Vintage , etc.

I have situation where I have identical wines from different sources.

What I need to do is keep the one with the highest price and either hide or delete the others.

For instance. I have 10 lines of a wine 2005 Leoville Las Cases. 5 Lines are in regular sized bottles. The other 4 are 1.5 L MAGNUM Sized bottles . Is theres a function or formula that will pick the 1 line with the highest price from each bottle size (and in my spreadheet of 7000 each wine as there are hundreds) and delete the others or hide or some way of sorting them?I just need to remove duplicates and be left with the Item with highest price. If there ends up with 2 or more with same high price can it randomly leave 1 and delete the others?

So basically of all the columns only the following 4 variables would need to used as variables: VIntage, Wine, Bottle Size and Price.

View Replies!   View Related
Using Scripting Dictionary To Remove Duplicates In 5 Column Table
removing duplicates from a the first column of a two column table while maintaining the data in the second column. Unfortunately my skills fall short and I have been unable to alter the code to work for a 5 column table.

For example, if I might have data that might look something similar to this: ....

View Replies!   View Related
Remove Duplicates :: Advanced Filter, Unique Records Only
I'm having a problem deleting duplicates from list in excel. I’ve attached a sample. I’ve tried the following:

1-Advanced Filter, Unique Records Only

2-Remove Duplicates function in Excel 07.

3-Pivot Table

4-Colour Conditional Formatting, sorting by colour

5-B2=IF(A2=A3,”Dup”,”Not-Dup”). The entire column returns “Not-Dup”

6-I’ve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ‘Numeric’ Keypad to appear in the ‘Find’ Function.


View Replies!   View Related
Trying To Run A Macro To Sort And Remove Duplicates From Entire Sheet
Currently use this macro (see below) but occasionally I have more rows than 842. I could increase to 10000 and this would work but looking for a more automated macro that will automatically remove duplicates from all rows i.e. a macro that works out all rows.

Sub copyTab()
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$J$842").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

View Replies!   View Related
Count The Sums Of Repetitive Cells And Then Remove The Duplicates?
I've got the following table (see attachment). In a column A there're the articles and some of them appear several times. In column B there's the quantity. The goal is to get in column C the sum of quantities in column B for the repetitive articles in column A and then delete the repetitive articles.

so the table should look like:
3RD125070442H 30
3RE071870493H 4
3RI100009624T 15

in excel 2007 I found only the function to remove the duplicate cells but I need to get the sums before removing them and have no clue how to that.

View Replies!   View Related
Remove Leading Single Quote From Text Cells
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")

Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).

I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.

View Replies!   View Related
Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)
If I have, in one cell (call it D1):


and in a lookup table on another sheet:
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...



View Replies!   View Related
Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View Replies!   View Related
Find Multiple Instances Of Single Criterion In Row & Return To A Single Col
Find Multiple instances of Numeric Criterion in Row & Return To a Single

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

View Replies!   View Related
Check Cell Rows Based On Numbers In Single Cell
I have problem with function, I have array with random numbers in cell G1 and columns with X in some of the cells, now I want to check the cells with numbers from cell G1 in column F to see is there X or not if yes count if not do nothing. In attached example result should be 3.

View Replies!   View Related
Formatting Cell Currencies Based On A Selection In A Single Cell
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.

View Replies!   View Related
Expand/Create Single Cell Alpha Series From Cell
I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.

View Replies!   View Related
123569LOC23 is in one cell and I am trying to copy the "LOC23" to a new cell. However, the amount of digits vary: LOC23, LOC1,LOC15 etc all the way down the column.

View Replies!   View Related
Another Delete Duplicates (last Cell)
I have a macro that Deletes duplicates in a column. But it deletes all but the last cell. Does anyone know how to change it so It keeps the first cell and deletes the subsequent duplicates Instead?

View Replies!   View Related
Show Duplicates Name's In Cell
I have a list on column a



What I want is to only show duplicates, else "". One way is to sort column then use =exact function and deterimine true/false. however is there an easier way, hence withouth having to sort? i just want it to show only duplicate names in cell's.

View Replies!   View Related
Range Duplicates Itself If Cell Has Number >0
I have this macro when run copys and pastes values for a row of cells onto another sheet. However if 2 cells in the same row have a number greater than zero. It duplicates the entry. What I need is some sort of check that says if 2 or more cells in the same row have a number greater than zero just copy that row once only.

View Replies!   View Related
Search For Each ID In Single Cell & Return Corresponding Cell
to search for specific text in a cell but not just one word or letter, in fact it could be many. I need to first verify that the exact texts exist in a table and then match the various text instances with corresponding data in that table.

At the moment, this works to search for one type of text and verify it's exsistence in the able and indeed return the corresponding value, but i need it to do it for all verified texts

=(INDEX(A2:A4,MATCH( LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$2:$B$6&" "," "&D2&" "),$B$2:$B$6),B2:B4,0),1))

Example attached

View Replies!   View Related
Put Both Text And Cell Value From Another Sheet In A Single Cell..
I'm trying to put both text and cell value from another sheet in a single cell.

I've tried:
Doesn't work.

Also, I understand this is somehow it's supposed to be done
="Total: "&A12
But I need the cell data to be from another sheet and also I need the text to succeed the cell value.

Going by the above example, I tried:
But that also doesn't work.

View Replies!   View Related
Change Text In A Cell, Sum Qty And Delete Duplicates After That
writing the vba code that will sum the qty of a common item, change text in a cell then delete its duplicate?

Code - column 1
Item - column 2
Qty - column 20

if possible would like vba to ask for column before running test...

really appreciate if anyone would be so kind to help

Code Item Qty
SP A 2
JY A 1
SP B 3
SP C 1
JY C 4
Code Item Qty
P A 3
SP B 3
P C 5

View Replies!   View Related
Eliminate Duplicates And Alphabetize The Rest Within A Cell
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Hello good people.

I tried to search back through old threads and could not find the answer, so this is what I am trying to do.

I would like to eliminate the duplicate items within a particular cell and alphabetize the remaining items.

For example,
South Atlantic, East South Central, South Atlantic, Pacific, Mountain, New England, East South Central,

Wanted result:
East South Central, Mountain, New England, Pacific, South Atlantic,

I’d do it manually, but at current rate, it looks like it will take me about 86 hours

View Replies!   View Related
Count Duplicates In A Cell Then Rank Them And Sort Them
I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.

The number order of precendence is 0-9 with zero being the lowest.

Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.

I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)

When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.

Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.

View Replies!   View Related
Find Active Cell Value & Ignore Duplicates
I need to write a macro for find a value in all sheets in current workbook. The problem is put like this: I have to write a ID (for DVD archiving) in a column but testing that generated value is unique in entire workbook (I have multiple sheets, organized by movie genre). I can use find method and testing for value <> Nothing but it always said that the value is present (the current cell value); there is any method to omit selected cell value? I think "After:=" parameter can do this but I don't figure out how to handle with it...

View Replies!   View Related
Sum Totaling Of A Single Cell?
Is there any way that one would be able to sum total a single cell?

I have my numbers listed in column A & would like the return in column B .....

View Replies!   View Related
Incrementing A Value In A Single Cell
Is there a way to count how many times a value is changed?

Cell A1 can be either “YES” or “NO”. Its' value is controlled by validation…

When Cell A1 is changed from “YES” to “NO” I want to increment the value in B1 by one.

When Cell A1 is changed from “NO” to “YES” I want to leave B1 as it is.

View Replies!   View Related
Let me state first that I do not see the need to put so much in one cell. Aren't there enough cells ?

Secondly, note the difference between DISPLAY and actual CONTENTS (see formulabar) of the cell. Contentslimits: see helpfiles, but displaylimits check out this code. It will explain itself. Experiment with settings within code.

Option Explicit

Sub count_characters_in_cell()
'Erik Van Geit
Dim cell As Range
Dim nr As Variant
Dim x As Integer
Dim temp As String

Set cell = Sheets(1).Range("A1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

View Replies!   View Related
Get A Single Cell To Look At An Array
What I’m trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. I’ve attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}

But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.

View Replies!   View Related
Accumulate Value In A Single Cell
I will try explain what I want to calculate but cannot find the way. I want to calculate a result from two digits, then remember the result and add to this sum next digit typing in the same cell. Example:

colum A names
colum B numbers
In cell B1 I will write 5, after enter result in B1 is 5.
In cell B1 again write 7 result in B1 is 12.
In cell B1 again write 6 result in B1 is 18....and so on.

View Replies!   View Related
Autosum For Single Cell In Vba
I have a macro that takes a large block of info, sorts it by certain criteria, then adds blank rows, and puts in Autosum for the various blocks of info. Problem occurs when a particular "block" of info is only 1 row. Any dirty little tricks to keep the macro running past this roadblock? The section of code looks like

ActiveCell.Offset(2, 0).Select
Do While ActiveCell <> "end"
If ActiveCell.Offset(-1, 0) = "TOTAL" Then
ActiveCell.Offset(-1, 1).Select
ActiveCell.Font.Bold = True
SendKeys ("%="), False
SendKeys ("~"), True
ActiveCell.Offset(1, -1).Select
ActiveCell.Offset(1, 0).Select
End If

View Replies!   View Related
Finding Duplicates (cell At The Top Show Row Number Of Duplicate)?
i have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).

At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.

View Replies!   View Related
Adding Numbers In A Single Cell
What is the function to add numbers in a single cell to show only a single number. I know it can be done but do not know the quick function to do it.

View Replies!   View Related
Copyright © 2005-08, All rights reserved