+ Reply to Thread
Results 1 to 9 of 9

Filling a long column of names

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Filling a long column of names

    Dear all,

    I have one column with 70K of names that vary in their string lenghts.
    Each name is the top cell in its group of cells in the same column. The rest of the cells in each group is just an underscore which means "ditto" for the top name of the group.

    I'm attaching a simple example for illustration. The top group in that file is:
    Whittemore
    _
    _
    _
    _
    _
    _
    _
    _
    _


    What is required is to replace the underscore with the top name of the group so it becomes:
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore
    Whittemore

    More names are set in the attached file.

    Can I get some precious help with this question, please?

    Many thanks in advance.

    T.
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,221

    Re: Filling a long column of names

    Here's a simple loop...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,525

    Re: Filling a long column of names

    Hi Terry,

    Try this:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,221

    Re: Filling a long column of names

    Or this...No loop

    I notice Post 2 has a Elseif statement which is not needed
    Please Login or Register  to view this content.
    Last edited by sintek; 07-25-2020 at 07:28 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    818

    Re: Filling a long column of names

    Please Login or Register  to view this content.
    Kind regards, Harry.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,221

    Re: Filling a long column of names

    @ HSV

    Of Course...Great simplification...

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,874

    Re: Filling a long column of names

    An alternative solution is to use Power Query

    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Filling a long column of names

    honestly .. it has been my privilage that my question attracted all those great minds to solve it. All suggested solutions worked to the desired result, but of course, with different processing time. The fastest was Sintek's first solution, which I implemented on the real data file after trying all those great solutions.

    I respectfully ask everyone participated in solving this question to accept my deep gratitude.

    Thank you very much.

    T.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,221

    Re: Filling a long column of names

    The fastest was Sintek's first solution
    Not possible...I would think HSV adapted code would be the most suited...
    How big is your data set...

    Thanks for the kind words and 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] Creating multiple column randomized long list out of 114 names
    By terryhenderson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2019, 06:27 AM
  2. Filling in a long-term table from a single, changing source
    By Drayde in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2014, 12:38 PM
  3. [SOLVED] Need help filling in a table horizontally from a long string of data
    By BentotheJJ in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-05-2013, 06:25 PM
  4. Filling in Names
    By CDS93 in forum Excel General
    Replies: 3
    Last Post: 12-09-2009, 07:59 PM
  5. Filling in names into spaces below
    By eonizuka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2009, 01:28 PM
  6. Filling in names according to corresponding numbers
    By Poldi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-24-2008, 11:16 PM
  7. Replies: 3
    Last Post: 01-14-2006, 08:50 PM

Tags for this Thread

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