Hide Duplicate Rows

Feb 12, 2008

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

View 5 Replies


ADVERTISEMENT

Delete Duplicate Rows :: Duplicate Company Names

Dec 11, 2008

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.

View 5 Replies View Related

Delete Duplicate Cells Or Rows With A Duplicate Cell

Nov 1, 2007

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.

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

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]....

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

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.........................

View 9 Replies View Related

Hide Specific Rows In Sheet1 And Hide 5 More In Sheet2?

May 26, 2014

I have a macro in which i can enter the rows i want to hide.

If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].

Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.

View 14 Replies View Related

VBA - Button Command To Auto-Hide Rows With Value HIDE

Jun 9, 2013

Sub Button294_Click()

If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub

View 4 Replies View Related

Identify Duplicate Rows And Copy / Paste First Cell To All Cells Of Equal Rows

Apr 14, 2014

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] .........

View 10 Replies View Related

How To Dynamically Insert Rows With Duplicate Data Of Previous Rows

Oct 30, 2013

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

View 1 Replies View Related

Find Duplicates In 1 Col Then Copy Entire Rows Of These Duplicate Rows?

Feb 2, 2014

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]....

View 7 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows?

Feb 20, 2008

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.

View 4 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows

Feb 20, 2008

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:.......................

View 2 Replies View Related

How To Create Duplicate Rows Of All The Existing Rows

Jun 26, 2013

Everyone always want to get rid of duplicate rows.

Is it possible to create duplicate rows of all the existing rows?

View 5 Replies View Related

Hide Blank Rows Based On Values In Other Rows

Nov 3, 2006

I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

View 2 Replies View Related

Duplicate All Rows

Mar 6, 2009

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.

View 10 Replies View Related

Removing Duplicate Rows?

Apr 14, 2013

Is there way, short of creating a macro, to have duplicate rows deleted in a spreadsheet?

View 3 Replies View Related

Similar But Not Duplicate Rows

Jan 29, 2014

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 View Related

Find Duplicate Rows

Jun 28, 2009

I'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 Related

Deleting Duplicate Rows

Oct 14, 2009

In 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 Related

Duplicate A Box Depending On How Many Rows Another Tab Has.

Nov 16, 2009

I 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.

View 5 Replies View Related

How To Duplicate Rows In Excel

Jan 26, 2010

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

View 7 Replies View Related

VBA To Delete Duplicate Rows?

Aug 18, 2012

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.

View 2 Replies View Related

Moving Duplicate Rows

Dec 2, 2012

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

View 4 Replies View Related

How To Delete Duplicate Rows

Jan 23, 2014

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.

View 9 Replies View Related

Merging Duplicate Rows To One

Oct 18, 2005

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

View 9 Replies View Related

How To :: Delete Duplicate Rows?

Mar 21, 2007

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.

View 9 Replies View Related

Highlight Duplicate Rows

Oct 15, 2008

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?

View 9 Replies View Related

Remove All But 1st Of Duplicate Rows

Aug 12, 2008

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.

View 4 Replies View Related

Combine Duplicate Data From Different Rows?

Jul 25, 2012

I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.

I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls

View 9 Replies View Related

Marking Rows With Certain Duplicate Values?

Jul 8, 2014

So I am trying to mark certain rows as duplicates if their values in Columns J, AD and ABS(BD) match. J and AD contain Number letter Identification combinations and BD contains a value. I currently have a formula as shows that will mark all of these values:

[Formula]
=IF(AND(COUNTIFS(J:J,J15,BD:BD,ABS(BD15),AD:AD,AD15)>1, (SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,BD:BD,ABS(BD15)))<>ABS(BD15)),"Duplicate","")
[Formula]

The issue with this formula is that I do not want it to mark rows that look like this:

J - AD - BD
AA1 BB2 20
AA1 BB2 -20
AA1 BB2 20

I have a lot of rows that will appear like this. Their sum in BD equals the absolute value of the value in BD. I thought this would get picked up using the SUMIFS portion of my formula but it is not. I cannot find the problem.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved