Macro to search and categorize items

[alfordtp]

I have a large list where I'm trying to put everything into family categories.

As an example, if I have the following list, a person could go through and identify that there are families of parts: ABC, ABCD, CDE, CDF, and ZZ. Sometimes the families are identified by the first 3 characters and sometimes its by the first 2 or 4 characters.

ABC12

ABC14

ABCD15

ABCD20

CDE12

CDE25

CDF20

ZZ33

ZZ60

In my real table, the family names could be identified by the first 1-6 characters. Is there a way to automate Excel to help identify and group these families? this could get very memory intensive, so I'm looking for suggestions on how to approach this. I've thought about extracting the first 6 characters, then counting how many other records also have those first 6 characters. If no other ones exist, then step down by one character and repeat the process. I realize this could be very intensive, but I'm not sure what else to do.

 

[alansidman]

What do you want the end result to look like. With the current configuration shown, you could just sort them alphabetically. But I am guessing there is more to this than a straight sort. Expand a bit on your explanation.

 

[alfordtp]

This is relatively easy for a human to identify the families by looking at the group of items. However, how do you explain that through programming?

Item	Family
ABC12	ABC
ABC14	ABC
ABCD15	ABCD
ABCD20	ABCD
CDE12	CDE
CDE25	CDE
CDF20	CDF
ZZ33	ZZ
ZZ60	ZZ

 

[alansidman]

Based upon current configuration, you can split the column with Power Query -- Non Digit to Digit as the delimiter.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Item", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Item.1", "Item.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Item.1"})
in
    #"Removed Other Columns"

Book8
	A	B
2	ABC12	ABC
3	ABC14	ABC
4	ABCD15	ABCD
5	ABCD20	ABCD
6	CDE12	CDE
7	CDE25	CDE
8	CDF20	CDF
9	ZZ33	ZZ
10	ZZ60	ZZ
Sheet2