Find size data from Item Title column to create a Size column

[frewert]

Originally the workbook only contained Column A and the intention is to create Column C (Size Column).

Here is my workbook so far.

Size project method A.xlsm

drive.google.com

Here is an image preview.

https://imgur.com/a/nOEYEbC

I have highlighted the ones that aren't working as intended and put a column of what the desired result should be.

You will notice I have columns F and G that are valid sizes and conversions.

My goals now:

• Item Titles with (Number)X(Number) should return the first number. E.g., 32X30 should return 32.
• Item Titles with M/L are returning nothing. It should return L (the largest size)
• Item Titles ending in 0,2,4,6,8,S,M,L are not returning a result. Something to do with not being surrounding by the space character.
• Item Titles with 6S, 6R, 6M, 6L, 6T should return 6. (Should work with all numbers, not just 6 in example)

Here is the vba code used. (credit: reddit.com/user/arkangelshadow007)

Sub shoe_sizes()

Dim items As Range, validSizes As Range, conversion As Range

Set validSizes = Range("f2:f200") 'adjust ranges
Set conversion = Range("g2:g200")
Set items = Range("a2:a4200")

For Each Item In items.Cells

    For Each validSize In validSizes.Cells

        x = InStr(Item.Value, validSize.Value)

        If x > 0 Then

            Cells(Item.Row, 3) = Cells(validSize.Row, 7)

            GoTo next_item

        End If

    Next validSize

next_item:

Next Item

End Sub

Thank you!

 

[Akuini]

You should check records that returns empty result, there are many, see if they are actually should give some result. If so, we need to revise the code.

 

[frewert]

You should check records that returns empty result, there are many, see if they are actually should give some result. If so, we need to revise the code.

This is true. I have attached an image that addresses all the blanks.

 

[frewert]

I will return sometime tomorrow. If you are up for it, I have a few other columns I'd like to make.

Your skills are the best! Thanks a lot.

 

[Akuini]

Ok, I'll try to revise the code when I have time in a few hours.

 

[Akuini]

Can you update the list in col F:G first to include the rest of the criteria? Just exclude the part with "ties. scarves. wallets. hats. bags" because I will put these words in the code.
And it would be best if you can post your data using XL2BB TOOLS.

 

[frewert]

Can you update the list in col F:G first to include the rest of the criteria? Just exclude the part with "ties. scarves. wallets. hats. bags" because I will put these words in the code.
And it would be best if you can post your data using XL2BB TOOLS.

Ok I have updated F:G with XXS, 0XWP..3XWP, 1XL.
I'm not sure what you need me to do about Torrid. The brand has sizes:

Book1
	A	B
1	TORRID SIZES	EQUIVALENT SIZES
2	0	0X
3	1	1X
4	2	2X
5	3	3X
6	4	4X
7	5	5X
8	6	6X
Sheet1

Typically I always create the titles with both 2 and 2X for example, but you can see a few I did not do that.

Here is the updated sheet.

Size project method C.xlsm
	A	B	C	D	E	F	G
