Log In
Register
Home
July 28, 2018
Offline
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?
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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.
July 28, 2018
Offline
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.
July 28, 2018
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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.
Answers Post
