+ Reply to Thread
Results 1 to 17 of 17

Populate data in specific way .. complicated

  1. #1
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Populate data in specific way .. complicated

    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
    Attached Files Attached Files
    Last edited by KingTamo; 01-20-2017 at 07:47 PM.

  2. #2
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    any help please

  3. #3
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    Is everything clear in my first post?

  4. #4
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    Can anyone help me please?

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate data in specific way .. complicated

    If you need to solve any problem try to divide your request into small issues .. Focus on one part in each thread
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  6. #6
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    thanks for reply but it is only one issue and I need to solve it as a whole

  7. #7
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    how about N Bibi M03 05/07/15 400
    this also has 3 rows!! and it is in Q column!
    ? am i missing something?
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    oh.. i guess it's a mistake on your results table... after the first 400 should be 279,87 twice right?

  9. #9
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    Hope it helps!
    Open the file and press the button.

    This is the code I wrote:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    That's great
    Actually I missed out a figure on the right hand side for N Bibi who had a deduction for child tax. In cell S6 should be 120.13.
    For each month the sum of the values of JC figures should always equal JD figures.
    Also the bank code from column V needs adding to the relevant cells in the results and for some reason the BP row does not come last for results of S Stead, otherwise looking good.

  11. #11
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    Quote Originally Posted by KingTamo View Post
    Actually I missed out a figure on the right hand side for N Bibi who had a deduction for child tax. In cell S6 should be 120.13
    that is not a problem... just add the value to the correct place and run the code again.
    Quote Originally Posted by KingTamo View Post
    ... for some reason the BP row does not come last for results of S Stead...
    the code treats the values not the names, that means that for each value in the right it writes 1 or 2 lines and passes to another value.

  12. #12
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    Yes I have added the value. Only need the BP codes need adding and could you take a look at why the BP row for S Stead is coming in the wrong place?
    I have added .Range("B" & lr) = Range("V" & val.row) and now BP code is showing.Is this ok?

  13. #13
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    Yes your addition is good. I have modified the code to put the lines created by U-column before the BP line.

    please copy and replace :
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    Thanks a lot for help.
    I have now tried it with real data. All working ok except a minor overlook on my behalf.
    Sometimes the P.A.Y. in column R can be a negative, but results can not have a negative value in them. Instead the negative is turned into a positive value and the code for column A is reversed (JD instead of JC).

    BTW the codes in the green box should have been. I'v corrected these on my sheet.

    7000-JD 2210-JC 2211-JC 2220-JC 7050-JD 2220-BP
    7025-JD

  15. #15
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    here :
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Populate data in specific way .. complicated

    Sorry for my late response
    That's great job. Thank you very much for helping me
    thanks a million

  17. #17
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Populate data in specific way .. complicated

    You're welcome! Thank you for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populate data according to specific numbers
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2014, 08:17 PM
  2. Populate a list of data from specific worksheets
    By Emou in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2014, 02:51 PM
  3. Complicated Macro to populate data with sum into 1 spreadsheet
    By cathchin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2012, 10:45 PM
  4. Auto-populate specific data from one sheet to another
    By arskiracer in forum Excel General
    Replies: 0
    Last Post: 10-17-2012, 10:27 PM
  5. Populate data until specific row criteria is reached.
    By ollander88 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-27-2012, 01:23 PM
  6. Excel 2007 : Auto-populate data - seems complicated.
    By kaifab in forum Excel General
    Replies: 1
    Last Post: 11-29-2011, 02:40 AM
  7. Replies: 7
    Last Post: 04-09-2010, 11:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1