Automate copying of data (with some conditions)

  • A B C D E F

    Department: Cost Center: 110100
    Emplid OldEmpid Cost Center Name Nationality Profession
    00216 00115 INDIA NONE
    00499 00163 INDIA CLEANER
    Department: Cost Center: 110105
    Emplid OldEmpid Cost Center Name Nationality Profession
    04449 02809 KENYA INT. AUDITOR

    The above data shows a file I have imported from our system to excel.

    Col A is for EmplID

    Col B is for OldEmpID

    Col C is for Cost Center (I inserted this col; this is where i want to copy the "cost center code" housed also in col F (e.g. 110100,110105)

    Col D is for Name

    Col E is for Nationality

    Col F is for Profession (This col contains also the "cost center code" that I want to copy to col C)

    My problem:

    If there is a value in Col A (EmplID) that starts with "0" (all EmpID starts with "0"), I will copy the corresponding "cost center code" in Col C.

    The word "Department" (housed also in Col A), triggers that a different set of "cost center code" will be copied to Col C.

    Actually, I also wanted the cost center in col C to be 5digits only, so I wanted to exclude the first digit.

    So in this case,
    Emplid 00216 and 00499 will have "cost center code" of 10100; and
    Emplid 04449 will have "cost center code" of 10105.

    I have lots of rows for this, so i need a VBA to automated/facilitate the copying of the Cost Center codes.

    Any help is highly appreciated.

    Thanks a mil.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!