How to code data eg if more than one dept has a 1 or 2 in it
Using Windows 10 and Excel 2010 32 bit - hope the following makes sense.
I have information in a single worksheet which needs to be coded into different types of information - a, a1, a9, b, b1, b9, c, c1, c9, d, d1, d9. The attached spreadsheet is a sample of the original spreadsheet - in the original spreadsheet there could be up to 6 depts with between 1 and 6 sections in each.
Column A contains a name, Column B and C contain data relating to information that is taken into account in the coding of a letter, eg, a, b, c, d.
Columns D and I are check total columns and not needed in the analysis.
Columns E to H contain data either 1 or 0 which need to be analysed and therefore coded with either a 1, 9 or blank.
Columns J to L - summarise cells E to H as follows
column J = dept1a = COUNTIF(E3,1)
column K = dept2a = COUNTIF(F3:G3,1) note this counts 2 columns
column L = dept3a = COUNTIF(H3,1)
Column M (code letter) is derived from columns B and C using the following formula
IF(AND(B3=0,C3=0),"a",IF(AND(B3=0,C3=1),"b",IF(AND (B3=1,C3=0),"c",IF(AND(B3=1,C3=1),"d"))))
The following is the section I need help with
Column N (code no) needs a formula to work out the following using the values in columns J to L.
1 if more than one dept has a 1 or 2 in it return 1, see rows 7, 8, 14 to 17, 21, 26 and 27
2 if one dept has 2 return 9, see rows 9, 18, 22 and 28
Column O (Final code) is derived from concatenating columns M and N unless column N is blank then only use column M using the following formula
IF(N3="",M3,CONCATENATE(M3,N3))
The following fields would be hidden in final spreadsheet (dept1a, dept2a, dept3a, code letter, code no.), ideally I would like not to have to enter more columns, as the original spreadsheet uses columns A to CL.
Also I was wondering whether this should be done using VBA
|