' Purpose: Creates a reference to a range combined from two ranges. MsgBox "Removed duplicates.", vbInformation Set drg = RefCombinedRange(drg, dict(uKey)) If dict(uKey).Value < vKey Then ' the value is greater If dict.Exists(uKey) Then ' unique name exists To remove duplicate values, click Data > Data Tools > Remove Duplicates. Finally, the delete range's entire rows are deleted in one go.ĭim wb As Workbook: Set wb = ThisWorkbookĭim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")ĭim lRow As Long: lRow = ws.Cells(ws.Rows.Count, uCol).End(xlUp).Rowĭim urg As Range: Set urg = ws.Cells(fRow, uCol).Resize(lRow - fRow + 1)ĭim dict As Object: Set dict = CreateObject("Scripting.Dictionary")ĭim drg As Range, uCell As Range, vCell As Range Windows Web In Excel, there are several ways to filter for unique valuesor remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced.Whichever is greater, remains, or gets set as the dictionary item, while the other one is combined into the delete range.If they are already added, then it checks the current value ( C) against the previous value of the item (which is a one-cell range).Loops through the column of the keys ( B) and if they are not already in the dictionary, adds them, as the keys, and the reference to the associated value cells ( C), as the items, to it.'as example, we start pasting data in row 2 column E If Not MyDict.Exists(Range("B" & i).Value) Then LR = Range("B" & Rows.Count).End(xlUp).Row 18 In Excel 2013 Select all Data Select Data Tab -> Remove duplicates Check 'My columns have headers' if your data has column headers. Set MyDict = CreateObject("Scripting.Dictionary") Just create Pivot Table, Columns A and B into rows section and Column C into Values section and Choose MAX instead of SUM You can use Pivot Tables to get the range you want and copy/paste. Sub RemoveDupe () Dim rCell As Range Dim rRange As Range Dim lCount As Long Set rRange Range ('C1', Range ('C' & Rows.Count). Where this data comes from, it will always have the same format and will always be placed in the same columns which is why if A,B,P are the same, then it is. Then copy/paste as values and delete original data 1 ACCEPTED SOLUTION BAPete Super User 10-14-2021 02:53 AM Hi Anonymous, In Power Query, multi-select (Ctrl+click) your Request ID and All Return Reasons columns then, on the Home tab, go to Remove Rows > Remove Duplicates. The Range of data is from A thru P and down and the 3 columns that would identify a duplicate are A (which is numeric, an ID), B (which is a date), and P (which is a Text string). You can accomplish this in different ways:Īnd formula in G3 is =MAXIFS($C$1:$C$26 $B$1:$B$26 F3 $A$1:$A$26 E3)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |