Hi,
I'm after a vba code to convert data like in the attached excel file.
The data is to be converted is in the right hand side of the Input Data sheet and the results are on the left hand side.
One line of data can have various lines of results depending on the input data.
Each cell in the data row between cloumns Q and T would create a new line of output data.
A value in column U would create 2 lines of output data, one with a Type code of JC and one with a type code of JD.
if there is a date in the Pay Date Column then an extra row is created on the results side
The nominal AC Reference its taken from cells R2 to V2 EXCEPT for The nominal reference for Gross Pay...
If the name is M Rafiq then the nominal reference is 7000 and for any other name it is 7025.
I have put in 4 examples of data and results.
I would like the results to be sent to sheet "Wages to Import"
Ok.
When doing conversion first cell to check O2. If value = M Rafiq then use last 2 digits from Q2 for cell A2. Use first 4 digits in Q2 for cell c2.
If o4<> M Rafiq then use Q3.
Now we get data from yellow area.
First work in column Q
Date in column D2 =last Eight digits of P4
E2=o4
F2= o4+q1+d2
g2=q4
columns H and I are always T9 and 0.00
j2=first 3 digits of p4
k2 is always raf
If r4 is empty then goto work on column S
If s4 is empty then goto work on column T
A3=last 2 digits from t2
C3=first 4 digits from t2
etc....
For the last example where there is data in column U7 "Employer NIC" then the the last 2 digits from U2 are used for A15 and the first 4 digits for C15 etc. but then it is repeated on the next line but using the codes from S2 for A16 and C16.
Data in Column Q= 1 Line of output
Data in Column R= 1 Line of output
Data in Column S= 1 Line of output
Data in Column T= 1 Line of output
Data in Column U= 2 Lines of output
Data in Column V= 0 Lines of output
Data in Column W= 1 Line of output
I appreciate your help a lot
Bookmarks