1	Item Title	Size				List of valid size entries	replace entries
2	Lands End Denim Shirt Womens M 10/12 Cotton Vintage Long Sleeve Button Up	M				7XP	7XP
3	Lululemon 11" Shorts Mens XL Black Zip Pockets Elastic Waist Drawstring Vintage	XL				6XP	6XP
4	LC Lauren Conrad T-Shirt Womens 2XL XXL Plus Crew Short Sleeve Cotton Floral	2XL				5XP	5XP
5	Dickies Long Sleeve Pocket Tee Mens 3XL XXXL Orange Relaxed Performance Wicking	3XL				4XP	4XP
6	Croft & Barrow Blouse Womens 2X Blue 3/4 Sleeve Knit Spandex	2X				3XP	3XP
7	Chicos Travelers Dress Womens 3 (US: XL) Black Acetate Spandex Sheath Sleeveless	XL				2XP	2XP
8	Old Navy Active 5.5" Shorts Womens 3X Plus Olive Camo Stretch Tech Zip Pocket	3X				1XP	1XP
9	Allison Daley T-Shirt Blouse Womens 1X Polyester Spandex Short Sleeve Colorful	1X				0XP	0XP
10	Old Navy NWT Wide Linen Blend Pants Womens Large Flax Beige Pockets High Rise	L				3XWP	3XP
11	Apt 9 Kohls Snake Print Top Shirt Blouse Cotton Stretch Womens Plus 1X	1X				2XWP	2XP
12	American Eagle Mom Short Womens 6 High Rise Zip Distressed Denim 2.5" Inseam	6				1XWP	1XP
13	D&Co Active Sleeveless 1/4 Zip Shirt Womens 1X Blue Cotton Spandex Tank Golf	1X				0XWP	0XP
14	Croft & Barrow The Classic Tee Womens 1X Cotton Black Polka Dot T-Shirt Crew SS	1X				26P	26P
15	Old Navy 100% Cashmere Sweater Full Zip Hoodie Womens XL (S, M) Long Sleeve	XL				24P	24P
16	We The Free People Denim Cut-off Shorts Womens 26 2" Blue Cotton Fringe	26				22P	22P
17	Melissa Paige NWT Petite Elastic Waist Skirt Blue Polyester Lined Womens PXL	PXL				20P	20P
18	Nike Dri Fit Shirt Womens Large Relaxed Long Sleeve Teal Heather Crew Raglan	L				18P	18P
19	Nike Performance Polo Shirt Mens 2XL XXL Red Striped Short Sleeve Polyester	2XL				16P	16P
20	JMS Just My Size NWT Blouse Polo Womens 2X 18W 20W Yellow 3/4 Sleeve Cotton	2X				14P	14P
21	Old Navy Boyfriend Mid Rise Blue Denim Shorts Womens 22 Cotton Stretch 2.5" Inse	22				12P	12P
22	Nike Golf Shorts Pleated Beige Polyester 10" Mens 33	33				10P	10P
23	Croft & Barrow Blouse Womens 2X Rayon Spandex Black Ruched Cuffs Pleated	2X				8P	8P
24	Woman Within Dress Womens 3X 30/32 Rayon Black Short Sleeve Floral	3X				6P	6P
25	Sonoma T-Shirt Womens 2XL XXL Yellow Paisley Print Cotton Polyester Short Sleeve	2XL				4P	4P
26	Athleta Salutation Stash Pocket II 7" Short Womens 2X Blue Nylon Lycra Spandex	2X				2P	2P
27	NYDJ NWT Capri Jeans Original Slimming Fit Womens 24W Denim Stretch	24W				0P	0P
28	Swannies Polo Shirt Mens Medium Blue Polyester Spandex Short Sleeve Striped	M				00P	00P
29	Levis 511 Denim Shorts Mens 30 Cotton Blue 11" Inseam Slim	30				P7XL	P7XL
30	Fila Sport NWT Golf Polo Shirt Mens 4XB 4X Big Gray Athletic Fit Polyester Wick	4XB				P6XL	P6XL
31	Under Armour T-Shirt Womens 2XL XXL Pink Threadborne Loose Fit HeatGear V-Neck	2XL				P5XL	P5XL
32	Lucky Brand Blouse Womens Medium Blue V-Neck Short Sleeve Elastic Waist	M				P4XL	P4XL
33	Perceptions Dress Womens 1X Floral Black Yellow Sleeveless A-Line Swing Stretch	1X				P3XL	P3XL
34	Red Hot By Spanx Full Slip Womens Large Spandex Nylon Soft Nude Lace Slimming	L				P2XL	P2XL
35	Knox Rose Kimono Blouse Womens M/L Rayon Open Front Floral Red Stain	L				PXL	PXL
36	Susan Graver Ultra Stretch Pull On Pedal Pushers With Pockets Womens XL	XL				XLP	PXL
37	Suzanne Betro NWT Shirred Fit & Flare Dress Black/White Crew Stretch Womens M	M				PL	PL
38	Tommy Bahama Orange Striped Polo Shirt Polyester Mens Medium M	M				LP	PL
39	Champion Tank Top Mens 5XLT Gray Spell out Cotton Blend T-Shirt	5XLT				PM	PM
40	Outdoor Research NWT T-Shirt Mens Large Echo Duo Tee Pewter Alloy Gray Polyester	L				MP	PM
41	Chaps Blouse Womens 3X Cotton Blend Beach Palm Trees Short Sleeve Blue	3X				SP	PS
42	White Stag Sport Collection Blouse Womens 22W 2X Blue Polyester Cotton	2X				PS	PS
43	Love + Joy Blouse Womens 3X Yellow Floral V-Neck Short Sleeve Elastic Waist	3X				PXS	PXS
44	Quacker Factory Snap Front Cardigan Womens Medium Yellow Floral Long Sleeve	M				P2XS	P2XS
45	Tahari 100% Cashmere Sweater Womens Small Beige Pockets Luxe Long Sleeve	S				7XB	7XB
46	Style & Co Woman Embellished V-Neck Blue/Green Top Blouse Nylon Womens 2X	2X				6XB	6XB
47	Carhartt Polo Shirt Mens 2XLT Tall Green Pocket Short Sleeve Loose Fit	2XLT				5XB	5XB
48	Maurices Cardigan Sweater Womens 2X Open Front Ruffle Dolman Rayon Blend	2X				4XB	4XB
49	Wilfred Merino Wool Sweater Womens Medium Brown Mock Long Sleeve	M				3XB	3XB
50	Duluth Trading Shirt Mens XL Trim Fit Multicolor Check Plaid Short Sleeve Button	XL				2XB	2XB
51	Old Navy Sweater Pullover Womens 2XL XXL Cotton Blend Classic Crew Gray	2XL				1XB	1XB
52	Croft & Barrow Button Front Shirt Womens 3X Cotton White Flamingo Pockets Stain	3X				6XLT	6XLT
53	Old Navy Mens XL Navy/Gray Gingham Plaid Shirt Long Sleeve Built In Flex Button	XL				5XLT	5XLT
54	Arcteryx T-Shirt Mens Medium M Gray Law Enforcement Armed Forces Crew	M				4XLT	4XLT
55	Falls Creek Dress Womens 2X Cotton Spandex Short Sleeve V-Neck Fit Flare Red	2X				3XLT	3XLT
56	Maurices Tank Top Blouse Womens 2X Rayon Ruffle Sleeveless Red	2X				2XLT	2XLT
57	Knox Rose Tank Top Blouse Womens Large Blue White Floral Sleeveless Rayon Cami	L				XLT	XLT
58	Torrid Button Front Shirt Womens 2 2X Blue Tencel Chambray Sleeveless Dolman	2X				LT	LT
59	Lands End No Iron Pinpoint Oxford Shirt Womens 14 Long Sleeve Cotton Floral	14				MT	MT
60	Ellen Tracy Linen Pants Womens XL Navy Blue Pull On Elastic Crop Capri Pockets	XL				7XL	7XL
61	Lands End Sport Tank Top Blouse Blue Stretch Womens 2X	2X				6XL	6XL
62	J Crew Pull On Pants Womens 10 Linen Cotton Striped Gray White Drawstring Pocket	10				5XL	5XL
63	Torrid Cropped Cami Linen Blend Top Womens 1 1X Blue Stretch Smocked	1X				4XL	4XL
64	7 For All Mankind Luxe Performance The Straight Mens Jeans 36X31 Stretch	36				3XL	3XL
65	American Eagle Vintage 7" Shorts Womens 14 Cotton Denim Blue Cut Off	14				2XL	2XL
66	Levis 525 Straight Perfect Waist Jeans Womens 6S Cotton Spandex Original	6				XL	XL
67	Shein T-Shirt White Tee Crew Short Sleeve Flag/Sunflower Stretch Womens XL	XL				1XL	XL
68	Eddie Bauer Adventurer 2.0 Skort Womens 8 Green Polyester Spandex Short Liner	8				L	L
69	J Crew Draped Pocket Tank Tunic Top Polyester Sleeveless Womens Size 4	4				M	M
70	Levis Premium Big E Wedgie Jeans Womens 30 Cotton Stretch Distressed Blue	30				S	S
71	Old Navy Boyfriend Flannel Shirt Womens 2XL XXL Red Plaid Long Sleeve Cotton	2XL				XS	XS
72	JM Collection Round Neck Beaded Blouse Top Shirt Cotton Modal Womens 3X	3X				2XS	2XS
73	Karen Scott Pullover Sweater Womens 2X Red Cotton Raglan Crew Knit	2X				XXS	2XS
74	Catherines Dress Womens 4X Short Sleeve Red Tiered Shift Lined Polyester 32W 30W	4X				7X	7X
75	Lands End Polo Shirt Womens 2X 20W 22W Red Polka Dot Cotton Spandex Short Sleeve	2X				6X	6X
76	Terra & Sky 4" Shorts Womens 2X 20W 22W Tie Dye Drawstring Elastic Waist Soft	2X				5X	5X
77	I Love Jesus And Naps T-Shirt Womens 18 20 2X 2 Elly And Grace Live And Tell NWT	2X				4X	4X
78	Sara Michelle Blouse Womens 2X Polyester Green Tiered Lined Short Sleeve	2X				3X	3X
79	American Eagle Womens 6 Vintage Hi Rise Festival Distressed Gray Shorts Denim	6				2X	2X
80	C Est 1946 Cato Blouse Womens 22/24W 3X Floral Polyester Spandex	3X				1X	1X
81	JM Collection Linen Shirt Womens 24W 3X Button Front Pocket Floral Tab Sleeve	3X				0X	0X
82	Maeve Womens Sz 4 Scallop Swiss Dot Blouse Top Blue Navy Rayon Crew Neck	4				XXXL	3XL
83	Terra & Sky Sheer Blouse Button Front Black Floral Long Sleeve Womens 3X 24W 26W	3X				XXL	2XL
84	Catherines Suprema Collection T-Shirt Womens 1X 18/20W Green Cotton Spandex V	1X				Large	L
85	Uniqlo Button Front Shirt Womens 2XL XXL Poly/Rayon Long Sleeve Flaw	2XL				Medium	M
86	River Trader Button Front Shirt Mens 2XL XXL Seer****** Cotton Pocket SS Yellow	2XL				Small	S
87	Chaps Blouse Womens 2X Polyester 3/4 Sleeve All Over Floral Print	2X				44W	44W
88	Cato Blouse Womens 18/20W 2X Brown V-Neck Short Sleeve Elastic Waist	2X				42W	42W
89	Eliane Rose Blouse Tank Womens 1X Plus White Teal Brown Viscose Spandex Tunic	1X				38W	38W
90	LL Bean Chamois Cloth Shirt Mens Large Regular Red Pockets Long Sleeve Cotton	L				36W	36W
91	Croft & Barrow Light Jacket Womens 1X Green Paisley Full Zip Polyester	1X				34W	34W
92	Banana Republic Pants Mens 34X28 Linen Cotton Straight Fit Beige Plaid Pro Hem	34				32W	32W
93	Shein Curve Fit Flare Dress Womens 2XL Pink Hearts V Wrap A-Line Short Sleeve	2XL				30W	30W
94	American Eagle Hi Rise Festival Shorts Womens 4 Cotton Distressed Blue Denim Zip	4				28W	28W
95	Protege Collection Cosby Sweater Mens XLT Made USA Black Crew Pullover	XLT				26W	26W
96	Belle Sky Blouse Womens 2X Blue Tie Neck Long Sleeve Polyester Ruffle	2X				24W	24W
97	White Stag NWT Draped Tankini Spandex Black Halter Womens 3X 22W 24W	3X				22W	22W
98	Boden Dress Womens 6P Cotton Spandex Short Sleeve Empire Tulip Jersey Gray	6P				20W	20W
99	Old Navy Active Powersoft Leggings Womens Large Go Dry High Rise Gray Heather	L				18W	18W
100	Chicos Weekends Blouse Tunic Womens 1 (US Medium) 8 Green Floral 3/4 Sleeve	M				16W	16W
101	Old Navy Button Front Shirt Mens 2XL XXL Blue Slim Built In Flex Pocket SS	2XL				14W	14W
102	The North Face Narrows Short Mens 34 Shorts Distressed Straight Twill 8" Inseam	34				One Size	One Size
103	Terra & Sky Blouse Womens 1X 16W 18W Cotton Short Sleeve Notch Neck Swing Orange	1X				All Sizes	One Size
104	LL Bean Button Front Shirt Womens Medium Reg Pink Stripe Long Sleeve Stain	M				60	60
105	Cato Blouse Womens 18W 20W Brown Paisley Split Round Hem 3/4 Sleeve V-Neck 2X	2X				58	58
106	Adidas Leggings Womens 2X Primegreen Aeroready Pink Abstract	2X				56	56
107	Sag Harbor Button Front Shirt Womens 1X Polyester Lined Short Sleeve Orange V	1X				54	54
108	Alice + Olivia Blouse Top Womens XS Stretchy Cropped Snake Print Long Sleeve	XS				52	52
109	LOFT Ann Taylor Blouse Womens XL Yellow 3/4 Sleeve Cotton	XL				50	50
110	Terra & Sky Button Front Shirt Womens 1X 16W 18W Blue Stripe Pockets SS	1X				48	48
111	Torrid Jogger Pants Womens 3 3X Green Cargo Rayon Stretch Pull On Elastic	3X				46	46
112	Guess Linen Pants Cropped Capri Black Drawstring Womens 32	32				44	44
113	Eddie Bauer T-Shirt Mens 2XL XXL Cotton Black Long Sleeve Mock	2XL				42	42
114	Old Navy NWT Slim Jeans Mens 38X31 Black Wash Cotton 360 Stretch Zip Mid	38				40	40
115	Muk Luks Womens Cabin Sock NWT Black L/XL 8-10 Slip Resistant Warm Cozy	XL				38	38
116	George Long Sleeve Button Front Shirt Mens 2XL XXL Blue Pink Check Plaid Cotton	2XL				36	36
117	Lands End Tunic Blouse Womens XL 18 Gray Pockets Long Sleeve V-Neck Cotton Blend	XL				35	35
118	CJ Banks Button Front Blouse Womens 2X Linen Rayon Black Pockets Jacket	2X				34	34
119	Talbots Pants Womens XLP Petite Plaid Black Blue Spandex Leggings Pull On	PXL				33	33
120	Blair Pull On Capri Pants 24W Teal Pockets Elastic Waist Cotton Polyester	24W				32	32
121	Eddie Bauer Parka Coat Womens 1X Orange Cotton Polyester Hooded	1X				31	31
122	Levis Denim Shirt Mens XL Cotton Long Sleeve Pockets Charcoal Wash Button Up	XL				30	30
123	Levis 502 Taper Fit Mens Jeans Big E Premium (Actual 33X28) 33X30 Cotton Spandex	33				29	29
124	Lucky Brand Button Front Shirt Mens Large Linen Orange/Red/Yellow Plaid SS	L				28	28
125	Paradise Found Made In Hawaii Hawaiian Shirt Rayon Camp Mens Large L	L				27	27
126	Lucky Brand Henley Blouse Womens Large Taupe Short Sleeve Dolman Pockets	L				26	26
127	Eddie Bauer Polo Shirt Mens 2XL XXL Orange Pima Cotton Cocona Polyester SS	2XL				25	25
128	Verve Ami Cardigan Sweater Womens 3X Rayon Blend Red Open Front Long Sleeve	3X				24	24
129	J Crew Mercantile Slim Flex Shirt Mens Large Cotton Spandex Plaid Long Sleeve	L				22	22
130	Grayson The Hero Button Up Shirt Womens Size 3 (M) Bicycle White Red Long Sleeve	M				20	20
131	Croft & Barrow Tunic Blouse Womens 1X Rayon Blue Floral Smocked 3/4 Sleeve	1X				18	18
132	Livi Dress Womens 18/20 2X Blue Tie Dye Rayon Spandex Maxi Sleeveless	2X				16	16
133	JMS Just My Size Blouse Womens 2X 18W 20W Polyester Flare Sleeve Purple Pink	2X				14	14
134	Great Northwest Indigo Blouse Womens 2X White Embroidered Short Sleeve	2X				12	12
135	C Est 1946 Cato Blouse Womens 26/28W 4X Yellow Pullover Puff Sleeve 3/4	4X				10	10
136	Eileen Fisher Jumpsuit Womens Large Black Rayon Spandex Pockets Wide Leg Zip	L				00	0
137	Nike Dri Fit Running Shorts Womens 2X Brief Lined Polyester Black Teal	2X				8	8
138	Bonobos Button Front Shirt Mens XL Short Blue Cotton Seer****** Standard Fit	XL				6	6
139	Chicos Maxi Dress Womens 0 (US Small, 4) Sleeveless Polyester Spandex Lined	S				4	4
140	Avenue Shirt Womens 22/24 Purple Crinkle Long Sleeve Button Front Top	24				2	2
141	JM Collection Button Front Shirt Womens 24W 3X Cotton Purple Floral Sequin V	3X				0	0
142	Cato Button Front Shirt Womens 18/20W 2X Orange Floral Cotton Short Sleeve	2X
143	Lane Bryant Womens 24 Button Front Shirt Tie Wrap Belt Blue Cotton Stretch	24
Sheet1

 

