+ Reply to Thread
Results 1 to 12 of 12

Need to match data in 2 columns to autopopulate cells.

  1. #1
    Registered User
    Join Date
    07-09-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    8

    Post Need to match data in 2 columns to autopopulate cells.

    To better understand I want to provide an example.

    Looking at the sheet I provided, I am looking to fill any cells with the owner name missing. For example, Jessica has 3 cars, each listed with her Owner ID, but not all cells are filled with the owner name. I am looking to find a way to automate the process where, if a an ID matches with a name, it will populate all cells associated with that ID. There are also some that have the name scattered about such as Len's. Some IDs also do not have a name to be matched with because there is no data for their name.

    If there is a row, with a name and ID #, I want all rows that have that ID # to populate the owner's name associated with it.

    So ideally any row that has the ID # 123 in it should automatically fill the Owner name to Jessica in this example.

    Let me know, thank you.
    Attached Files Attached Files
    Last edited by nwebbs10; 07-09-2021 at 11:08 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Need to match data in 2 columns to autopopulate cells.

    Welcome to the forum.

    By 2008, do you mean MS365? If so, please update your forum profile.

    Now, where in your sample workbook have you mocked up what you want? I don't believe I can see what you are describing there.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Need to match data in 2 columns to autopopulate cells.

    You may need a helper column/sheet where you have those associations listed out so that the values can be populated.
    then you can either use VLOOKUP or INDEX/MATCH
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need to match data in 2 columns to autopopulate cells.

    I think we need to see some expected results, as Owner IDs are not unique to a name. For example, 12344 belongs to 2 people... and Mark Good has 3 different IDs.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need to match data in 2 columns to autopopulate cells.

    On a NEW post in THIS Thread, please show starting point and end result. The two files now on Post 1 are VERY different in terms of what is in column C. Please upload a clean sample that you have checked over. The problem is easy to solve IF we have a decent sample to work with.

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Need to match data in 2 columns to autopopulate cells.

    I see unique id's in the file i downloaded:

    nwebbs10.jpg

  7. #7
    Registered User
    Join Date
    07-09-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    8

    Re: Need to match data in 2 columns to autopopulate cells.

    Correct, the ID is unique to the owner. I want to match the owner with that ID and populate the blank owner cell with the correct name according to the ID in the column.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need to match data in 2 columns to autopopulate cells.

    ... and person's name appears at the first use of the ID... or the last... or anywhere in between?? And what about 9566???

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need to match data in 2 columns to autopopulate cells.

    So, if that is the case:

    =IFERROR(INDEX($A$2:$A$18,MATCH(1,($C$2:$C$18=C2)*(LEN($A$2:$A$18)>0),0)),"")
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-09-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    8

    Re: Need to match data in 2 columns to autopopulate cells.

    That is all intentional. There is meant to be an ID # with no name attached to it as well as names scattered about.

  11. #11
    Registered User
    Join Date
    07-09-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    8

    Re: Need to match data in 2 columns to autopopulate cells.

    Thank you! Is there any way you could explain the formula so I can try and transfer it to a different data set involving many more rows?

    I really appreciate the help, I am very new to using excel.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need to match data in 2 columns to autopopulate cells.

    1. Try to avoid using whole column references, as this could slow you up... but if you have 600 rows... set the ranges to be 2000. Just be sensible and future-proof.

    =IFERROR(INDEX($A$2:$A$18,MATCH(1,($C$2:$C$18=C2)*(LEN($A$2:$A$18)>0),0)),"")

    Red: Returns TRUE if the condition is true, otherwise false... so any/all rows in col C containing 123 will return TRUE.
    Orange: same same... returns TRUE where the string length in column A is >0.

    TRUE*TRUE=1... everything else = 0.

    So...

    Green: find an exact match for the the rows that return a 1 (the green 0 specifies an exact match)
    Blue: return the corresponding value from column A where both conditions are satisfied:
    Black, if nothing amatches (an error) return a blank.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Autopopulate Data to multiple sheets while eliminating zero or blank cells
    By teamdynasty27 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-25-2019, 12:31 PM
  2. Data Validation and formula to autopopulate several rows and columns
    By coachf in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2018, 04:37 PM
  3. Replies: 3
    Last Post: 09-05-2016, 05:38 PM
  4. [SOLVED] How to autopopulate columns on one sheet from a data range on another.
    By NUKLEAR-SLUG in forum Excel General
    Replies: 7
    Last Post: 07-01-2014, 10:52 AM
  5. [SOLVED] Highlight cells if data in 2 columns match
    By damobilebrood in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-28-2014, 09:19 AM
  6. [SOLVED] Compare two columns with data, if match or not found then change the colour of cells
    By Sachy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-15-2013, 03:58 AM
  7. autopopulate formula from two columns of data
    By thedunna in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2013, 09:57 AM

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