Find and group/highlight almost identical or in series numbers in Excel

You can use this conditional formatting rule, applied to the cells in A2:A412:

=SUMPRODUCT(--(LEFT(A2,LEN(A2)-1)=LEFT($A$2:$A$412,LEN($A$2:$A$412)-1)))>1

This will find the cells where only the last digit is different (governed by the 1 highlighted in red). You could use a second rule (with a different colour) and change the highlighted 1 to 2 to find the cells where the last 2 digits are different.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can use this conditional formatting rule, applied to the cells in A2:A412:

=SUMPRODUCT(--(LEFT(A2,LEN(A2)-1)=LEFT($A$2:$A$412,LEN($A$2:$A$412)-1)))>1

This will find the cells where only the last digit is different (governed by the 1 highlighted in red). You could use a second rule (with a different colour) and change the highlighted 1 to 2 to find the cells where the last 2 digits are different.

Hope this helps.

Pete

Thank you, Pete.

I am getting below message while applying rule in conditional formatting.
Note - I am applying to range C2 - C29989 and have made changes to your given formula.

Attachment 834851

Regards.

is this is acceptable?

Originally Posted by sandy666

is this is acceptable?

Thank you Sandy.

I think you have used power queries and I am not that familiar with that. Not sure how to implement the same on my data.

Let me do some home work figuring it out.

Regards.

sure
this is a Power Query, here is an explanation so maybe it will help a bit

Let's break down each line:

1. let:
   This is the start of a variable assignment. It allows you to define and name a query with a sequence of steps.
   
2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]:
   This line sets the variable "Source" to the content of the table named "Table1" in the current Excel workbook. The "Excel.CurrentWorkbook()" function retrieves the current workbook, and then the "{[Name="Table1"]}" part specifies to get the table named "Table1," and "[Content]" extracts its content.
   
3. Text = Table.TransformColumnTypes(Source,{{"Numbers", type text}}):
   The "Table.TransformColumnTypes" function is used to transform the "Numbers" column of the "Source" table into the "Text" data type.
   
4. #"Duplicated Column" = Table.DuplicateColumn(Text, "Numbers", "NumbersD"):
   This line creates a new table by duplicating the "Numbers" column from the "Text" table and names the new column as "NumbersD."
   
5. #"Split Column by Positions" = Table.SplitColumn(#"Duplicated Column", "NumbersD", Splitter.SplitTextByPositions({8}), {"NumbersD.1"}):
   Here, the "Table.SplitColumn" function is used to split the "NumbersD" column in the "Duplicated Column" table based on positions. It takes the first 8 characters from the "NumbersD" column and creates a new column named "NumbersD.1."
   
6. #"Grouped Rows" = Table.Group(#"Split Column by Positions", {"NumbersD.1"}, {{"All", each _, type table [Numbers=nullable text, NumbersD.1=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}):
   This line groups the rows of the "Split Column by Positions" table based on the values in the "NumbersD.1" column. The "Table.Group" function groups the rows and creates two new columns: "All," which contains the original rows in each group, and "Count," which counts the number of rows in each group.
   
7. #"Added Custom" = Table.AddColumn(#"Grouped Rows", "List", each [All][Numbers]):
   This line adds a custom column named "List" to the "Grouped Rows" table. The "List" column contains the "Numbers" column from the "All" column, which represents the original rows in each group.
   
8. #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}):
   This line transforms the "List" column in the "Added Custom" table. It combines the values in each row of the "List" column into a single text value, separated by commas.
   
9. #"Sorted Rows" = Table.Sort(#"Extracted Values",{{"NumbersD.1", Order.Ascending}}):
   The last step is to sort the "Extracted Values" table based on the "NumbersD.1" column in ascending order.
   
10. in #"Sorted Rows":
    Finally, the result of all these transformations is the "Sorted Rows" table, which is the output of the entire query.