Macro to search and categorize items

alfordtp

Board Regular
Joined
Oct 3, 2008
Messages
62
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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?

ItemFamily
ABC12ABC
ABC14ABC
ABCD15ABCD
ABCD20ABCD
CDE12CDE
CDE25CDE
CDF20CDF
ZZ33ZZ
ZZ60ZZ
 
Upvote 0
Based upon current configuration, you can split the column with Power Query -- Non Digit to Digit as the delimiter.

Power Query:
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
AB
2ABC12ABC
3ABC14ABC
4ABCD15ABCD
5ABCD20ABCD
6CDE12CDE
7CDE25CDE
8CDF20CDF
9ZZ33ZZ
10ZZ60ZZ
Sheet2
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,595
Members
452,657
Latest member
giadungthienduyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top