Highlight Duplicate Rows for an Action Number

[WPonto]

The attached workbook indicates 6 Action Numbers, each with multiple rows and eight columns. Each Action Number has a series of Step Codes. The task is to highlight when an action number has a duplicate Step Code with the same role. The attached workbook, row 1, indicates this task. I have highlighted the first Action Number showing the results I am needing to achieve. Many thanks.

[John Vergara]

Hi WPonto:

Use this formula into Conditional Formatting (CF), leaving active A3 cell:

=COUNTIFS($A$3:$A$44,$A3,$D$3:$D$44,$D3,$H$3:$H$44,$H3)>1 

Check file with the CF applied. Blessings!

[WPonto]

Thank you so much. If I want to take this one step further now, using the workbook you attached showing the CF, would it be possible to delete the Action Numbers that do not have any CF applied? In the attached workbook, for example, that would be rows 19 - 25 for Action 125692; rows 30 - 36 for Action 125716.

[John Vergara]

Hi again, Wponto!

The original question of this topic is solved. If you want to ask for anything else apart of your original question, please open a new thread. Blessings!

[bebo021999]

The original question of this topic is solved. If you want to ask for anything else apart of your original question, please open a new thread. Blessings!

Hi, I think keep going with this topic is better than open new thread, because he use same sample file, just added requirement.
HI WPonto,
Try to add below formula in column I, then fiter for "TRUE" then delete

Please Login or Register  to view this content.

For Ex2016 or older, confirm with Ctrl-Shift-Enter combination.

[WPonto]

A big "thank you" to bebo021999 as this is magic; will save me hours moving forward. My workbook has 15107 rows and Excel is not copying the formula down with ease; some rows the formula stops and inputs 0; I then repeat the process and wait; patiently. I also experience Excel freezes when I try to filter a column when there are excessive rows. In any event, the formula works despite my Excel issues (at work).

[John Vergara]

Hi to both!

I think formula way is very expensive (in terms of performance) for the machine. If you want another approach, I recommend Power Query. Check this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Action No.", Int64.Type}, {"Action Status", type text}, {"Date Submitted", type date}, {"Step Code", Int64.Type}, {"Code) Start Date", type date}, {"Code) End Date", type date}, {"Descr", type text}, {"Role Status", type text}}),
    GroupRows = Table.Group(ChangeType, {"Action No."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table}}),
    Comp = 
        let
            GroupRows = Table.Group(ChangeType, {"Action No.", "Step Code", "Role Status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
            GroupRows2 = Table.Group(GroupRows, {"Action No."}, {{"Count", each Table.RowCount(_), Int64.Type}})
        in
            GroupRows2,
    Join = Table.NestedJoin(GroupRows, {"Action No."}, Comp, {"Action No."}, "Join", JoinKind.LeftOuter),
    ExpandCount = Table.ExpandTableColumn(Join, "Join", {"Count"}, {"Count2"}),
    SelectRowsEqCount = Table.SelectRows(ExpandCount, each [Count] = [Count2]),
    RemoveColumns = Table.RemoveColumns(SelectRowsEqCount, {"Count", "Count2"}),
    ExpandCols = Table.ExpandTableColumn(RemoveColumns, "AllRows", List.RemoveItems(Table.ColumnNames(Source), {"Action No."}))
in
    ExpandCols 

Check file with M Code applied. If you change the table source, right clic in green table and update (like a pivot table). Blessings!

[WPonto]

Hi John. Thank you for an alternate approach as yes, it is using a lot of Excel resources. I need to take a Power Query course as your solution is beyond my experience and comprehension. I will keep this for future reference.