Hello
My question is posed to see what better ways if any (I am sure there must be) there are of achieving a result.
I am analysing approx. 30K rows of product sales data where each row includes a part code of which a sub-string of (generally) 3 letters describes the product type. Up until today I had identified 37 individaul product types/sets and have been achieving the extract by using nested IF statements with COUNTIF and FIND to identify the sub-string and return it, thus:
=IFERROR(IF(COUNTIF(A2410,"*GRA*"),(MID(A2410,FIND("GRA",A2410)+0,3)),IF(COUNTIF(A2410,"*CAA*"),(MID(A2410,FIND("CAA",A2410)+0,3)), etc etc
and then using vlookup to reference the list of types and associate the product set name with the part code. I hope that makes sense? The purpose being to categorise the numerous individual part codes into product sets for sales analysis purposes.
As time goes on and, as has just happenend, many more part codes and product types are added to the sales inventory so the nested IF statements grow ever greater to the point that today I will need 62 ...perilously close to the maximum possible.
How, without VBA which I have no knowledge of, can I achieve the same result without using the monster of a formula I have spawned that will shortly be incapable of achieving my aim?
All help is appreciated.
Thank you.
My question is posed to see what better ways if any (I am sure there must be) there are of achieving a result.
I am analysing approx. 30K rows of product sales data where each row includes a part code of which a sub-string of (generally) 3 letters describes the product type. Up until today I had identified 37 individaul product types/sets and have been achieving the extract by using nested IF statements with COUNTIF and FIND to identify the sub-string and return it, thus:
=IFERROR(IF(COUNTIF(A2410,"*GRA*"),(MID(A2410,FIND("GRA",A2410)+0,3)),IF(COUNTIF(A2410,"*CAA*"),(MID(A2410,FIND("CAA",A2410)+0,3)), etc etc
and then using vlookup to reference the list of types and associate the product set name with the part code. I hope that makes sense? The purpose being to categorise the numerous individual part codes into product sets for sales analysis purposes.
As time goes on and, as has just happenend, many more part codes and product types are added to the sales inventory so the nested IF statements grow ever greater to the point that today I will need 62 ...perilously close to the maximum possible.
How, without VBA which I have no knowledge of, can I achieve the same result without using the monster of a formula I have spawned that will shortly be incapable of achieving my aim?
All help is appreciated.Thank you.
