• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Merging/importing data based on other column data|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Merging/importing data based on other column data|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Merging/importing data based on oth…
sp_PrintTopic sp_TopicIcon
Merging/importing data based on other column data
Avatar
Karen Lewis
Member
Members
Level 0
Forum Posts: 24
Member Since:
July 28, 2018
sp_UserOfflineSmall Offline
1
April 15, 2019 - 9:53 pm
sp_Permalink sp_Print

I am unsure what to use to enable me to copy data from one workbook to another workbook based on another value. 

I have a 2 different tables, one each in a different workbook. In Workbook 1, Table 1, I have the columns - Full name, Class Description, Sales person (and others). In Workbook 2, Table 2, I have the columns - Full Name, Intake Description (which is the same as Class description), Salesperson (and others). If the Full Name and Class description (or intake description) is the same in both worksheets,  I want to import the Sales persons name from one worksheet and place it against the relevant name and class/intake description in the other worksheet. Is someone able to assist me with this please?

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
April 16, 2019 - 5:01 am
sp_Permalink sp_Print

Hello,

It would help a lot if you can provide some sample file or two. No need for real data, what is important here is to see the structure and what the expected outcome is.

Avatar
Karen Lewis
Member
Members
Level 0
Forum Posts: 24
Member Since:
July 28, 2018
sp_UserOfflineSmall Offline
3
April 16, 2019 - 6:24 am
sp_Permalink sp_Print

Thanks Anders. I was thinking maybe it is a IF and AND function I need to use. E.g. If WS1 column A = WS2 column B and WS1 column C=WS2 column D then return the corresponding value from WS2 column E...something along those lines. I will post a sample file this evening. Thank you. 

Avatar
Karen Lewis
Member
Members
Level 0
Forum Posts: 24
Member Since:
July 28, 2018
sp_UserOfflineSmall Offline
4
April 17, 2019 - 6:10 am
sp_Permalink sp_Print

Here are a couple of workbooks. I have put a formula in the Marked Assessments - test in the Note Type column but it is returning a blank instead of adding the text I need. 

Expected outcome:

If Columns C & D in the Marked Assessments - test=Columns A & D in the Validations - test then place the value from Column B in the Validations - test into Column I in the Marked Assessments - test. 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
April 17, 2019 - 7:47 am
sp_Permalink sp_Print

Hello,

A simple solution is to create one extra column in both tables, then you can use VLOOKUP.

Note: I hade both files opened when working with these files. The path to the Validations-test.xlsx is different if that file is closed.

In Validations-test.xlsx I created the new helper column as column B, just to have the helper column to the left of Note Type, thus Note Type is now on column C.
The formula in the helper column is =[@[Full Name]]&[@[Unit validated]].
I noticed that you have an extra space between the words in Unit validated column.

In Marked-assessments-test.xlsx I created the new helper column in column K.
The formula in the helper column is =[@[Full Name]]&[@[Assessment Name]].

The formula in cell I2 is:
=VLOOKUP([@[Helper Column]],'Validations-test.xlsx'!Table1[[Helper column]:[Note Type]],2,FALSE)

You will get an #N/A error for rows 2 and 3, as there are no match for those two. If you forget to remove the extra space in Validations file and Unit Validated data, you will get the error for all rows. So if you don't want to display those errors then you can wrap the above formula with IFNA() function to handle how the errors are displayed.

I have attached the two modified files for reference, just notice that the reference to Validations-test.xlsx file is showing full path to where I stored the file, so do use the formulas I have written here.

sp_AnswersTopicAnswer
Answers Post
Avatar
Karen Lewis
Member
Members
Level 0
Forum Posts: 24
Member Since:
July 28, 2018
sp_UserOfflineSmall Offline
6
April 17, 2019 - 8:59 pm
sp_Permalink sp_Print

Thank you for your help Anders. Works like a dream! Smile

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
April 18, 2019 - 3:35 am
sp_Permalink sp_Print

Hello Karen,

Thank you for the feedback, I am just glad I was able to help.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Getschel, Lynnette Altomari, Roy Lutke, Jeff Krueger, Natasha Smith, Sopi Yuniarti
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.