Week 8 - 06 Dec 2012 - Thu

37th day at SSMC.

Today, I was working on using formula to determine whether the data is automotive or non-automotive part. So I started off checking and reading that particular macro programming to see how to differentiate between automotive and non-automotive parts.

Firstly, I tried several formula that allow me to compare certain criteria and some formula that I had used before like 'COUNTIFS' and 'SUMIFS' but both formula cannot be used in this case as they don't display text even though they allow several criteria to be set.

Thus, I turned to other formula like 'IF' and also read and searched through the Excel Help function on several other formula like 'SEARCH' and 'REPLACE'. And also learnt of how to use these formula in combination.

My task was not just determining the automotive and non-automotive part, I have to display the text as well.

The criteria in determining the automotive parts are:

1. The part name contains 'LZ'.
2. There is a list of partID that does not contain 'LZ' but are still considered as automotive part. So I have to matched against the list.

And I have done 2 different formula to differentiate automotive parts from non-automotive ones for each of the criteria mentioned above.

1. =IFERROR((REPLACE(C3,1,SEARCH(Y1,C3)+2,"Auto")),"Non-Auto")
This formula searches for 'LZ' in each part name and replace that part name with 'Auto'. However, if the search failed, #Value will be displayed. Thus, IFERROR is required to display 'Non-Auto' for every #Value.

2. =IF(T2=Sheet3!A$3,"Auto",IF(T2=Sheet3!A$4,"Auto",IF(T2=Sheet3!A$5,"Auto",IF(T2=Sheet3!A$6,"Auto",IF(T2=Sheet3!A$7,"Auto",IF(T2=Sheet3!A$8,"Auto",IF(T2=Sheet3!A$9,"Auto",IF(T2=Sheet3!A$10,"Auto",IF(T2=Sheet3!A$11,"Auto",IF(T2=Sheet3!A$12,"Auto",IF(T2=Sheet3!A$13,"Auto",IF(T2=Sheet3!A$14,"Auto",IF(T2=Sheet3!A$15,"Auto",IF(T2=Sheet3!A$16,"Auto",IF(T2=Sheet3!A$17,"Auto",IF(T2=Sheet3!A$18,"Auto",IF(T2=Sheet3!A$19,"Auto",IF(T2=Sheet3!A$20,"Auto",IF(T2=Sheet3!A$21,"Auto",IF(U2=Sheet3!A$22,"Auto",IF(U2=Sheet3!A$23,"Auto",IF(U2=Sheet3!A$24,"Auto",IF(U2=Sheet3!A$25,"Auto",IF(U2=Sheet3!A$26,"Auto","Non-Auto"))))))))))))))))))))))))

In this formula,
T2 : First 6 characters of part name
U2 : First 7 characters of part name
Sheet3!A3:A26 : List of automotive part names that does not contain 'LZ'

Therefore, I need to compare T2 and U2 against Sheet3!A3:A26. If there's a match to the list, 'Auto' will be displayed else, 'Non-Auto' will be displayed.

Eventually, I have combined the 2 formula using the 'IF' and 'OR' functions to be fully successful in determining automotive and non-automotive parts.

I was having trouble combining the 2 formula and kept having errors. I guess I've got confused due to too many brackets. But still, I had managed to work it out! (^^)

The finally formula is :
=IF(OR(IFERROR((REPLACE(C3,1,SEARCH(Y1,C3)+2,"Auto")),"Non-Auto")="Auto",IF(T2=Sheet3!A$3,"Auto",IF(T2=Sheet3!A$4,"Auto",IF(T2=Sheet3!A$5,"Auto",IF(T2=Sheet3!A$6,"Auto",IF(T2=Sheet3!A$7,"Auto",IF(T2=Sheet3!A$8,"Auto",IF(T2=Sheet3!A$9,"Auto",IF(T2=Sheet3!A$10,"Auto",IF(T2=Sheet3!A$11,"Auto",IF(T2=Sheet3!A$12,"Auto",IF(T2=Sheet3!A$13,"Auto",IF(T2=Sheet3!A$14,"Auto",IF(T2=Sheet3!A$15,"Auto",IF(T2=Sheet3!A$16,"Auto",IF(T2=Sheet3!A$17,"Auto",IF(T2=Sheet3!A$18,"Auto",IF(T2=Sheet3!A$19,"Auto",IF(T2=Sheet3!A$20,"Auto",IF(T2=Sheet3!A$21,"Auto",IF(U2=Sheet3!A$22,"Auto",IF(U2=Sheet3!A$23,"Auto",IF(U2=Sheet3!A$24,"Auto",IF(U2=Sheet3!A$25,"Auto",IF(U2=Sheet3!A$26,"Auto","Non-Auto"))))))))))))))))))))))))="Auto"),"Auto","Non-Auto")

After lunch. I continued to do similar task:

- The second formula was to match if the data is automotive part and if the customer name matches with the list of customer code.
- For the third formula, it was harder as I do not know what to compare to for the data to be categorize as sample lot. Even after reading the macro programming, I was still not sure. I couldn't find out what does '=IF(C[-17]>12,1,0). I couldn't understand and find out what is C[-17] even after comparing to other macro programming. I had only know that it should be referring to certain column. So, I tried to look for past excel file data. And I had finally found out that sample lot is actually determine by the quantity column. When the quantity is more than 12, it is considered as a sample lot, on the other hand, when quantity is less than 12, it is not considered as a sample lot.

Had a tiring day today. Time to sleep. (^^)