+ Reply to Thread
Results 1 to 5 of 5

column has gaps; need to fill rows with data, based on another column

  1. #1
    Registered User
    Join Date
    05-09-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    column has gaps; need to fill rows with data, based on another column

    Hello,

    2 issues: will draw sample data below so hopefully it makes sense. Note: in the format below -- is the column separator for columns A and B.

    A -- B
    Mike -- 1234
    Mike --
    Sanjay --
    Sanjay -- 1456
    Sanjay --
    Ted --
    Ted --
    Chris --
    Chris -- 4444


    in the above example, this is what the end result should be:

    A -- B
    Mike -- 1234
    Mike -- 1234
    Sanjay -- 1456
    Sanjay -- 1456
    Sanjay -- 1456
    Ted --
    Ted --
    Chris -- 4444
    Chris -- 4444

    In the above example, column B for the TED rows are still blank, because NONE of the TED rows, have a value, so the source data is blank for TED, so that's fine. Basically if the source data has a value for any of the rows, all the rows with the same column A value, should have the same B value.

    The next part is a bit stranger. Vlookups and matches aren't that difficult, but how do we limit it based on a section of data.


    Example:

    A -- B -- C
    123 -- Mike -- Chris
    123 -- Susan -- Lin
    567 -- Sanjay -- Ted
    567 -- Kary -- Alice
    567 -- Alice -- Opum

    In the above case, column D should result no match for records under A = 123, since the names in B and C dont have any matches, but for 567, column D should show: 567 Alice

    since alice is in both columns B and C.

    Thanks

    Regards,

    Sanjay

    PS: i can't think how to do this with regular functions; i assume array formulaes, which i just dont get. is anyone here, that can explain arrow formulae if i send a bunch of questions? the help files aren't really helping (I used those to learn the regular functions and was fine but something about arrays is much worse )
    Last edited by StarFyre; 08-26-2016 at 04:48 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: column has gaps; need to fill rows with data, based on another column

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-09-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: column has gaps; need to fill rows with data, based on another column

    added sample sheet with notes. 4 sheets. 2 for each issue.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: column has gaps; need to fill rows with data, based on another column

    Hi,
    for case1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    case2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-09-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: column has gaps; need to fill rows with data, based on another column

    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. [SOLVED] Fill Column based on Data in Column A
    By sm789 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-30-2016, 10:09 AM
  2. [SOLVED] need to fill in rows based on column a information
    By AWITCHER in forum Excel General
    Replies: 4
    Last Post: 05-13-2015, 03:43 PM
  3. Insert rows based on a cell value and Fill down column A
    By blaxor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2014, 04:54 AM
  4. Replies: 3
    Last Post: 10-17-2014, 02:29 PM
  5. Replies: 3
    Last Post: 04-12-2013, 04:36 PM
  6. Fill company name down column with gaps
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-19-2013, 04:57 PM
  7. [SOLVED] Auto Fill Column with Date based on rows in other column
    By JOUIOUI in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2006, 01:35 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