Bin Location - Odds and Evens

[Merchandiser]

Hi All,

I'm having difficulty writing a formula which will separate products by department.

We need to create a new Dept. column in column D, using the criteria below:

• Bin A - D to show Dept listed in F (Accessories, Footwear etc.)
• Bin E1 - E10 to show as "Hanging"
• E11-Even and E12-Odd (i.e. E11-02 and E12-01) to also show Dept listed in F
• E11-Odd and E12-Even (i.e. E11-01 and E12-02) to show as "Hanging"

Myself and a few colleagues have attempted using various formulas, but can't seem to figure it out.

[Merchandiser]

Apologies, i need a few posts before uploading the file

[Pete_UK]

Apologies, i need a few posts before uploading the file

No you don't - that applies to posting links (you need at least 10 posts to do that). You can attach a file like this:

Click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do NOT try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

[Merchandiser]

Thank you!

[JeteMc]

This proposal employs six helper columns which may be moved and/or hidden for aesthetic purposes.
Each column represents a rule from post #1
The A:D rule is populated using: =IF(C2="","",IF(CODE(LEFT(C2,1))-64>4,"",F2))
The E1:E10 rule is populated using: =IF(OR(C2="",H2<>""),"",IF(AND(CODE(LEFT(C2))-64=5,VALUE(MID(C2,2,2))<=10),"Hanging",""))
The E11-Even:E12-Odd rules are all populated using formulas similar to: =IF(OR(C2="",H2<>"",I2<>""),"",IF(AND(CODE(LEFT(C2))-64=5,VALUE(MID(C2,2,2))=11,ISEVEN(VALUE(RIGHT(C2,2)))),F2,""))
Column D is populated using: =H2&I2&J2&K2&L2&M2
Note that there are other Bin locations for which rules are not specified.
Let us know if you have any questions.