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

Attached Files

data.xlsx (57.7 KB, 7 views)

If I have understood you correct.

Attached Files

Departments.xlsx (57.5 KB, 8 views)

Thank you very much for your quick response and for this -

With regard to your comments re N6 and N28 you are correct.

I will see if I can adapt the formula to take into account more departments and more sections - so I may come back if I get stuck.

Once again many thanks for your quick response and for the fomula.

E