InputFile.xlsx (15.4 KB)
Hi,
I want to highlight the duplicate rows, I have attached the input excel file and screenshot of the expected output for reference.
Thankyou
Hi @Deeksha
Can you try the below
dt.AsEnumerable.GroupBy(Function(r) r(0).ToString).Select(Function(grp) If(grp.Count=1,grp.first(),grp.Where(Function(a) a(1).tostring.Trim<>"").first())).copytodatatable
InputFile.xlsx (15.7 KB)
Cheers!!
thankyou @lrtetala
Seeing your output i assume that the duplicates are removed.
But i want to highlight the duplicates not to remove them.
Sub HighlightDuplicateRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastColumn As Long
Dim rng As Range
Dim rowRange As Range
Dim cell As Range
Dim i As Long, j As Long
Dim isDuplicate As Boolean
' Set your sheet name here
Set ws = ThisWorkbook.Sheets("YourSheetName")
' Find the last row and last column with data
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Clear existing interior color
ws.Cells.Interior.ColorIndex = xlNone
' Loop through each row
For i = 2 To lastRow
isDuplicate = True
' Check if the row is empty
If WorksheetFunction.CountA(ws.Rows(i)) > 0 Then
' Loop through each cell in the row
For j = 1 To lastColumn
' Check if the cell value is equal to the corresponding cell in the first row of the same column
If ws.Cells(i, j).Value <> ws.Cells(1, j).Value Then
isDuplicate = False
Exit For
End If
Next j
' If all cells in the row match the corresponding cells in the first row, highlight the entire row
If isDuplicate Then
Set rowRange = ws.Rows(i)
rowRange.Interior.Color = RGB(255, 0, 0) ' Red color
End If
End If
Next i
End Sub
Hi @Deeksha
Sub HighlightDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim dict As Object
Set ws = ThisWorkbook.Worksheets("ListID_1") ' Change "ListID_1" to your sheet name
Set rng = ws.UsedRange
' Create a dictionary to store unique rows
Set dict = CreateObject("Scripting.Dictionary")
' Loop through each row in the range
For Each cell In rng.Rows
' Generate a concatenated string for the row
Dim rowString As String
rowString = ""
For Each c In cell.Cells
rowString = rowString & c.Value & "|"
Next c
' Check if the rowString already exists in the dictionary
If dict.exists(rowString) Then
' Highlight the duplicate row
cell.Interior.Color = RGB(255, 0, 0) ' Change color as needed
Else
' Add the rowString to the dictionary
dict(rowString) = True
End If
Next cell
End Sub
Hope it helps!!
Sadly, we do not have excel installed on server.
An UiPath based modelling could look like this:
OffSet | Int32 = 2
Read In
Calculate the Duplicates (Col A and B should allow us to rate duplicates)
arrDupIndexes | Int32 Array =
(From d In dtData.AsEnumerable
Group d By k1=d("Number").toString.Trim, k2=d("LOB").toString.Trim Into grp=Group
Where grp.Count > 1
From g In grp.Skip(1)
Select idx = dtData.Rows.IndexOf(g)).toArray
LastColLetter | String =
UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dtData.Columns.Count)
Set Range Color and Fix Formats:
Source: Excel.Sheet("ListID_1").Range(String.Format("A{0}:{1}{0}", (idx+Offset).ToString, LastColLetter))
Format set: red Filling
Excel.Sheet("ListID_1").Range(String.Format("A{0}", (idx+Offset).TOString, LastColLetter))
set Datatype format 0
Excel.Sheet("ListID_1").Range(String.Format("B{0}", (idx+Offset).TOString, LastColLetter))
set Datatype format 00000
We could use the ’ Trick and avoid the Reformattings for Col A and B
EDITED: fixed that the first row from a duplicated group will not be colored
then we can try
- Workbook Activites
- OpenXML oriented Code for the Range Color settings
can you elaborate 2nd point please.
Hi @Deeksha
Try the below way:
Code:
Sub HighlightDuplicates()
Dim lastRow As Long
Dim lastCol As Long
Dim rng As Range
Dim cell As Range
Dim dict As Object
' Find the last row with data in column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Find the last column with data in the range
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
' Define the range of data
Set rng = Range("A2").Resize(lastRow - 1, lastCol) ' Assuming data is in columns A to lastCol
' Create a dictionary object to store unique values
Set dict = CreateObject("Scripting.Dictionary")
' Loop through each cell in the range
For Each cell In rng.Columns(1).Cells ' Only consider the first column (Number column)
If dict.exists(cell.Value) Then
' Highlight the cells until the last column if the value is a duplicate
For Each c In Range(cell, Cells(cell.Row, lastCol))
If c.Value <> "" Then ' Check if cell contains data
c.Interior.Color = RGB(255, 0, 0) ' Red color
End If
Next c
Else
' Add the value to the dictionary if it's not a duplicate
dict(cell.Value) = 1
End If
Next cell
End Sub
Workflow:
Text file Path:
ApplyColor.txt (1.3 KB)
xaml:
Sequence67.xaml (12.6 KB)
Output:
InputFile (1).xlsx (15.5 KB)
Regards
thank you @vrdabberu
This is exactly what I wanted but I saw that if the data is blank in any of the cell it’s not getting highlighted, irrespective of the data is present or not the whole row should be highlighted.
currently the output is like this.
1 Like
vs.
So, Excel is available or not?
it’s just on one server, I did not want to restrict my process to run only on that server.
If we can do it without using excel application scope that would be excellent if not, then I can work on my infra.
better to let us know fixed requirements at the begin.
However, we shared with you above an UiPath-Excel related approach
@ppr
Sorry for the inconvenience
Hi @Deeksha
Code:
Sub HighlightDuplicates()
Dim lastRow As Long
Dim lastCol As Long
Dim rng As Range
Dim cell As Range
Dim dict As Object
' Find the last row with data in column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Find the last column with data in the range
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
' Define the range of data
Set rng = Range("A2").Resize(lastRow - 1, lastCol) ' Assuming data is in columns A to lastCol
' Create a dictionary object to store unique values
Set dict = CreateObject("Scripting.Dictionary")
' Loop through each cell in the range
For Each cell In rng.Columns(1).Cells ' Only consider the first column (Number column)
If dict.exists(cell.Value) Then
' Highlight the entire row if the value is a duplicate
Range(Cells(cell.Row, 1), Cells(cell.Row, lastCol)).Interior.Color = RGB(255, 0, 0) ' Red color
Else
' Add the value to the dictionary if it's not a duplicate
dict(cell.Value) = 1
End If
Next cell
End Sub
Text file Path:
ApplyColor.txt (1.1 KB)
Workflow:
Output:
InputFile (1).xlsx (15.6 KB)
Regards
@vrdabberu Thank you, it worked
1 Like
You’re welcome @Deeksha
Happy Automation!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.