Share via

Data Mapping/Linking

Anonymous
2023-10-26T02:57:28+00:00

(*Moved from feedback.)

How do I tell excel that specific data needs to go into specific rows. E.g. I have 250 names in a column.. I want to import new data into an adjacent column for each of those names, but the names change every time on the imported sets. Therefore, I need the new data to find the correct name. Otherwise, I have to manually check each name and data to ensure it is accurate. PLEASE HELP!! See below for example.

Set A is my master set. I want to copy the numbers of Set B and C and put them to the right of the first numbers in Set A. But I don't care about the additional names in Set B, or the lack of names in Set C.

Set A Set B Set C
Jack 49 Jack 45 Jack 55
Mary 58 Mary 50 Mary 68
John 74 Hannah 79 Luke 59
Luke 21 Luke 25 James 64
James 69 James 60 John 56
Georgia 74 Georgia 75
John 66
Jane 65
Microsoft 365 and Office | Excel | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-10-27T12:18:28+00:00

    . . . Yours is a simpler formula. Thank you for sharing your solution. . .

    Thank You for the prompt revert. Appreciate!

    Best Wishes!

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-10-27T12:08:56+00:00

    Hi,

    Yours is a simpler formula. Thank you for sharing your solution.

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2023-10-27T11:58:14+00:00

    . . . Alternatively you may enter this single cell formula in cell C3.

    =LET(app,VSTACK($H$3:$I$10,$K$3:$L$7),IFNA(DROP(REDUCE("",A3:A8,LAMBDA(a,i,VSTACK(a,TOROW(FILTER(CHOOSECOLS(app,2),CHOOSECOLS(app,1)=i))))),1),"")) . . .

    Image

    Hi Ashish,

    The following formula is getting the required output.

    Is this formula correct?

    OR

    Is there a flaw that I am not able to spot?

    I will be grateful if You share Your expert opinion.

    Thank You!

    Best Wishes!

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-10-26T23:09:38+00:00

    Hi,

    Select the ranges of Set B and Set C and convert them into Tables (Ctrl+T). Enter this formula in cell C3 and drag down

    =LET(app,VSTACK($H$3:$I$10,$K$3:$L$7),TOROW(FILTER(CHOOSECOLS(app,2),CHOOSECOLS(app,1)=A3)))

    Alternatively you may enter this single cell formula in cell C3.

    =LET(app,VSTACK($H$3:$I$10,$K$3:$L$7),IFNA(DROP(REDUCE("",A3:A8,LAMBDA(a,i,VSTACK(a,TOROW(FILTER(CHOOSECOLS(app,2),CHOOSECOLS(app,1)=i))))),1),""))

    Hope this helps.

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2023-10-26T21:54:18+00:00

    Hi HEALY, This is Bryll

    Thank you for writing us here in Microsoft Community.

    It sounds like you are trying to do data mapping and linking in Excel application. This can be a complex and challenging situation, but I will try to help you as best as I can.

    First, please make sure that you have the latest updates for Excel and Windows. Sometimes, outdated versions can cause compatibility or performance problems. You can check for updates by following these steps: https://support.microsoft.com/en-us/windows/update-windows-10-3c5ae7fc-9fb6-9af1-1984-b5e0412c556a Second, please verify that your data sources are valid and accessible. If you are using external data sources, such as web pages or databases, make sure that they are online and that you have the correct credentials to access them. You can test your data sources by following these steps: https://support.microsoft.com/en-us/office/connect-to-import-external-data-0f9200b2-1559-44c7-a9e9-b77d6a927593 Third, please review your data mapping and linking settings and make sure that they are correct and consistent. You can use the Data Model feature in Excel to create relationships between different tables of data. You can also use formulas or Power Query to link data from different sources. You can learn more about these features here: https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b

    I would love to answer your question further, but it feels like it's beyond our expertise. It is more complex than what we usually discuss here in the community. In order for you to get the accurate information you need, please visit the dedicated experts from forums Microsoft Q and A Questions - Microsoft Q&A and Welcome to the Word group! (microsoft.com) They are experts with this kind of concern.

    I am honored to be part of your journey.

    Respectfully,

    Bryll

    Microsoft Moderator

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-30T01:16:49+00:00

    Thank you so much guys!! This was an unbelievable help!

    0 comments