+ Reply to Thread
Results 1 to 5 of 5

Copy character from name and paste number of times across according to value

  1. #1
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Copy character from name and paste number of times across according to value

    Hi

    I have an extension to my project

    From the name entered, we need to copy the character/letter and paste in J20 and copy across to max length of the name.

    Thereafter a value is allocated to each character/letter of the alphabet

    This is thereafter pasted under the age across the number of times each letter has a value

    Thank you for all help

    The age will be calculated from the date of birth to the current year


    Kind regards
    Vraj
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy character from name and paste number of times across according to value

    Try this

    In J27, then drag right as far as AE27

    =IF(J21="","",SUM($J21:J21)-J21)

    This creates a running total of the numbers in J21:AE21.

    Then in J24, and drag right as far as needed.

    =LOOKUP(MOD(J23-1,SUM($J$21:$AE$21)),$J$27:$AE$27,$J$20:$AE$20)

    Does that do what you need?

  3. #3
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy character from name and paste number of times across according to value

    Hi

    Many thanx for the help, it helped me greatly

    One question if I may ask. can the age be calculated automatically and populate from J23 to max age, starting from 1

    Regards

    raj

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Copy character from name and paste number of times across according to value

    I assume by 'Max age' you mean the difference between date of birth and current date?

    Having each digit of the dates in individual cells is not formula friendly.

    With date of birth in AU1 and current date in AU2, enter this formula in AU3

    =DATEDIF(AU1,AU2,"Y")

    Then change the lookup formula to allow for the age.

    =IF(J23>$AU$3,"",LOOKUP(MOD(J23-1,SUM($J$21:$AE$21)),$J$27:$AE$27,$J$20:$AE$20))

    If you want to do it the without the extra dates in AU1:AU3 then you are going to need some complex and messy formulas.

  5. #5
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy character from name and paste number of times across according to value

    Hi Sir

    thank you very much it worked brilliantly

    Kind regards

    Raj

+ 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] Copy paste data number of times based on cell value
    By shreeja178 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2016, 06:19 AM
  2. Copy and Paste names using a variable for the number of times
    By miles44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2015, 03:13 PM
  3. Copy a row and paste it a number of times based on a cell value
    By glrubens in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-02-2013, 12:37 PM
  4. Copy and Paste a defined number of times...
    By ccxc007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2012, 02:10 AM
  5. [SOLVED] Copy one value and paste it x number of times
    By jcb in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-12-2012, 04:52 PM
  6. VBA; copy paste number of times based on cell value
    By photopidge in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2011, 03:17 AM
  7. Copy then paste/insert X number of times
    By Steve6386 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-04-2010, 04:53 PM

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