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 RepliesI have a file with 2 worksheets:
The "On" worksheet represents ALL of the students that LIVE on campus (freshman, sop****re, juniors, seniors)
The "JS" worksheet represents all the juniors and seniors going to school here
I need to know which of the JS live OFF campus.
So.....I need a macro that will compare the JS worksheet to the On worksheet and create a new worksheet called Off and populate it with those JS that are not in the On worksheet.
The "On" worksheet contains:
A B
Last First
The "JS" worksheet contains:
A B C D E F G
Last First Street Street2 City State NY
The new "Off" worksheet should contain the same columns as "JS"
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies View RelatedI 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%)
Hi i have the below code which runs on deleting duplicate code which i found in an excel manual. I am having some trouble as i want to convert the code so that it will run on all worksheets i have which are numbered such as 1,2,3,4 etc.
This is so it makes the workbook easy maintenance for when deleting or adding worksheets which happens quite regularly.
Application.ScreenUpdating = False
Range("B:B").Select
For Each c In Selection
If c.Value = Range("A4") Then c.Rows.Interior.ColorIndex = 36
Application.ScreenUpdating = True
I got this code so far that highligts the rows that match but range a4 needs to go down the column to a5, a6, a7....so it only checks the first cell in column a. I need it check all cells. Then i was going to delete the entire row in column B where it was not highlighted.
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....
Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..
View 1 Replies View RelatedI feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.
I know that there has been similar requests on this forum (I searched and read them) but thew were different.
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies View RelatedI need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
View 9 Replies View RelatedI have a requirement as shown below, what i want is to have another column which will display the priority of each case name as per the case id value.
The expected result is shown in TABLE 2, TABLE 1 is the requirement, I just want to have some excel formula or vba that can do it.
REQUIREMENT-
CASE_ID
CASE_NAME
REF_CASE_ID
PRIORITY
[Code] ..........
Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.
View 3 Replies View RelatedI 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 RelatedI've been looking for a solution to highlight all duplicates within a certain column across all worksheets in the workbook as the entry is made, no button to search for them. I have found bits and pieces, but can't seem to stitch them together to create what I am looking for, still very green with vba.
My workbook is a loading schedule at a warehouse, so there is a tab for each day (the date being the tab name, ex. '12.04.13'.) Tabs are continually added and removed to progress the calendar, and minimize file size. The column I am searching for duplicates in, is column L (or 12, however you wish to identify it.) If a duplicate value is entered, I want at the very least to highlight the value just entered, and the value elsewhere on the workbook, and if possible, have a msg box pop up stating the location of the other duplicate (or at least the tab (date) the duplicate is on.) The message box is more so for an in your face error check, with a built in GPS. Would it also be possible to only search from row 2 to 100, and ignore any further rows on each sheet?
The point of this, is to locate duplicates to make sure an appt has not been double booked, and both entries can be located to verify which entry has the correct information (carrier, delivery appt, etc.)
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
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:
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
I've searched the forums, and there are a lot of similar questions, but not quite what I'm looking for.
I have a large list of song titles, in one column. As an example, Song A is listed 4 times, Song B is 10 times, and Song C is 6 times.
Is it possible for Excel to go from this:
Song A
Song A
Song A
Song A
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song C
Song C
Song C
Song C
Song C
Song C
to this:
04-Song A
10-Song B
06-Song C
The numbers wouldn't necessarily have to be in the same cell, as long as I can sort the list from highest to lowest numbers while keeping the song titles matched with the correct number of instances.
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.
I'm in need of a formula, or another automated technique, to link a column of data from one worksheet to another. I have an input worksheet that I copy the raw data into that is formatted as follows:
COLUMN A
Row 1 12001
Row 2 12004
Row 3 12011
Row 4 12020
Row 5 13050
etc...
I need this import sheet to link to an output worksheet that would be updated automatically as new data is entered into the input sheet. However, I need the output sheet to copy the input values a specified # of the times and list them. For example, if the specified # was 3, it would be formatted as follows:
COLUMN A
Row 1 12001
Row 2 12001
Row 3 12001
Row 4 12004
Row 5 12004
Row 6 12004
Row 7 12011
etc...
I need the formula, or method, to be one that I don't have to continually re-enter as the data changes, as I have thousands of records to do this with. I've tried filling down, but autofill doesn't recognize an accurate trend. Is this something that I can accomplish by combining several fomulas, creating macros, or a combination of the both?
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.
View 4 Replies View Relatedwith 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
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 RelatedI'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
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.
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.