10-10-2019 09:53 AM
10-10-2019 09:53 AM
I am trying to determine which excel formula would be the best to create a spread sheet that will automatically input the dimensions for lumber into a given cell. The excerpt below shows the format I am attempting to achieve. In short, I need the first numerical figure in the "Grade/Description" column to automatically populate in the "Thick" column, the second numerical figure, followed by "X" to populate in the "Width" column, and finally, the last numerical figure, followed by "-" to populate in the "Length" column. Any tips or advice is greatly appreciated!
|GSWP Item #||Thick||Width||Length||Grade/Description||Units||Unit Size||Total Pieces|
|1630700||1X12-12 D GRADE WHITE||3||128||384|
|1320700||1X 6-10 D&BTR WHITE||1||256||256|
|1330700||1X 6-12 D&BTR WHITE||1||256||256|
|1220000||1X 4-10 ROUGH WHITE||4||200||800|
|1230000||1X 4-12 ROUGH WHITE||8||200||1,600|
10-10-2019 11:17 AM
@J-Griff I'd build a lookup table that you can reference.
|1||12||12||1X12-12 D GRADE WHITE|
|1||6||10||1X 6-10 D&BTR WHITE|
|1||6||12||1X 6-12 D&BTR WHITE|
|1||4||10||1X 4-10 ROUGH WHITE|
1X 4-12 ROUGH WHITE
10-10-2019 11:42 AMSolution
In the attached file, the formula for Thick in B2 is:
Moreover, the formula for Width in C2 is:
Finally, the formula for Length in D2 is:
10-10-2019 12:37 PM
@Twifoo, Thank you so much for your help! I was able to get the first line to work with all three formulas you provided. And as you can see in the attached excel document, the first two columns, thick & width, work perfectly, but I can't figure out why the subsequent lines in the length column are not computing correctly?
10-10-2019 01:06 PM
@Twifoo i see what you mean, but unfortunately, I can't change the pattern for the entries in column f because they are being populated from the data entry tab. I presume that extra space after the "X" is because the the figure goes from being two digits to a single digit. Either way, apart from manually altering each line, there is no way to change the format before the data is exported into excel.
10-10-2019 02:08 PM
In the attached version of your file, I wrapped all formulas with IFERROR. Given your explanation, I found it operose to ascertain the apposite formula. Nonetheless, I unearthed this formula in F7 for you:
10-10-2019 06:48 PM
@Twifoo Thank you for all your help. I was able to build upon your first set of formulas to achieve my desired result. By adding a another column and applying the formula, =SUBSTITUTE(SUBSTITUTE(G7,"X ","X"),"- ","-") to the Grade/Description column, I was able to eliminate the formatting discrepancies which allowed your original formulas to work perfectly. Thanks again for all your help! I would not have been able to figure it out without you!