Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro to delete duplicate rows based on condition Results

Hi All,

Could anyone help me to create a macro which can delete duplicate rows in column A if column B meets the following conditions:
1. If one of the values in B starts with 9 then delete the other one. (if there is more than one delete all duplicates)
2. If both (or more) values in B start with 9 then don't delete them.
3. If none of the duplicates in A have value starting with 9 in B - don't delete them.

Thanks a lot.

Check Macro.xlsx

Hi all,

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 11 and row 24:

If column 23,24,27,28,29,30 in row 11 = row 24 and column 22="" and column 5="X" in row 24 has to be deleted.

This condition has to be followed for all other rows in the excel used range.

I have this code which deletes the lines with column22="" or column 22<>"" and column 5="X" or column 5 <> "X".

I want to delete only when column 22="" and column 5="X".

I'm not sure how to modify this code.

Public Sub RemoveDuplicateComponent()

Dim xlCalc As XlCalculation

With Application

    xlCalc = .Calculation

    .ScreenUpdating = False

    .Calculation = xlCalculationManual

    .EnableEvents = False

End With

With Range("IV5:IV1000")

    .Offset(, -1).FormulaR1C1 = "=RC23&RC24&RC27&RC28&RC29&RC30"

    .FormulaR1C1 =
"=IF(AND(RC[-1]<>"""",COUNTIF(R5C[-1]:RC[-1],RC[-1])>1),""X"",0)"

    On Error Resume Next

    .SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete

    On Error GoTo ExitPoint

    .Offset(, -1).Resize(, 2).Clear

End With

ExitPoint:

With Application

    .ScreenUpdating = True

    .Calculation = xlCalc

    .EnableEvents = True

End With

End Sub

Any help will be helpful.

Thanks in Advance.

Hi there -

I used this macro to find the duplicates in column B:

	VB:
	
 KryDups() 
     
    ScreenUpdating = False 
    FirstItem = ActiveCell.Value 
    SecondItem = ActiveCell.Offset(1, 0).Value 
    Offsetcount = 1 
    Do While ActiveCell  "" 
        If FirstItem = SecondItem Then 
            ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0) 
            Offsetcount = Offsetcount + 1 
            SecondItem = ActiveCell.Offset(Offsetcount, 0).Value 
        Else 
            ActiveCell.Offset(Offsetcount, 0).Select 
            FirstItem = ActiveCell.Value 
            SecondItem = ActiveCell.Offset(1, 0).Value 
            Offsetcount = 1 
        End If 
    Loop 
    ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The duplucate cells are now red in color. (RGB(255, 0, 0))
How do I now code VB to delete the rows in column B where the cell color is red?

Here is some of the code that I tried:

	VB:
	
 DeleteConditional() 
    Dim c As Range 
    Dim rngRed As Range 
    For Each c In ActiveSheet.Range("b6:b" & Range("b9306").End(xlUp).Row) 
        If c < Date Then 'Change this line to match conditional formula
            If rngRed Is Nothing Then Set rngRed = c 
            Set rngRed = Union(rngRed, c) 
        End If 
    Next c 
    On Error Resume Next 
    rngRed.EntireRow.Delete 
End Sub 
 
>>>>>>>>>>> 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks to this forum for all of the help I have received!!

Hi all,

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.

Any help will be helpful.
Thanks in Advance.

I've searched far and wide but not finding any VBA that meets my requirements, so any help would be greatly appreciated.

I need to go through all rows and compare rows that all have the same value in one column (Number col in the e.g. below) but delete the rows with date outside of the next 30 days (Date col in the eg below).

Hope I haven't confused you but here is an example to demonstrate the above:

ID Date Number Name
***********************************************
1 13-Mar 16 Dan <------- Keep this row
2 15-May 16 Eric <------- Delete this row
3 3-Feb 16 Kevin <------- Delete this row
4 30-Apr 13 Mike
5 4-Feb 13 John

Appreciation in advance.

EDIT: Originally posted this as three criteria, but realized I only need two.

I'm creating a large table with call center data, and am trying to automate the process with VBA to delete unneeded information (keep the table smaller) as data gets entered. I need to include an error check based on two criteria to prevent importing data that already exists.

The two criteria must be used because separately they are not unique. They are: CallType and Date.

I can get so far as to set variables named: CallTypeV and DateV to the values being imported. What I can't figure is how to search for those two variables existing on the same row. In other words, I want to exit the macro (which I know how to do) if the macro finds DateV in column A and CallTypeV in column G - all in the same row. As the table grows, there will be literally hundreds of instances where two of the three conditions meet, but either zero or one instance of both in the same row.

I figure I could concatenate the two values and search for an exact match that way, but I prefer to not add an extra column to the spreadsheet. As it is, I'm already anticipating 17 columns and over 30,000 rows.

Any suggestions?

Hi,

I recently posted this question on here and received some help from a member, but we encountered a problem that could not be fixed and thus I need to ask for help again!! My knowledge of VBA is limited, and whereas I have understood what I have learnt so far I am not experienced enough to be writing code this long myself so if any experts out there can be help me I will be eternally grateful.

I have lists of people who have entered competitions which go through a data cleaning process and end up in the format of the file attached to this post. That part works fine, but what I need to do next is run a new macro to remove the duplicated entries so that where people have entered the competition more than once my final list should exclude the duplicates and display only 1 row per unique user.

I need unique users to be determined by a matching name AND email address (columns A and B). The address, phone and postcode columns can be ignored but do need to be displayed in the final list which should retain the original column structure.

This is the hard bit...

If someone is logged into the website when entering a competition the status of "Yes" is automatically assigned to the VIP column (G), but then some people will enter the competition repeatedly and if they are not logged in on any of these occasions a "No" for VIP is returned. My problem is that in the final non-duplicated list I need to know who is a VIP, and therefore if any of one persons has a Yes in that column, even if it is just one out of several entries I need the final list to retain that yes for their VIP status.

This was my original code that worked perfectly for removing duplicates but did not take into account the Yes/No condition:

Sub RemoveDuplicateRecords()
     '   Local Variables
    Dim rngData         As Range, cell  As Range
     '   Set the data range ( based on "Email Address" field )
    Set rngData = Worksheets("test").Range("B2",
Worksheets("test").Range("B2").End(xlDown)).Offset(0, -1)
     '   Sort the table by Name / Email Address
    rngData.EntireRow.Sort Key1:=rngData.Range("A2").Offset(0, 1), Order1:=xlAscending,
Key2:=rngData.Range("A2").Offset(0, 2), Order2:=xlAscending, Key3:=rngData.Range("A2").Offset(0, 0),
Order3:=xlAscending
     '   Remove duplicate entries ( Name / Email Address determine duplicate entries )
     ' For speed purposes use clearcontents and then resort list
    For Each cell In rngData
        If cell.Offset(0, 1) = cell.Offset(1, 1) And cell.Offset(0, 2) = cell.Offset(1, 2) Then
            If cell.Offset(1, 0) = "" And cell.Offset(0, 0) <> "" Then cell.Offset(1, 0) =
cell.Offset(0, 0)
            cell.EntireRow.ClearContents
        End If
    Next cell
     '   Sort the table by Email Address and then Name
    rngData.EntireRow.Sort Key1:=rngData.Range("A2").Offset(0, 0), Order1:=xlAscending,
Key2:=rngData.Range("A2").Offset(0, 1), Order2:=xlAscending
End Sub
And this is what another user wrote that seemed to work but when I tested it thoroughly there was a lot of "No"s returned for users who should have been "Yes":

Sub remove_dups()

Dim lastrow As Long
Dim i As Long, j As Long

    Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
   
lastrow = Range("A1").End(xlDown).Row

For i = 2 To lastrow

    If Range("A" & i).Value = Range("A" & i + 1).Value Then
    
        If Range("B" & i).Value = Range("B" & i + 1).Value Then
        
            If Range("G" & i).Value = "Yes" Then
                Rows(i + 1 & ":" & i + 1).Delete
            ElseIf Range("G" & i).Value = "No" Then
                Rows(i & ":" & i).Delete
            End If
            
            lastrow = lastrow - 1
            
        End If
    
    End If
                
Next

End Sub
--------------

In the example file "Catherine Paice" "Francis Healey" and "Wendy Price" should all be returned in the final non-duplicated list as YES because they have yes and no from various entries, but Julia Cook should have her final listing as NO as she was no every time.

I really hope this makes sense!!! The real databases are much much bigger 3k - 5k rows before duplicates removed, hence this being way beyond my knowledge...

Any help would be GREATLY appreciated - as time is running out for me to get this all sorted, and once this is done I have something even bigger to tackle!!

Susie

Hello Excel Forum Gurus,

I am attempting to make my first VBA macro to make life simpler when I get phone number updates from Verizon.

I have a list of the updated phone numbers, including whether the number is disconnected, or newly activated in a sheet called "VerizonUpdates"

I also have two blank sheets to process these entries into, "Processed" and "NotFound".

There are 4 possible outcomes for my information based on the two conditions:
(1) whether the phone number has already been processed and
(2) if it is a disconnect or newly activated line.

I want to take the first row of "VerizonUpdates" and get the phone number.
See if that phone number (column 11) matches any entries in "Processed"
If there is a match in processed, and it is a new activated number (signified by an "I" in column 6 in "VerizonUpdates)......overwrite the entire row in "Processed" where the match was found.
If there is a match in "Processed, and it is a disconnect number (signified by an "O" in column 6 of "VerizonUpdates")....delete the entire row in "Processed" where the match was found.

If there is NOT a match in "Processed" for the phone number, and it is a newly activated number.....I want to insert that row from "VerizonUpdates" to the top of the "Processed" sheet, pushing all its current entries down
If there is NOT a match in "Processed" for the phone number, and it is a disconnect number....I want to move the "VerizonUpdates" row to a new line on the "NotFound" sheet, signifying that the number was removed, but never on my Processed list

Here is my code so far: I am having particular difficulty with the foundRow, signifying the row where the match of the phone number from the Updates occured on the Processed sheet

Sub
ProcessVerizonUpdates()
'
' ProcessVerizonUpdates Macro
' This program reads the top line of the VerizonUpdates sheet, and tries to find a matching phone number in Col 11 in the
Processed list.  If there is a match it looks at the I/O values in Col 6.
' Match found
'   I - Overwrite foundRow in Processed with VerizonUpdates row
'   O - Delete foundRow in Processed

' Match not found
'   I - Insert the VerizonUpdates iCount row on the top of the Processeed List
'   O - Copy the VerizonUpdates line to the NotListed list
'
' Either case, remove the top line of the Verizon file, or update the iCount number
' Keyboard Shortcut: Ctrl+y
'
Dim iCount As Long
Dim iNum As Long
Dim foundRow As Long




    iCount = 1
    iNum = 11
    foundRow = 0
    statusRow = 0

Do While iCount < iNum
    
    On Error Resume Next
    foundRow = Match(VerizonUpdates!K1, indirect("Processed!K1" & ":" &
Index(indirect("Processed!K" & ":" & "K,counta(Processed!K" & ":" &
"K)"))), 0)
    If foundRow = 0 Then
        If A6 = "I" Then
        'overwrite match
        Worksheets("VerizonUpdates").Row(1).Copy Worksheets("Processed").Range(foundRow)
        Sheets("VerizonUpdates").Select
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Else
        'delete match
        Worksheets("Processed").Row(foundRow).Delete
        Sheets("VerizonUpdates").Select
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        End If
    Else
        If A6 = "O" Then
        'nothing, cut and paste to not found
        Worksheets("VerizonUpdates").Row(1).Insert Worksheets("NotFound").Row(1)
        Sheets("VerizonUpdates").Select
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Else
        'insert row at top of processed
        Worksheets("VerizonUpdates").Row(1).Insert Worksheets("Processed").Row(1)
        Sheets("VerizonUpdates").Select
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        
        End If
    End If
        
iCount = iCount + 1


Loop

End Sub
Thank you for your help,
Adam

Please see a copy of the above with working data in my attachment or at http://ifile.it/10e32ok/VerizonUpdatesMacro_01.xlsm

I think that my answer lies in transferring Excel functions like Match Index Indirect Address into VBA functions like range. And also exploring the properties of the VBA objects.

Hi all. I'm not an VBA person at all so I need your help on writing the code. I have this assignment and I need your help with the VBA coding. Here it is.

Description: we pull data from database (btw, the database is a mess) and paste it onto an Excel sheet. Here is the order of the columns, start with comlumn A and line 1 is reserved for the header, so the whole line 1 read from left to right is
WONUMBER (A), WORKTYPE(B), STATUS(c), DESCRIPTION1(D), JNO(E), PNO(F), ACTFINISHDATE(G), GROUPID(H), ASSETNUM(I), DESCRIPTION2(J), DAYAFTER(K), CALLBACK(L).

Primary key is WONUM, so no duplicate in this table or sheet.

Now the WORKTYPE can be: PM, PJ, PC, PI, PS, RP, AQ, and these we call it the main group of work type. Each of the has to associate to some asset. Another work type is RD. And RD is only generated/caused by one of type of work in the main group. Or put it in this way, RD's are work of a parent worktype that is PM or PJ or any that is in the main group.

Requirement:
1/ I have to run a Sort Data that sort by AssetNum (ASC) and FINISHDATE (ASC) which is easy and is the only thing I can do.

2/ After sorting, write a macro to bold all the lines that are not RD's and move any main worktype (PJ, PM, etc) that has no RD's followed to another sheet called, "no RD"),

3/ After that, look that the result after sorting, specifically look at each block of asset, there will be one or more of the work type in the main group (PJ, or PM, etc), we need to delete all the RD's that are before any PJ. This mean, in a block of asset, if there is none PJ before an RD, that RD need to be gone.

4/ The date after of RD need to be calculated and it is based on the date of the closest PJ above it.

Example with the attached photo: this is after the sorting. (I can't get to insert the image so here is the link to the photo:

http://i25.photobucket.com/albums/c7...e/untitled.jpg

- Line 2,3, and 24-28 should be deleted since there is no PJ for these RD to fall into.
- Line 29 should be moved to another sheet.

Please help me on this. Much appreciate

Hello! I have a spreadsheet (Sample1.xlsx) for which I need to a macro that will concatenate multiple row/cells values in Column G into a single cell/row value based on matching values in Columns A, B and C, then deleting the extra rows. Generally speaking, the matching could be just on column A, ID_NUM but we have had instances where the value in Column A/ID_NUM was duplicated, therefore as a safety, we need to match on all three. I've supplied a sample of what the desired result would look like (Sample2.xlsx). I've tried a few macros from other posts but can't seem to get it to where I need to be. Any help would be greatly appreciated!

Within the same spreadsheet, I have two workbooks. The first workbook holds all the data and the 2nd holds additional data that I want to append. However, I need to make sure that I don't add in any duplicates.

I want to create a macro that will delete rows from the append spreadsheet based on information in the data spreadsheet.

I looked through the tips and I saw there were a lot of macros written about how to conditionally delete rows, but it all seemed like the selection critera was based on data within the same workbook (autofiltering, blank, etc.)

I'm trying to keep the append spreadsheet as "untouched" as possible, except for when i delete rows since it's being imported and exported as CSVs and sometimes adding columns will add a bunch of extra commas in the CSV file.

I think using a countif statement would work? But that will probably only work if I go row-by-row in the macro, which might end up being too slow.

I hope I'm being clear enough explaining my question...

Any ideas?

Dear users,

I'm struggling to find an awnser to what i'm looking for.
I have on one settings sheet with a query tabel from a database 5 columns of budget data which look like this (total of like 500 rows). These rows could change every month:

Customer product Condition Sales Budget
BAS NPC COOP 1000 10
BAS NPC REBATE 1000 10
BAS MON COOP 500 50
ACT NPC REBATE 2000 20
ACT MON COOP 5000 50

And so on.

In another sheet: "the model" i have these same headers for which i want to create drop down boxes.
First the user selects incell A1 a customer (no duplicates should be allowed). Then the user should select in B1 the product that this customer has.
Third in C1 the condition is choosen. The fourth and Fith action should be automatically and in D1 en E1 the sales and budget should be shown.

These drop down boxes should only appear after the row has been activated, so by some kind of macro. After the data has been filled in the drop down box should be deleted and only the selected values should stay.

Can anyone give me a hint as how to do this? I've tried to find solutions but i'm not sure any are applicable to my desires.

Kind Regards,

John

Hi All, Following help from this forum, I use the named ranges and following code to change the row font colour in sheet 1, based on whether a duplicate ID Number exists in sheet 2 column A and the status in sheet 2 column C. The code works great, just as I need with the following exception...

Problem: The code runs during a format macro after sheet 1 is updated daily. During this format macro a new column is insert in front of column A. This changes the named range below to (NbRow =COUNTA(Sheet1!$B:$B) and the conditional formatting no longer works.

Is there a way to keep the NbRow Range focused on column A even after a new column is inserted before it. Or can the NbRow named range be reset to column A with another line of code. Any help or suggestions will be most welcome. Thank you in anticipation

Best regards
Jay

Named Ranges
Status =OFFSET(Sheet2!$C$1,0,0,NbRow,1)
Duplicates =OFFSET(Sheet2!$A$1,0,0,NbRow,1)
NbRow =COUNTA(Sheet1!$A:$A)


	VB:
	
Range("A1:A5000").Select 
With Cells 
    .FormatConditions.Delete 
    .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=(SUMPRODUCT((Duplicates=$I1)*(Status=""Open""))=1)" 
    .FormatConditions(1).Font.ColorIndex = 3 
    .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=(SUMPRODUCT((Duplicates=$I1)*(Status=""Closed""))=1)" 
    .FormatConditions(2).Font.ColorIndex = 32 
End With 
Range("A1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Sub ColumnMatchUp()

Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
    newsht.Name = "R1"
    Sheets("Sum1").Select
    Columns("A:B").Select
    Selection.Copy
    Sheets("R1").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("Sum2").Select
    Columns("A:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("R1").Select
    Columns("C:C").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("Sum3").Select
    Columns("A:C").Select
    Selection.Copy
    Sheets("R1").Select
    Columns("E:E").Select
    ActiveSheet.Paste
End Sub
I would like to append the following tasks to this macro.

1. Go to Sheet “R1” look at the contents in cell A1 then look for a duplicate of those contents in Column C and Column E.

2. When Column A has duplicates in Column C and Column E, copy that row of Column A and include Column B, then copy the matching row in Column C while including Column D, and finally copy the matching row of column Column E while including the same row of Column F and Column G. In other words A:B belong together, C:D belong together, and E:G belong together, but I want to group these Columns together based on the contents in Columns A, C, and E.

3. Create a new worksheet and name it “Final”

4. Select worksheet “Final” and paste Columns A:B, Columns C:D, and Columns E:G from sheet “R1”, which may have all been in all different rows, into the same row in sheet “Final”.

Go back to sheet“R1”and repeat the same process for every row in Column A. While including these two conditions

1. If Column A in sheet “R1” does not have a match in Column C and Column E, then leave it alone and move down to the next cell.

2. If Column A sheet “R1” has more than one match in Column C and Column E, copy only the rows in Columns C:D, and Columns E:G where the duplicates exist. Select sheet “Final” and underneath the 1st time the matching contents were pasted in C:D and E:G paste the duplicates. After all the duplicates have been pasted protect the same rows in Columns A:B so that no more data can be pasted into them.

Can you help?


No luck finding an answer? You could always try Google.