[frewert]

I spoke about wanting a few other columns. These are: Category, Gender, Condition

Condition would simply be: If the title contains "NWT", then it is New. If it does not contain "NWT" it is Pre-owned.

Gender is: Womens, Mens, Boys, Girls, Unisex, and blank if none of those are found.

I will begin writing a reply explaining Category next.

 

[frewert]

Here is info about building the Category column

Category	transform	notes
Top	Tops
Blouse	Tops
Tee	Tops
Tunic	Tops
Kimono	Tops
T-Shirt	Tops
Shirt	Tops
Tank	Tops
Dress	Dresses
Shirt Dress	Dresses	"Shirt Dress" = Dress, and "Dress Shirt" = Tops
Jumpsuit	Jumpsuits
Romper	Jumpsuits
Overalls	Jumpsuits
Jeans	Jeans
Pants	Pants
Leggings	Pants
Jacket	Jackets
Vest	Jackets
Sport Coat	Sport Coats
Blazer	Sport Coats
Suit Jacket	Sport Coats
Waist Coat	Sport Coats
Sweater	Sweaters
Cardigan	Sweaters
Shorts	Shorts
Skirt	Skirts
Skort	Skirts
Polo	Polos	We need a way around the problem of when it's the brand "Polo Ralph Lauren" or "US Polo Assn" but is also in the polo category
Rugby Shirt	Polos
Hoodie	Hoodies/Sweatshirts
Sweatshirt	Hoodies/Sweatshirts
Hat	Misc
Wallet	Misc
Bag	Misc
Scarf	Misc

