+ Reply to Thread
Results 1 to 3 of 3

Fill blank vertical cells with row's horizontal cell values

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Fill blank vertical cells with row's horizontal cell values

    Have been perplexed by this one today...........Not sure how to execute it.........

    I have a column of similar, grouped names. One row of them has a horizontal row of numbers like this

    A B C D E
    This Product 1 Light 2 Light 3 Light 4 Light
    This Product
    This Product
    This Product
    That Product 4 Light 5 Light 8 Light 9 Light
    That Product
    That Product
    That Product


    How do I concatenate the name with the different number of lights downward using the horizontal values as a fill in series? So Far, I have:

    =IF($A$1:$A8=A1,CONCATENATE(A1&" "&......................

    But from here I am lost............Anyone here got an idea of what i should be looking at as far as a Function to complete it like this??

    A B C D E F
    This Product This Product 1 Light 1 Light 2 Light 3 Light 4 Light
    This Product This Product 2 Light
    This Product This Product 3 Light
    This Product This Product 4 Light
    That Product That Product 4 Light 4 Light 5 Light 8 Light 9 Light
    That Product That Product 5 Light
    That Product That Product 8 Light
    That Product That Product 9 Light

    Thank you

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Fill blank vertical cells with row's horizontal cell values

    Assuming your matrix is as shown, but this in B1 and drag down.
    =A1&" "&INDEX($C$1:$F$1,1,MOD(ROW()-1,4)+1)
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Re: Fill blank vertical cells with row's horizontal cell values

    Thank you .....it kinda works.....It didn't catch the second set of row values, just copied the same first row down. I just had to fill the series down the blanks, and then changed the formula to this:

    =A1&" "&INDEX(C1:F1,1,MOD(ROW()-1,4)+1)

    Does what i need.....thanks!

+ 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. Macro to move horizontal data to vertical - in new sheet--excluding blank cells
    By Jackdaddy0711 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2012, 12:54 PM
  2. [SOLVED] countif, fill horizontal, lookup vertical
    By tigaente in forum Excel General
    Replies: 4
    Last Post: 04-23-2012, 05:21 AM
  3. Replies: 0
    Last Post: 03-21-2012, 10:18 AM
  4. Replies: 1
    Last Post: 03-23-2011, 07:41 AM
  5. Function to list values of last 3 non-blank cells in a vertical bl
    By ANJ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2005, 08:06 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