Excel Filter and filter data need to add data in filtered rows

Help Studio
1

Hi ALl

I have an Excel sheet where some part numbers are duplicated. For each part number, I need to apply a filter, select the first occurrence, update all relevant rows for that part number, and then move to the next part number and repeat the process.

2

@vineelag

if you need to do on excel only

then use

  1. Read data and get all the unique part number dt.AsEnumerable.Select(function(x) x("ColumnNameOrNumberOfPartNumber").ToString).Distinct()
  2. Now loop on the part numbers
  3. use Filter excel to filter with part numbers
  4. Then use write cell with visible rows and also if you run in loop it would run for all rows
  5. use condition to check if it is first row or not and write data as needed

cheers

3

Hi @vineelag

Do processing everything in DataTable. You can group by PartNumber, then update rows , then write back the DataTable to excel.

  1. Read excel and store in Dt
  2. Then identify duplicate part numbers by grouping or use linq like dt.AsEnumerable(). Select(Function(r) r(“PartNumber”).ToString.Trim). Distinct(). ToList()

image
image598×238 3.92 KB

4. Then loop for above output i.e each part number.
5. Then for each part number, get all rows in seaparate dt1, get first row details, using this update all other rows in current dt1.
6. Continue same steps for all part numbers.
7. Then write this to workbook output.

4

Thanks everyone — I found my solution.
Sub UpdateRows(val As String, updateValue As String, sheetName As String, filterCol As Long, updateCol As Long)

Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

Set ws = ThisWorkbook.Sheets(sheetName)

' Get last row from the filter column
lastRow = ws.Cells(ws.Rows.Count, filterCol).End(xlUp).Row

For i = 2 To lastRow ' assuming row 1 is header
    If Trim(ws.Cells(i, filterCol).Value) = Trim(val) Then
        ws.Cells(i, updateCol).Value = updateValue
    End If
Next i

End Sub

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.