I have a data set that I add information to weekly. I then add a value at the end of the row, example active or inactive. I've found that, when using the remove duplicate function, it does not always remove the second instance, resulting in a loss of that added data(active or inactive). So I guess my question is, can I somehow specify which instance of duplicated data is removed?
View 6 RepliesSuppose I have some data in a column like below
Blue
Red
Green
green
red
RED
BLUE
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
I would like to combine 3 string together but I would like to remove the duplicate found in any of the string. I attached an example with the desired result. I know =A1&""A2&""&A3 can combine the string, as I wan a space between each string. anyone can further edit my formula to remove the duplicate ...
View 13 Replies View RelatedIs there way, short of creating a macro, to have duplicate rows deleted in a spreadsheet?
View 3 Replies View RelatedI need to restructure a report that I have imported into Excel.
My columns are titled (from left to right):
Company Name, Account ID, Client Code, Billing Street, Contact Name, Contact Number
There are many rows per Company because every company has many contacts. As a result, for each company, the Company Name, Account ID, Client Code, etc. columns with their associated information in the fields underneath the heading are repeated unnecessarily.
How can I delete the extraneous fields of information (leaving one Company Name field filled out per company) without having to go in an manually deleting them (which would take hours)
I need some adv on how to remove lines that containing duplicate cells. My data is up the max lines of 65536.
I have data in column D that have duplicate value and I need to remove that duplicate line from the list ( thus leaving only unique value). Is there any macro that can do this?
Let's say I have two mailing lists, A and B.
A:
bob@bob.com
jeff@jeff.com
tom@tom.com
cindy@cindy.com
jose@jose.com
B:
fred@fred.com
jeff@jeff.com
angus@angus.com
cindy@cindy.com
chuck@chuck.com
Now I want to create list C, a modified version of list B, which is made up of everybody in list B, EXCEPT for those people who are also present in list A (in this case, Jeff and Cindy).
So basically list C should look like this:
C:
fred@fred.com
angus@angus.com
chuck@chuck.com
What would be the fastest and most efficient way to create list C?
I have trying to remove duplicate value for two columns which are not in order since long time. I have listed an example for the purpose. Two rows contains same information in different columns. I just wanted to have one row and remove the other with same information.
AAAA
500135500136
500136500135
500137500138
500138500137
500191500192
500192500191
500277500278
500278500277
500413500414
500414500413
Hi, I have a report that has multiple lines for the same job with notes of issues and solutions. Each line has a date assosiated with it.
What I need to do is to delete all of the notes apart from the first one. I have attached a sample of the data and highlighted the rows that need to be kept.
I have left the data in it's raw form as it is not always extracted in date order.
Can anyone help me out with a Macro that will sort the data and remove all rows apart from the earliest note per job number?
I have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...
Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State
etc...
It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).
I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).
Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.
I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.
I have a column that is filled with various car brands that a dealership might sell. The data in the cell may look like CHRYSLER; CHRYSLER; CHRYSLER which I would want to just say CHRYSLER. If the cell says CHRYSLER; FORD; CHRYSLER; FORD I want it to just say CHRYSLER; FORD.
View 1 Replies View RelatedCurrently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
I have a list of customers that contains duplicate records. These records ALSO include thier addresses WHICH may or may not be identical.
Example: Customer with LAST NAME in A1, FIRST NAME in B1 may be the same person as A2 and B2, however each record may have a different address in column C1 and C2 respectively.
I want to be able to filter out all duplicate customers based soley on the first and last name (data in columns A and B).
I'm trying to remove duplicate entries from a list of names & addresses.
What I'm after is some VBA code which will examine row by row the 'post code' field and the 'Full name' field and highlight/delete duplicate entries.
Its best illustrated by an example
Full Name Address Postcode
Mr C. Verougstraete 6 somone st, Leicester HY8 9YK
Mrs R. Brazier 8 high st HY9 9LK
Mr C. Verougstraete 6 somone st, HY8 9YK
The last record is obviously a duplicate of row 2 therefore will be removed.
I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.
However, I would now like to remove any duplicates in this cell; preferably while running the same code.
--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range
[Code].....
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
I have this data set which has customers D.O.B's. This a test data set for the MGM Grand Casino and some customers are under aged below 21 or not even born yet (basically wrong inputs). So ultimately I want to retain the row entries of the customers who were born between (1930 - 1992).
mgm_cleaned TEST ABCDEFGHIJKLM43928-Sep-20048-Sep-200405.4722000004-Oct-194944969-Sep-20049-Sep-200408.5720.25000004-Oct-1949459320-Apr-200423-Apr-2004010.9255000004-Oct-1949469420-Apr-200420-Apr-200409.2941000004-Oct-1949479121-Apr-200421-Apr-2004019.637.25000004-Oct-1949489221-Apr-200421-Apr-2004016.2941000004-Oct-1949499323-Apr-200323-Apr-2003010.96-10.25000004-Oct-1949509623
[Code] .........
I have got an Excel program that retrieves colunms of data from a Database using MS Query on the first sheet of the workbook. I then have some code that abstracts the data from from the first sheet into each seperate sheet depending on the number value in the Family Groups column. This is all done using a loop.
This all worked fine until I tried to insert another sheet which contains a couple of columns of static values which I dont wish to be affected by the VBA loop. when I now run the code it overwrites the contents of this sheet.
May question is how can I put some code into my loop to prevent it from updating this sheet in the work book?
I have 40,000 part numbers that range in style.
For example:
13538-AL
BC-DIB-13
13750U
13762-1
I want to take all the numbers that have the ending tag "-XX" that are LETTERED but NOT the numbered combo (just like example 13538-AL) and remove the tag to just have the root 5-digit number.
I have all the end tag combos I would like removed. SO if I needed -AL, -LG, -CG, -SS, etc. removed, I would need the formula that would find all the 5-digit numbers with those end tags and remove those tags, leaving the other numbers alone.
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100
101
102
105
Col D
6
6
5
5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
[Code]....
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100.........................
Would like to have a formula for removing duplicate row values from the attached sheet.
In simple, in the first row, Name1 and Name2 are repeating. And the requirement is to remove the repeated value "A" from Name2.
I have created a simple VLookup to view the data from one sheet to another.
its works fines, but i get the following error message if there isn't a value on the second worksheet, #N/A
how can i remove these, without deleting the formula, so it just be a blank field " ".
I have a excel sheet and inside certin cell are vales of "0". I would like to delete those 0 but keep the other values I have around them. Is there any way to delete only the "0" without going cell by cell by cell?
View 3 Replies View RelatedI'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
Formula:
[Code] ....
I require a formula that will show unique data in a specific rows or cells. Here are the data:
Raw Data
Col1
Row 1 A
Row 2 A
Row 3 B
Row 4 C
Row 5 C
The result should look like this (Note that the unique data are displayed in rows 6 to 8:
Col1
Row 6 A
Row 7 B
Row 8 C
I have a worksheet with columns A to E which hold the following data:- Week No, Budget Code, Budget Description, Name and Hours. However, as you can see in the attached spreadsheet there is not always a value in the hours field.
What I want to achieve is to automatically exclude any of the rows of data in the Pivot table that includes zero in the hours field (such as the rows highlighted in yellow) and also not to show if/when the pivot table is refreshed.
what I need for excel to automatically remove the highest and lowest TOTAL POINTS and create an average "Speed Rating" of the remaining 3 scores.
Is it possible to get Excel to do this?
I am working on a form right now for a shipping company in MS Excel. Anyways, I have a few cells formatted as text that have a starting value of "[Insert Comments Here]". What I am trying to do is have it so that IF the cell has the original value "[Insert Comments Here]" upon printing, THEN the cell's value is changed to "" or some other way of not making it print.
I would like to be able to do this both with cells and the "Label" form objects.