I want to delete rows that are a subset of any other row. Not manually, as there are thousands of rows. For example, in the attached file, row 4 is a subset of row 5 (also row 12 of row 13, row 14 of row 15, and both rows 21 and 22 of row 23).
View 2 Replies
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 have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
I 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 have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
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.........................
I have a spreadsheet that has a long list of charges... some of those charges have multiple codes attached to them... when this happens I am wanting to group them together and let the user pick which one they want to view at one time.
Here is a very small example of what exactly I am referring to... with charge #14 I am wanting it to combine and let the user pick between 2565 and 2566. The problem is that the values can range and will not always be 2565 & 2566 and there is 10,000 + records similar to this.
test.xlsx
I am new to macro and just trying to learn. I have a spreadsheet with 20000 rows and 8 columns. I am trying to identify equal rows based on the values of columns C, D, E and F. then I need to separate equal bunches with a blank row. Then I need to copy the ID number from the first cell of column B of each bunch and paste it for the rest of the rows in that bunch. I have written the following code but this does not put the ID of the first cell in a bunch for the rest of the rows in that bunch.
[Code] .........
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.
The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:
Original Sheet:
"A" "B" "C" "D"
Teil1A11000
Teil1B21001
[Code]....
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedEveryone always want to get rid of duplicate rows.
Is it possible to create duplicate rows of all the existing rows?
I will be both apologetic and happy, though, if you can suggest a solution that does not require programming. If a programming solution IS required, I'd be grateful if you could give me a note or two on how to run the code if it is necessary. I'm competent with computers and I could program what I need in C++ if I had to, but I haven't used VBA before.
Here's my excel problem:
I have two long sets of data:
One is pressure from a transducer under water (in the river) recorded every 30 minutes. The other is pressure from a transducer above the water recording every hour.
I need to find the pressure due to water for each point (meaning I need to subtract the atmospheric pressure from each point of total pressure). From that, the height of water can be calculated, which will allow me to calculate discharge, or flow, of water at this spot in the river.
Because the atmospheric pressure is only recorded hourly, I need to duplicate each row of the atmospheric data worksheet so I can copy it over and make it the 'subtract' column.
Since I am working with years of data, there are thousands of rows, and the idea of duplicating each row manually is lame.
I tried to figure out a way for my calculation formula to use each row of the 'subtract' column twice (by making the first two subtract the value in E5, the second two use E6, the third pair use E7, and then dragging the auto-fill formula thingy down through the whole data set, but it doesn't work because the first one that gets auto-filled subtracts the value right next to it {..., D9-E7, D10-E7, D11-E11, D12-E11, ...} and so on).
So, like I said, I think i'll probably need to program it. If there was a way get the auto-formula-fill thingy to stop skipping back to the cell directly next to it as soon as it starts over the loop of copying, then that would be great.
Thank you for your help, and I apologize if this has been posted before, but all I could find were like a billion threads on deleting duplicate data.
Is there way, short of creating a macro, to have duplicate rows deleted in a spreadsheet?
View 3 Replies View RelatedI'm treating a set of data in excel in wich I have three long columns of values. I need to find all sets of three equal values in these columns (for instance, I need to find all lines with 1,1,1 or 2,2,2 and so on). How can I make a programe to do this?
View 12 Replies View RelatedIn my Excel 2003 worksheet I need a macro to search column B for duplicate entries. The data in column B is both numeric and string. If there are any duplicate entries, then I need the duplicate rows to be deleted.
View 4 Replies View RelatedI need a macro to duplicate a box i have in a sheet depending on how many rows the other tab has. In this example file ive included the macro i have now. With this macro i select A1 : R23 and press ctrl shift d, it asks me how many duplicates i want and i enter the number based from how many the test2 tab has. I would like a macro to read how many rows the test2 tab has and then automatically duplicate the box that many times . In other words automate it, instead of me having to enter the number ( this number changes depending on the file i have ).
Ive included a test file with intructions and my current macro, i have 2 tabs which need to be looked at.
i have a spreadsheet of 1200 rows of data and i need to duplicate these rows 5 times each. is there any way to easily do this and avoid manually inserting or copying/pasting all 1200 rows? all i can find is how to DELETE duplicate rows, not how to ADD them. see below for an example:
original spreadsheet:
1 A
2 B
3 C
desired spreadsheet:
1 A
1 A
1 A
2 B
2 B
2 B
3 C
3 C
3 C
VBA code that will delete all the rows where cell "C" is duplicated
I have look on the net and there is loads of complicated codes out there but I am looking for something fast.
I have a workbook called "Data"
In column "C" I have a part number
In column "F:H" I have a description
5432345 3445rt4 er4445ty ddews6789
4352345 sdwe33 dew345e jjkiii567899
1234566 qwert5 ertyu777 uiopl88888
1234566 eedrf44 yuikj776 ggggt44444
When I have a duplicate part number i want the second description to be populated in rows "I:K"
1234566 eedrf44 yuikj776 ggggt44444 eedrf44 yuikj776 ggggt44444
I am trying to delete duplicate rows and have had some success using a VBA from MR Excel threads. However, i would like to add multiple sheets to be compared. When i change the vba to use array, the next line fails with error 438, not a supported objectproperty method.....
Here is the code:
Sub CheckWest()
Dim LR As Long, i As Long
With Sheets("west")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If IsNumeric(Application.Match(.Range("C" & i).Value, Sheets("Removals").Columns("C"), 0)) Then .Rows(i).Delete
Next i
End With
End Sub
This checks the sheet "removals" column "c", any duplicates found in sheet "west" column "c", row is deleted. I would like to add additional sheets, how to correctly change this line of the VBA?
With Sheets(array("west", "east", "north"))?? I beileive this is what i tried and the next line errored.
I have a list that contains multiple data that I would like to merge in to one. There are varying duplicates.
Currently it looks similar to this:
Repid, Lname, Fname, States
000001, Doe John, CT
000001, Doe John, NY
000001, Doe John, GA
000050, Jones Mary, NJ
000050, Jones Mary, NY
000025, Presley Elvis, PA
000025, Presley Elvis, NY
000100, Doe Jane, NJ
I want to return the following:
Lname, Fname, Repid, States
000001, Doe, John, CT, NY, GA, NJ, CA, MA
000050, Jones, Mary, NJ, NY
000025, Presley, Elvis, PA, NY, GA, NJ
000100, Doe, Jane, NJ
How would I go about deleting duplicate rows?
I've searched the board already and I couldn't find this.
I have a sheet with different data in each column, song titles, writers, owners and so on.
say this information runs from column A to H down 10,000 rows, occasionally information is duplicated in certain rows, except for column A.
I need to be able to search the sheet and delete any extra rows that are duplicated.
So I'll end up with the exact data in each row only appearing once.
i'm looking for a vba script, that will HIGHLIGHT duplicate cells, by the row.
etc.. lets say i have
COL A COL B
a w
a s
e t
v t
If i click on Col / cell a and run the script, rows 1 & 2 will be selected.
I i click on Col / cell b and run the script, rows 3 & 4 will be selected.
i know there are heaps around that will change colour etc. but i just want to highlight?
I am trying to search aspreadsheet for duplicate entries and the hihe the duplicate entry rows so far i have
Private Sub worksheet_activate()
Columns("A:D").Select
Selection.sort Key1:= Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
this will delete duplicate entries but as the cells have formulas in with cell refrences i need to hide the duplicates instead but the change does nothing, i don't get errors but nothing is hidden
ive got a spreadsheet with records such as this
james - smith - leeds- 01535 - 26/02/1983 - bd21 6ls
steven - smith - bradford - 213789 - 28/01/1982 - bd33 5th
james - smith - leeds - 01535 - 26/02/1983 - bd21 6ls
steven - king - manchester - 213789 - 28/01/1982
andrew - wright - bingley - 36473 - 12/01/1981 - bd23 689
what im trying to do is to check all of the rows for duplicates in such a way that (if any row has the matching colums 1,2,5 and 6 then all but 1 of the rows will be deleted.
i have a few thousand entries and am just trying to get rid of all the duplicate rows. The problem is that the duplicate rows are not IDENTICAL for instance soime of them may have spelling mistakes hence i cannot just do a straight forward if row = row then delete.
