VBA Delete Duplicate, Yet Retain Info From Adjacent Cell

Jul 16, 2007

I have a VBA macro that deletes duplicates rows, however I would like to retain the information from the duplicate row, just before deleting it. For instance, If I have these two columns: ....

View 9 Replies


ADVERTISEMENT

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

Compare Duplicate And Retain Only Latest One?

Feb 3, 2014

I have a list of values which also has duplicates. I want to remove the duplicates but after comparing it to the dates. Retain the latest date and remove the old dates Eg. there are two duplicates, one has a date of 31st Jan 2013 and the other one has a date of 25 jan 2013. I want to remove the duplicate which has the older date ( 25 Jan 2013) and keep the one which has the latest date.

View 2 Replies View Related

Autofill Only If Adjacent Row Has Info...

Nov 4, 2008

Below is my current code. I created this to work for a spreadsheet I had. Works mint. Then I use in another spreadsheet which is almost identical but has less rows. Everything works, but then im left with a bunch of extra rows that have DIV error. Rather than select range 2:34, is there a way to select the entire column, and autofill only if the column next to it has a number in it?

View 6 Replies View Related

Delete Rows Where Cell Doesn't Equal Adjacent Cell

Aug 16, 2007

I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).

View 3 Replies View Related

Delete Rows If Adjacent Cell Value Is From A Defined List

Mar 25, 2013

If I have a list in sheet1, of say 300 names, I need some code, that for a table on sheet2, from A1 to F500 will delete the contents of cells in columns B C and D, IF the value for column A in that row is from the list in sheet1

List of names: sheet1, A1 to A300

Table that needs changing: sheet2, A1:F500

cells to be deleted in columns B C D IF value in A corresponds to the list in Sheet1

View 3 Replies View Related

Macro Delete Row Based On Color And Adjacent Cell?

Mar 28, 2014

i am looking macro formula to delete row with 2 criteria :

- delete rows if yellow color background and based on adjacent cell (blank cell), otherwise keep rows when adjacent cell not blank

View 8 Replies View Related

Detecting Duplicate Cells Using Info From The List

Feb 5, 2014

I have a sheet containing lots of information (lists of flight information to and from all countries). So entries are duplicated in the entire sheet. For example LITHUANIA, USA, CHINA... appears lots of times. I only need to highlight particular countries.

I would just use conditional formatting, but maybe there is a quicker way to do that?

So there is a list of 50 countries in one sheet and in another sheet I got all the info. I need those 50 countries from the list to be highlighted in the info sheet (sheet has many columns and rows).

View 6 Replies View Related

Macros To Delete Entire Duplicate Row For Duplicate Values?

Aug 19, 2014

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.

Copy of OCL 2010 (3).xlsx‎

View 1 Replies View Related

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

Append Text In One Cell And Delete Duplicate Rows

Jul 14, 2009

I have data that covers multiple rows that I need to merge into one row.

Header:| InvoiceNumber | Type | Name
Row1: | 1000 | Payor | Doe, John
Row2: | 1000 | Payor | Smith, Mary
Row3:| 1000 | Payee| Jones, Henry
Row4:| 1000 | Payee | Jones, Bob

I need to get those four example rows down to one row such as:

Header | InvoiceNumber | Payor | Payee
Row1: | 1000 | Doe, John & Smith, Mary | Jones, Henry & Jones, Bob

The number of payor/payee can vary between 1 and several. I need some way to loop through and keep appending the names in one cell separated by a "&". And then deleting all the duplicate rows (based on InvoiceNumber).

View 9 Replies View Related

Delete Duplicate Rows Based On Cell/column Values?

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

Delete Duplicate Rows Based On Multiple Cell Criteria

Apr 21, 2007

I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).

How do I modify the code below to evaluate duplicate data in Columns A through D? .....

View 7 Replies View Related

Finding Duplicate And Or Similar Info Across Columns And Highlight (names And Addresses)

Jan 29, 2014

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.

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

Macro To Delete Info

Apr 2, 2009

There is a webcreated excel document that generates dates as the date of such as:

4/1/09 12:39:00 PM

I want to write a macro that will remove the date part and just leave the time. How would I write that? Times are in columns C and D and I want it to scan all the way down to row 600 and replace.

View 9 Replies View Related

Advising Users They Shouldn't Delete Info

Jul 11, 2008

I have a workbook where it is important that users do not delete any information in a particular range. But, I do not want to 100% block users from deleting the info.

What I would like to do is, if a user selects a cell within the range, and tries to overwrite or delete, that a warning box comes up to advise the user against changing the data. Is there a way to do this?

View 12 Replies View Related

Delete Rows But Save Info Based On Three Different Criteria

Aug 13, 2012

I want to delete the rows if they do not equal "TL" based on certain criteria, but save the info
then delete the rows if they do not equal "Fedex" based on certain criteria, but save the info
then delete the rows if they do not equal "LTL" based on certain criteria

After the information has been filtered it is supposed to load to a template giving monthly information for each of the above, but this is not working

VB:
If Load = "LTL" Then
Range("A2").Select
Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
For x = 2 To TotalRows
If Cells(x, 6).Value > 10000 And Cells(x, 1).Value = "FEDX" Then

[Code] ....

I am pretty sure that my problem lies within the parenthesis where it says Rows("2:" & x-1).Delete. Am I supposed to put something else there since I am deleting rows based on three different loads?

View 6 Replies View Related

Delete Duplicate Entries, Keep Last And Delete First

Feb 15, 2010

I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.

Sub Dupe_Killer()
Dim str As String
Dim str2 As String
Dim c As Integer
Dim i As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("SAMPLE").Select
rw = Cells(2, 1).End(xlDown).Row
'Sort Data by Date, Location & Number
Range(Cells(1, 1), Cells(1, 14)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _
, Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................

View 2 Replies View Related

Retain Formula In Cell

Feb 4, 2009

Is it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?

View 3 Replies View Related

Rule To Delete Data From Adjacent Cells

Feb 5, 2008

For the first grouping of data (Ex 1) I need to do the following; if the value in column J is less than 49 then delete the data in the corresponding row from column E to K. For the second grouping (Flx 1) if the value in column R is less than 49 then delete the data in the corresponding row from column M to S. I would like to be able to do this for all groups all the way to group Flx 5. All my sheets are set out in this way but I am not sure if I can do this using a formula or a macro (which I am not very experienced at writing).

View 3 Replies View Related

Delete Rows Based On Adjacent Blank Cells

Aug 8, 2008

I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub

View 2 Replies View Related

Join Cell Text & Retain Format Of Each

Oct 16, 2006

I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".

View 4 Replies View Related

Concatenate Formula To Retain Cell Formats

Jun 11, 2008

I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....

View 6 Replies View Related

Join 2 Times In Cell & Retain Time Format

Aug 23, 2007

I'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:

From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2

But as I'm sure you already know that yields this result:

From 0.708333333333335 to 0.916666666666668

View 3 Replies View Related

Retain Leading Zeros Passing Variable To Cell

Mar 14, 2008

I want to know how to index a drop down list to be used for calculations.

Up until now I was using

Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id

But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.

Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.

Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id

View 6 Replies View Related

Delete Duplicate Value

Jan 27, 2007

i've got a range of data. Typically there are columns that have the same value running for a few hundred lines before it gets to the next value. What i'm trying to do is create a macro that when i select a value in a column i would run the macro and it would delete all the duplicate values until it reaches the next value but making sure it only deletes that value in that specific column.

View 3 Replies View Related

How To Retain Leading Zeros In A Cell When Combine The Values Of Two Cells?

Oct 25, 2012

I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.

I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.

When I use this formula I get 353942. How can I retain these zeros?

View 1 Replies View Related

Excel 2007 :: Edit Links And Still Retain Cell Comments?

Jan 17, 2014

How can i break the links in a row of cells but still retian the cell comments thereafter.

At the moment, the comments dissapear after editing the links in excel 2007 that i am using.

Changing the display (foe cells with comments, show) under file-options-advanced has not really worked.

View 1 Replies View Related







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