Find Range And Delete Duplicate Value In Specified Column With VB Macro
Apr 21, 2009
See attached doc "Find and Delete.xlsx". Inside, the value in F12 is a very important cell. Using the text value from F12, I first want to use visual basic code to find the range in column B because it will vary. This means the 'text words' column might have 50 words or it might have 2000. I have racked my brain trying to find a universal way to find a variable range.
Then I would like to search for the exact text value within this range in column
B. For example, in F12 is the text word 'windows xp registry cleaner' and you can see that this value is in B9. There will only be one exact match in column B.
I then would like the vb script to delete and remove this found value, then move left one space in column A to also delete the ID number associated with the text word. Then shift both rows up to fill the empty gap. I have tried numerous different codes but to be honest, I'm not really sure what I'm looking for. I've tinkered with find and replace, vlookup, match, find, search, and many more.
View 2 Replies
ADVERTISEMENT
Dec 17, 2009
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
View 3 Replies
View Related
Feb 20, 2009
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
View 2 Replies
View Related
Jun 11, 2013
I have made up 7 worksheets that are completely identical with exception of course to the data they contain. I have a master sheet that contains some of the data from each of the other sheets that will be shared between them. Things were working very well until I discovered that some data was duplicated on a couple of the sheets; and I am in need of adding more worksheets.
The formula I am using for this will not allow me to have more than the 7 worksheets. It is an IF statement array. So now I am wondering how will I be able to add these extra sheets. I would like to use my worksheet names in a range but have evidently done something wrong and could not get that to work before, ending up with this very long 'IF'. !Can worksheet names be used to create a named range?
Also, I would like to know if there is a way to prevent duplicate data on these sheets. I am using conditional formatting on each sheet now, but that of course only works on the current sheet.
View 4 Replies
View Related
Oct 18, 2009
I have a file here included which has 8 + columns. I normally have to go through each column and find when the references start and delete everything below it. If i could have a macro find where the references start in each column and delete on down that would be a huge time saver.
#REF!
#REF!
#REF!
#REF!
Thats the example of the reference i run into, i was thinking of a macro to find where the REF starts and delete that cell and everything on down.
View 11 Replies
View Related
Jun 6, 2014
I have multiple tables like the one in the picture and have to duplicate this code for different known ranges.
View 11 Replies
View Related
Aug 26, 2008
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
View 9 Replies
View Related
Mar 29, 2012
I found a lot of information on this but not what I need. I have 8 columns A - H. Column D has some duplicate numbers. I would like to find the duplicate numbers in column D (they are all one right after the other) and delete the entire row leaving only the first. I do not need to sum or anything, just delete the row with a duplicate number. If there are 2 or 3, I just end up with one.
View 2 Replies
View Related
May 25, 2006
compare and find exact matches b/w the data in columns J and K in the attached csv file. I would simply like all rows with exact matches to be hidden. I can simply change any code to reflect something like
Selection.EntireRow.delete
later if necessary.
View 3 Replies
View Related
Dec 8, 2009
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies
View Related
Mar 14, 2014
I understand filtering will accomplish this but a macro would save time.
My data is in Column "B" that contains ID#. If there is a duplicate ID# in column B, delete the row, but keep one. In addition, I do not want to delete blank cells in Column "B" and non number data.
Before:
XXXXX
35069
35070
35855
35855
XXXXX
35865
35867
After:
XXXXX
35069
35070
35855
XXXXX
35865
35867
View 5 Replies
View Related
Oct 14, 2011
How do i delete duplicate row?
This is a sample of how my spreadsheet looks like. i want to check for duplicates under cell B:F. delete if duplicate. to like this: ( i know that in the second 4007 repair, there are some data similar to the first 4007 repair. but lets assume the data is different. Cause i cant change it now.)
cell A consist of my header and should not be shifted. i do not want a result like this,
The data should look like the original except the duplicates are deleted.
The macro should check until the row Period_name. that is anything after period name should not be deleted. Also i want Period_name row to be position under the leftover data. like this:
View 3 Replies
View Related
Jul 25, 2007
I have a column of data where duplicate records appear, can I get a macro to review the column and delete the duplicate record, I need the line item number to still show although the serial number has been deleted, please see example below-
line_number Serial_number
14FM12A1QWINC-1A
14FM12A1QWINC-1B
14FM12A1QWINC-1C
14FM12A1QWINC-1D
14FM12A1QWINC-1E
14FM12A1QWINC-1F
14FM12A1QWINC-1G
14FM12A1QWINC-1H
14FM12A1QWINC-1I
14FM12A1QWINC-1J
14FM12A1QWINC-1Jdelete serial number in column B, keep line number 1 in column A
24FM12A1QWINC-2A
24FM12A1QWINC-2B
24FM12A1QWINC-2C
24FM12A1QWINC-2D
24FM12A1QWINC-2E
24FM12A1QWINC-2Edelete serial number in column B, keep line number 2 in column A
34FM12A1QWINC-3A
34FM12A1QWINC-3Adelete serial number in column B, keep line number 3 in column A
44FM12A1QWINC-4A
44FM12A1QWINC-4Adelete serial number in column B, keep line number 4 in column A
View 9 Replies
View Related
Jul 28, 2013
with deleting duplicate entries in a spreadsheet that contains two names, a code number and a date. All rows with a duplicate number in column “B†should be deleted. Write a micro code? I have a few thousand of entries that need to be knocked out.
A sample file is attached : Example.xls
View 5 Replies
View Related
Feb 23, 2012
I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.
View 9 Replies
View Related
Nov 17, 2006
I've got this macro which works well, but I have to edit it to change the column I want to check for dupes. I don't mind doing that, but now I have to share the macro with someone who is not comfortable changing the references in the code every time they run it.
As it is it checks column G:G (7) for dupes and deletes the row. Good.
I'd like for it to do the same thing, but for whatever column the active cell is in.
I can use this:
Col = ActiveCell.Column
in place of the "7" in the With Range part
but I don't know how to replace the "G1:G".
Sub DeleteDuplicatesColumnG()
With Application
.ScreenUpdating = False
.EnableEvents = False
View 9 Replies
View Related
Jun 12, 2008
creating a macro for an excel spreadsheet? The problem I am faced with is I need to reduce a xls file from a ticketing system at work that contains roughly 50,000 rows.
What I need to have done is reduce a Site Code column (column B) so that for each ticket entry there is only one occurrence of each user that worked on that ticket.What the macro should do is to look through column B and remove any duplicate rows for the same ticket number located in column A. once the macro gets to the next ticket number in column A I need it to start over on the duplicate check for column B.
I have a attached a xls file to this post to try and show what it is i am working on.
View 8 Replies
View Related
Nov 29, 2006
I have a selected row as my range. I want to check if a specific value ("*") repeats twice.
View 9 Replies
View Related
Dec 21, 2012
I am looking for some VBA code that will look through a column and delete all but the last duplicate value. The rows are not to be deleted. The duplicate also may not be consecutive (i.e they may appear in rows 2, 3 and 72 - the one in 72 would need keeping).
I did find the following macro which seemed to do the job until I realised it wasn't working if the values weren't consecutive.
Code:
Sub Dupe_Killer_Keep_Last()
Dim lrow As Long
Application.Calculation = xlCalculateManual
[Code]....
View 9 Replies
View Related
Oct 27, 2008
I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.
I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.
View 9 Replies
View Related
May 16, 2006
I use the following macro to delete the columns with duplicate dates in it. It works ok but when a date is duplicate more than once – say 4 time – it only delete 2 of the 4. I want it to delete all duplicates (entire column) and only leaf the last one to the right.
Sub DeleteExtraDates()
Dim c
For Each c In Selection
If c = c. Offset(0, 1) Then
c.EntireColumn.Delete
End If
Next c
End Sub
View 6 Replies
View Related
Jan 13, 2009
I have a spreadsheet that has thousands of rows of data with maybe 6 columns.
I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.
There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.
Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg
So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1
I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.
View 2 Replies
View Related
Oct 28, 2009
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
View 5 Replies
View Related
Mar 14, 2008
I am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.
View 3 Replies
View Related
May 31, 2012
I have two columns with aproximately 80,000 rows each lets say column A and D.
Column A has numbers and it is the most recent data extracted from a program. Column D has also numeric records most of which are also in column A.
I need a macro that will take each number in column D and compare it to column A when the macro finds that the number in D exists in column A it shoudl move the number on the same row on column B (right next to it)
If the number does not exist on on columna a It should start a list on column C.
View 5 Replies
View Related
Mar 26, 2008
I am trying to find the Duplicate value in Column E and save it in another tab.
View 9 Replies
View Related
Nov 28, 2013
I need a Macro to do the following:
In column A I have a list of Acronyms from A2:A90000 and more
In column B I have the corresponding acronyms spelt out from B2:B90000 and more
When I run the macro, it shoud detect the multiple/duplicate Acronyms and it's corresponding descriptions, DELETE the multiples/duplicates and move the cells up.
View 5 Replies
View Related
Dec 21, 2013
I have one sheet that have +/-5000 data in 4 table ... all table is fill with just only 2 column, first column is Code Number and second column is Quantity.
There are many duplicates of different entries in Column 1st in all table range and I need to add up their quantities to new table in new sheet...
This is for example:
(Sheet1 name="INPUT") (table1=A:B) (table2=E:F) (table3=I:J)
A B E F I J
AB123 100 ABC123 20 AA1 10
AC278 50 BCD547 30 AA2 10
AB123 30 FGE988 30 AB3 10
DE586 120 ABC123 50 AA2 10
RE158 80 BCD547 120 AA1 10
DE586 50 ABC123 70 AD4 10
Then I want to use macro vba to create new sheet(Summary Report),copy,paste,sort and sum all duplicate...
It should look like this
(Sheet1 name="Summary_Report")
A B E F I J
AB123 130 ABC123 140 AA1 20
AC278 50 BCD547 150 AA2 20
DE586 170 FGE988 30 AB3 10
RE158 80 AD4 10
View 9 Replies
View Related
May 2, 2008
I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?
View 9 Replies
View Related
May 20, 2013
I have 3 columns containing id, item, colour. I want to check for duplicate id's in the id column and where duplicates are found merge the colours into one cell, as follows:
iditemcolour
1245bookred
1245bookorange
1245bookblue
1456chairred
1367chairgreen
1876tablewhite
1876tablebrown
would become
iditemcolour
1245bookred,orange,blue
1456chairred
1367chairgreen
1876tablewhite,brown
View 6 Replies
View Related