I left off a few misc items because they might be a problem. "Tie" could be a misc item but there are Tops and Dresses with the word "tie" in the title. Maybe "Tie" would only qualify for Misc category IF it was a Mens title.

 

[Akuini]

Which Excel version are you using? Please, update your profile to show which version of Office you are using.

I need to understand more about your data:

1. Approximately how many rows does the actual data have? More than 10,000 rows?
2. Does your data consistently use the same letter case? So, you don't have the same words with different cases, such as Tie and tie.
3. Regarding the Category column, what should happen if there are multiple matches?

 

[frewert]

Which Excel version are you using? Please, update your profile to show which version of Office you are using.

I need to understand more about your data:

1. Approximately how many rows does the actual data have? More than 10,000 rows?
2. Does your data consistently use the same letter case? So, you don't have the same words with different cases, such as Tie and tie.
3. Regarding the Category column, what should happen if there are multiple matches?

Office 365

1. More than 10,000 rows. I create about 3500 listing per year and I expect to double or triple that next year. The data comes in on CSV reports and then I transform the data via Power Query.
2. I capitalize every word in the titles consistently (exceptions would be a typo).
3. I don't think there will be any multiple matches. If so, maybe 1% of listings. The only thing I could think of is "Shirt Dress" and "Dress Shirt" which mean different things depending on the specific order. There could be multiple matches for the same category. E.g., something like Cardigan Sweater, in which case it would be called Sweater for Category.