Fill out columns based on reference table

[dommeehan]

Hi,

I want to clean some data I have received. It is webscrape data and all the data has arrived in the item column and I want to split this out to find the sex of the item (mens, womens, N/A) and what categories and sub categories they lie in.

I need a formula in the sex column that searches the item column to see if it contains "mens" or "womens" and then posts that as a value. If the cell doesn't contain mens or womens it should post N/A.

I then want a formula in category 2 column that searches the item column and matches a product word (cap, jacket, pack etc) to one in the reference table and posts it.

I then want a formula in category 1 column that searches the item column and matches it to an item in the reference table and posts which category the item is in. This value can be apparel, pack, sleeping bag, or accessory (see reference table).

Please let me know if that doesn't make sense.

I have filled in the first 3 rows, but as my data contains thousands of rows I'm looking for a quick way to do this.

Any help greatly appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Price	Sex	Category 1	Category 2	Category 3					Apparel	Pack	Sleeping Bag	Accessory
2	Blue denim pant mens		Mens	Apparel	Pant						Jacket	Rucksack	Sleeping Bag	Carabina
3	Down Jacket pink womens		Womens	Apparel	Jacket						Pant	Pack	Bivi	Headband
4	35L rucksack		N/A	Pack	Rucksack						Short	Waist pack	Shelter	Cap
5	Nylon short green										Tee			Hat
6	Nylon short yellow										Tshirt			Gaiter
7	White shell jacket										Boot			Beanie
8	Green shell jacket										Base-layer
9	Orange track pant										Mid-layer
10	White walking boots
11	Sleeping bag blue -5
12	Waterproof gaiter
13	Blue base-layer
14	Yellow gaiter
15	Green denim pant
16	Blue waist pack
17	6 Panel cap
18	25L rucksack green
19	Red base-layer
20	Sleeping blue -15
21	Black shell jacket
22	25L rucksack red
23	Double bivi
24	Yellow denim pant
25	Sleeping bag pink -15
26	White headband mens
27	Black headband mens
28	Green gaiter
29	Blue headband mens
30	Sleeping bag pink -10
31	Pink base-layer
32	Pink headband womens
33	Orange headband womens
34	Sleeping bag -25
35	Blue headband womens
36	Purple base-layer
37	Orange base-layer
38	25L rucksack orange
39	Sleeping bag blue -10
40	Snapback cap
41	Sleeping bag -35
42	Single bivi
43	Wool beanie
44	Group bivi
45	Single shelter
46	Double shelter
47	Sleeping bag pink -5
48	White base-layer
Sheet1

 

[Radoslaw Poprawski]

from where are u donloading data to column A?
is there any other info available?
From 5 to 8th row there is no male / female indicator.

 

[dommeehan]

from where are u donloading data to column A?
is there any other info available?
From 5 to 8th row there is no male / female indicator.

I scraped it from the web. This is a sample so I can see what formulas I need to create which I can then apply to my actual data.

If there is no mens or womens indicator it should post "N/A" as explained in my original query.

Thanks

 

[Radoslaw Poprawski]

I'm not sure if it's doable with the provided sample. there is no specific distinction in that column.

I don't see any way I could assign from C to F columns based on that column alone.
that's why I asked for the website from where you took data. Maybe there is a different solution or more data available.

 

[Trixterz]

Try using the formula below for cell C2 and if it does what you want, then apply the formula to the rest of the cells.

=IF(COUNTIF(A2,"*mens*")>0,IF(COUNTIF(A2,"*womens*")>0,"Womens","Mens"),"N/A")