Liliquestionsxlxs
New Member
- Joined
- Mar 2, 2022
- Messages
- 17
- Office Version
- 2021
- Platform
- Windows
I have this spreadsheet that uses the data from the Unit column to say which part type would that section of the part number be qualified for. As you can see on columns D to N that's the part numbers any of the sections can be qualified for. Some times as you can see in rows 3-12, rows 4-19 and 21-25 sometimes the same section can be qualified for 1 or two other part types.
Each column has an if then equation
Column E=IF(AND($C3>=3,$C3<=5,$C2>=3,$C2<=5),"C-2","")
Column F=IF(AND($C3>=5,$C3<=8,$C2>=5,$C2<=8),"D-2","")
Column G=IF(AND($C3>=8,$C3<=10*1.025,$C2>=8,$C2<=10*1.025),"A-2","")
Column H=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"B-2","")
Column I =IF(AND($C3>=3,$C3<=4.5,$C2>=3,$C2<=4.5),"P-10","")
Column J=IF(AND($C3>=8,$C3<=10,$C2>=8,$C2<=10),"P-25","")
Column K=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"P-30","")
Column L=IF(AND($C3>=3,$C3<=5,$C2>=3,$C2<=5),"3-1","")
Column M=IF(AND($C3>=5,$C3<=8,$C2>=5,$C2<=8),"5-1","")
Column N=IF(AND($C3>=8,$C3<=10,$C2>=8,$C2<=10),"8-1","")
Column O=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"10-1","")
If I keep on using the same IF/AND function row 13,20 and the very last row won't get qualified into any part type.
I know there is a better way to do this and even maybe create 1 single column that I could call "Part Types it Qualified for" and have 1 or more part types the section qualified for in a single cell.
I'm looking for help to get this optimized and make it definitely more efficient.
Thanks!
Each column has an if then equation
Column E=IF(AND($C3>=3,$C3<=5,$C2>=3,$C2<=5),"C-2","")
Column F=IF(AND($C3>=5,$C3<=8,$C2>=5,$C2<=8),"D-2","")
Column G=IF(AND($C3>=8,$C3<=10*1.025,$C2>=8,$C2<=10*1.025),"A-2","")
Column H=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"B-2","")
Column I =IF(AND($C3>=3,$C3<=4.5,$C2>=3,$C2<=4.5),"P-10","")
Column J=IF(AND($C3>=8,$C3<=10,$C2>=8,$C2<=10),"P-25","")
Column K=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"P-30","")
Column L=IF(AND($C3>=3,$C3<=5,$C2>=3,$C2<=5),"3-1","")
Column M=IF(AND($C3>=5,$C3<=8,$C2>=5,$C2<=8),"5-1","")
Column N=IF(AND($C3>=8,$C3<=10,$C2>=8,$C2<=10),"8-1","")
Column O=IF(AND($C3>=10,$C3<=12,$C2>=10,$C2<=12),"10-1","")
If I keep on using the same IF/AND function row 13,20 and the very last row won't get qualified into any part type.
I know there is a better way to do this and even maybe create 1 single column that I could call "Part Types it Qualified for" and have 1 or more part types the section qualified for in a single cell.
I'm looking for help to get this optimized and make it definitely more efficient.
Thanks!