Gathering text variations

[Nehogymar]

Hi Everyone,
Probably you’re not going to understand what I’d like, but I try to explain. Please help if you could.
I’ve 4 column table, let say source, in worksheet „source”. These tables comes from different companies. Unfortunatelly they have similar products, but mostly those has slightly different names.



What I need, a macro which is gather the predefined text variatons by me of cells from column „A”, for eg:
HP Toner LJ5L/6L/3100/3150 CCA, black, 2500/sheet.
HP Toner LJ5L/6L/3100CCA, black, 2500/sheet.
HP Toner LJ5L/6L/RED 2500/sheet.
ASUS Motherboard/ and so on
and add the result for column „E” in result worksheet as specified item of category.



The main key is, I would like to determine what text variations(product's specifiaction) belongs for which category. Is it make sense??Maybe is it possible way, I just wondering.. generating somehow for e.g.: if text found "toner" then copy to column E in wroksheet result in same row...


If anybody have a best approach how can I solve this problem and abopt this method the rest of tables with macro or pivot table I would be appreciated. What is usual way? How can I reduce this tedious work??(in this case about 15000 row)
Thanks in advance

 

[hiker95]

Nehogymar,

We are not able to clearly view your pictures/graphics.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum:
sensitive data scrubbed/removed/changed
what you have and what you expect to achieve

To attach screenshots, see below in my Signature block:
Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Nehogymar]

Hi hiker thanks for quick response.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

The example.xls file location is:http://www.box.com/s/ibrceu93p3c7taxdgxl6<o></o>

Excel 2010<o></o>

So my problem again<o></o>

I’ve 4 column table, let say source, in worksheet„source”. These tables comes from different companies. Unfortunatelly they have similar products, but mostly those has slightly different names. In this screen you can see one of the possible company's product list.



What I need, a macro which is gather the predefined text variatons by me for cells of column „A”, for eg: <o></o>

<o></o>
Xigmatek NRP-PC602
X igmatek NRPPC
Xigmatek NK-602 ab cde
Xigmatek Kp-12456<o></o>

..decide and add the result for column „E” in "required result" worksheet as specified item of category. <o></o>


The main key is, I would like to determine what text variations(product's specifiaction) belongs for which category. Is it makesense??Maybe is it possible way, I just wondering.. generating somehow for e.g.: if text found "toner" then add column E value: "Inkjet" as category in worksheet "reqired result" in same row...<o></o>

<o></o>

Briefly i'd like to separate automatically the items and send them to the approprieate category.<o></o>

If anybody have a best approach how can I solve this problem and abopt this method the rest of companies tables with macro or pivot table I would be appreciated. What is usual way? How can I reduce this tedious work??(in this case I have about 15000 row)<o></o>

<o></o>

Thanks in advance, for your kind effort

 

[hiker95]

Nehogymar,

I do not think that you are going to have an easy task creating a lookup list of key words that are unique to a Product Name, for all your customer product descriptions.

If you did, then we could program a macro to search for the unique keyword(s) in column A, and assign the correct Category Path.


Here is what I have come up with:

Excel Workbook
	A	E	F	G	H	I	J	K	L	M	N	O	P
1				Unique Category Path	Total	Column A contains 56 raw data rows: DataFilter, Custom, Contains, "keywords"
2				Power Supply Unit	15	pin	power	cool	Xigmatek
3				found		2	6	10	3
4
5				Inkjet/DotMatrioxprinter	16	ink	jet	P/S/C
6				found
7
8				AMD Mainboard(AM3+)	7
9				found
10
11				LCD Monitor	18	LED	"
12				found		16	18
13
14
15
16	Product Name	Category Path
22	23" Samsung S23A950D LED *FREE 3D Glasses	LCD Monitor
28	20" Samsung S20B300B LED	LCD Monitor
36	23" Philips 234CL2SB LED	LCD Monitor
43	18.5" Samsung S19A10N	LCD Monitor
46	23" LG IPS236V LED	LCD Monitor
55	15.6" Philips W160EL1SB LED	LCD Monitor
56	18.5" LG W1943C	LCD Monitor
58	18.5" Philips W196V3LSB LED	LCD Monitor
60	21.5" Samsung S22A100N LED	LCD Monitor
61	21.5" Philips 226V3LSB LED	LCD Monitor
63	23" LG E2360T LED	LCD Monitor
64	23" Samsung S23A350H LED	LCD Monitor
65	23" Philips W237E3QPHSU LED	LCD Monitor
67	23" Samsung T23A350A LED *built-in Tvtuner & Spk	LCD Monitor
68	23" LG D2342P 3D LED *FREE 3D Glasses	LCD Monitor
70	24" LG E2441V LED	LCD Monitor
71	24" Samsung S24A350H LED	LCD Monitor
72	27" Samsung S27A550H LED	LCD Monitor
73
reuired result

 

[Nehogymar]

Thanks hiker95 your prompt answer, and your time to helping me!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Yes your screenshot looks like what I searching for. At least good starting point and maybe worth it to develop.<o></o>
Meanwhile I realized, what I want in excel it is called „price routing” what price comparison script does, and I wish to rmodeling how these process works in excel.

So what is next, could you suggest me some excel technik for. e.g. looping or could I try your script? Or your help as you wrote” we could program a macro” means your suppport has payment?

Thanks

 

[hiker95]

Nehogymar,

So what is next, could you suggest me some excel technik for. e.g. looping or could I try your script?

What I did on the above screenshot, was done manually, usind DataFilter, Custom, Contains, and manually trying (and keeping score/results) to find what "keywords" would work the best.

You would have to do the same, for each of the Unique Category Path's.

Once you have manually created the keylists associated with each Unique Category Path, we could create an array to search column A with the keywords, and create/assign the Category Path.