Big Project - look ups and cross ref, grouping, HELP

[Bravo2003]

Here's one that i think may be un solvable!!

I have 3500+ products i need to group in to categories by using info within the description

Examples of the descriptions are:
10x10_5.5_xth_NN_Blue_PPP
10x10_7.5_xth_NN_Blue_PPR
100x250_5.5_sth_NN_Red_PNCB
75x250_1.5_xthBgd_Misc_PPP

There are roughly 130 group categories that I need to label them in to

So basically, Description is in Column C
In column AO I need to give each line a group code

• which will be based on the size details e.g. 10x10 the colour ref e.g Red and the code xth/sth part of the description
  • 45 size options
  • 4 colour options
  • 4 code options (xth/sth)

So from the 4 examples above I would give these group codes:
10x10_xth_Blue (or a code that this description would fall under like 10xB or 123, etc..)
10x10_xth_Blue
100x250_sth_Red
75x250_xthBgd_Misc
(you see the first two are the same group)

Now the question is… How do I do this???

 

[gaz_chops]

Or this one will work if 5 or 6 "_" appear

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"_",""))=6,SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))-1),"_","x",1),SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-1),"_","x",1))

 

[Bravo2003]

Try
=SUBSTITUTE(LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),2)))&MID(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),3)))&MID(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),5))+1,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),5))-1),"_","x",1)

This works great - only one last question if possible,

some items (where set up wrong in the past) have a space and extra numbers so my result is coming up
75x225_5th 210_Red
75x225_5th_Red

I need to have these match by dropping the 'space210' part...
Feasible?

 

[gaz_chops]

Highlight the column of data, select "Edit" (on top menu), the "Replace" enter Space then _ "Replace All", Test formula.

 

[Bravo2003]

Highlight the column of data, select "Edit" (on top menu), the "Replace" enter Space then _ "Replace All", Test formula.

Sorry, yes this works - almost - it now gives the section before the colour so one set of _xx_ early?
the other formula you gave me is making the 210 instead of colour.. again needs a little adjustment to which sections it's choosing

This is basically now a prime example of the orignal codes (now i've tweaked master data knowledge):
75_75_5.5_6th_310_NN_Red__PER_C
the first part 75_75 can be anything from 10_10 to 200_225 (so 2x2 or 2x3 or 3x3 characters)

then need the 6th part (again is 3 - 6 characters long)
then the Colour (as know 3-many characters long)

all master data should now meet this basic set up so can guarantee same _ sections (note the 2 _ part after colour!!)

 

[gaz_chops]

So in your example above, your result should be
75x75_6th_Red

If so try (i'm sure there must be a better solution!)

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"_",""))=6,SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))-1),"_","x",1),SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),7))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))-1),"_","x",1))

 

[Bravo2003]

So in your example above, your result should be
75x75_6th_Red

If so try (i'm sure there must be a better solution!)

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"_",""))=6,SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),5))-1),"_","x",1),SUBSTITUTE(LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),2)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),3)))&MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))+1,FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),7))-FIND(CHAR(1),SUBSTITUTE(B2,"_",CHAR(1),6))-1),"_","x",1))

Thank you - this now works (apart from the odd anomily! - but that is down to master data differences and shouldn't affect what I need to do for now)

May work on tweaking it and any alternative solutions once I've finished the next stage..

But a great big thank you for helping on this.

 

[gaz_chops]

You're welcome, hopefully someone will have a less cumbersome solution.

 

[Bravo2003]

You're welcome, hopefully someone will have a less cumbersome solution.

Just so I can get my head around this, can you roughly explain what the formula break down is doing?

 

[gaz_chops]

Ok,
"=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"_",""))=6" calcs the number of times "_" occurs, then uses 1 of 2 formula.
The 1st substitute with the last element "_","x",1)) changes the 1st "_" to an "x"
the other substitutes allow me to state which occurrence of "_" to look for, to calc how many characters are returned.

Hope that makes sense!

I have amended slightly, so a bit less cumbersome!

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"_",""))=6,SUBSTITUTE(LEFT(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",2)))&MID(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",3))+1,FIND(" ",SUBSTITUTE(B2,"_"," ",4))-FIND(" ",SUBSTITUTE(B2,"_"," ",3)))&MID(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",5))+1,FIND(" ",SUBSTITUTE(B2,"_"," ",6))-FIND(" ",SUBSTITUTE(B2,"_"," ",5))-1),"_","x",1),SUBSTITUTE(LEFT(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",2)))&MID(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",3))+1,FIND(" ",SUBSTITUTE(B2,"_"," ",4))-FIND(" ",SUBSTITUTE(B2,"_"," ",3)))&MID(B2,FIND(" ",SUBSTITUTE(B2,"_"," ",6))+1,FIND(" ",SUBSTITUTE(B2,"_"," ",7))-FIND(" ",SUBSTITUTE(B2,"_"," ",6))-1),"_","x",1))