I have multiple columns / rows of data, some of which are duplicates.
Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.
I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.
So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.
I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.
Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.
For my job I have to take hundreds of codes and compare them to other codes. For example, in column A I'll have 453 codes, in column B I'll have 352 and in column 97. I want to find common codes for all three columns. Sometimes, I'll have just two columns and sometimes it's multiple columns. I have tried a few formulas but nothing works that well. Any formulas or MACRO
I have two lists and wish to compare them to identify duplicate values. I have used Duplicate Values in Conditional formatting but cannot find a way of ensuring an exact match. For example one list has the value 4150 and the other list has other values like 5641509 and 341508, both of which contain the string 4150 but are clearly not the same value. However, the conditional formatting is picking these up as duplicate values.
I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.
My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.
I was wondering how I could highlight duplicate words or in a text strings of 5 or more words, 6 or more words etc or a desired value within a document? It would be great if I could highlight the cell with duplicate word in yellow. Essentially I was hoping that this could be done using VB/macro with just a simple button loaded on the toolbar. I hope that my explanation is easy enough to follow. Below is an example:
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.
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
Any way of finding particular data from a data set and deleting the data from that field without using conditional formatting or remove duplicates?
I have used a vlookup to find the words that I needed I now need to find where the are located in my original data set and delete these so I am left with data that if I perform a vlookup on it will not bring back any words as they will have been deleted.
I use this code below to copy information from various sheets on to one sheet,
PHP Code:
Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim copySheet As Worksheet Dim pasteSheet As Worksheet Dim xrow As Integer, xcol As Integer
[code]....
i use a command button to run the macro, but i would like something added that if someone else pressed the button and ran it again that it would check to see if it had duplicated the previous line and if it did it would delete it leaving just the one record for that week.
I have two columns that is use to record last name first name. I need a macro that will find matching names in the columns. i have found some examples for searching one column but have not had any luck finding someway to do 2 columns. in the example below i need it to find and highlight The names Smith John.
I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.
When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go
Name-----number Dave 555-1212 Dave 555-1212 John 536-2343 Smith 423-2312
needs to become
Name-----number John 536-2343 Smith 423-2312
I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows,
I have a spreadsheet to track events (servicing, repairs, fuelling, etc) for my car - dates in Col A, events in B, mileage in C, prices in D.
I want to return the mileage when the next service is due, based on the mileage of the last service. Using VLOOKUP set to FALSE returns the first service mileage; set to TRUE, it returns random values. The events in Col B repeat in a random order (e.g. fuel, fuel, service, fuel, repairs, fuel, fuel, service).
I have a spreadsheet I use for creating golf score cards. I select the golfers from a drop down list and display a message if a golfer has already been selected. The ranges I want to use are A7:A15, A25:A33, A43:A51, A60:A68. My code is below and I am getting an error saying 'compile error, argument not optional' and UNION is highlighted.
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Union(Range("A7:A15, A25:A33, A43:A51, A60:A68"))) Is Nothing Then With WorksheetFunction
Wondering if there is a way, script-wise or formula-wise that will look through a table and find duplicates but not just any duplicates, it has to match a certain criteria.
For example, I have a column for UPC and Vendor#. I have my table sorted by vendor (all 159 of them in one table) I want to find out whether a UPC appears more than once for a vendor.
I have a lengthy excel spreadsheet in which I catalog all of my Karaoke song collection. I would like to be able to automatically locate (not delete) cells in which there are duplicate song titles. For my purposes just finding these items quickly so I may deal with them in a manner I've already developed is all I want to do. VBA, Macro or whatever would be appreciated. I'm a relative novice with macros and VBA.
For example row 12 may have the song title "Crazy" in column C and later row 1200 may have the same song title. Manually searching the spreadsheet for dupliates is way too time consuming so I think it can be seen why I'm looking to unlock the power of excel to do this for me.
What I have is a massive table of data (15k+ entries), which consists of 4 different tables merged together. I have a lot of duplicate entries. However, most of these entries have information in one or another columns that its duplicate does not.
What I need to do is:
Identify the duplicate entries (by part number, which is one of the columns).
Merge the duplicate entries. In the merging process, I need to carry information over, taking data that is currently in 2 or more rows and merging it into one row.
Here's an example:
column1 column2 column3 column4 row1 a . . d row2 . b row3 . . c
needs to turn into:
column1 column2 column3 column4 row1 a b c d
If I'm not explaining this well, I will do my best to clarify. My main question is, is there any way to automate this process (even partially)
I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..
I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.
I get them from different people.
Person A- 234, 569, 498, 849, 848,343,567,347 etc...
Person B- 432, 596, 677, 566, 565,433, 455 etc..
Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.
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)
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.........................
I have 12 worksheets, each with a list of email addresses in the 2nd column. These are all email addresses that did not respond by opening/clicking our newsletters. I'm trying to find how many times each person did not respond in the last 12 emails sent. I will delete those that consistently do not respond to our newsletters. I know how to do a simple vlookup but have never tried across multiple sheets.
So I guess I want it to check all sheets for each email address and count how many times it appears on these No Response email lists.
Column 1 is an email ID, Column 2 is an email address.
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.
I wonder if there is any easy way of findinig (numerical) duplicate entries in a column? Some cells are empty, in case this might cause a problem. I do not wish to delete duplicate rows automatically, just to find them. Why not just sort it? Because indirect referenceing is used where each row corresponds to a separate spreadsheet in the workbook. What I need is to find the duplicate so that I manually can erase one of the spreadsheets for the particular case and adjusting a reference list.
Magazine subscription list. How to highlight the customers that are already in the sheet if enter them again (renewal). Our list is like so....
ColA ColB ColC ColD ColE ColF First Last 123 Ave City State Zip
Is there a way to highlight the row if the info on ColA, ColB, ColE, and ColF all match? Sometimes the Street info is abbreviated or entered PO Box instread of P.O. Box and they wind up on the list a second time.
I'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.
