Retrieving a value based on comparison of multiple columns

Hello all,

I am having trouble with combining different datasets in excel, which I hope you could help me with!

In our study, participants received feedback which they had to rate on usefulness. This resulted in two separate datasets: one with feedback received and one with feedback rated (which is not the same as the feedback received, as we have some missing data because participants did not rate all feedback).

For example:

Dataset 1
name - feedback_date - feedback_received (code)
john -------- 10/10/2014 ------- A1
john -------- 11/10/2014 ------- A2
john -------- 12/10/2014 ------- A3
john -------- 13/10/2014 ------- A4
mary ------- 10/10/2014 ------- A1
mary ------- 11/10/2014 ------- A2
mary ------- 12/10/2014 ------- A3
mary ------- 13/10/2014 ------- A4

Dataset 2
name ---- feedback_date - feedback_received (code) - rating of feedback
john -------- 10/10/2014 ---------------- A1 ---------------------- 2
john -------- 12/10/2014 ---------------- A3 ---------------------- 7
mary ------- 12/10/2014 ---------------- A3 ---------------------- 5
mary ------- 13/10/2014 ---------------- A4 ---------------------- 4



I want to combine these datasets so that the end result is that I have Dataset 1 with the ratings of the feedback added to it. To do this, I was thinking about comparing the data in these two datasets based on participant's name, the date the feedback was received, and the actual feedback that was received (which is coded). When these three variables are the same, I want the ratings to be added to the first dataset.
This is the end result that I would like to get (empty rating lines are missing data):

name - feedback_date - feedback_received - rating of feedback
john ------------- 10/10/2014 ----------- A1 ----------- 2
john ------------- 11/10/2014 ----------- A2
john ------------- 12/10/2014 ----------- A3 ----------- 7
john ------------- 13/10/2014 ----------- A4
mary ------------ 10/10/2014 ----------- A1
mary ------------ 11/10/2014 ----------- A2
mary ------------ 12/10/2014 ----------- A3 ----------- 5
mary ------------ 13/10/2014 ----------- A4 ----------- 4


I have tried several things using the VLOOKUP statement, but first of all I am not very familiar with this command, and second of all I am not sure whether the VLOOKUP statement is able to compare several columns at once.

I hope this is possible in Excel and I hope you guys can help me!

Thanks,

Rik

How about you copy/paste one dataset into another, sort all data (multiple levels, by name, then by date, then by feedback code if needed), then select the data range and click Remove Duplicates under the Data tab? I did not try this, just a quick idea.

Thanks for your reply!

I have tried it out and if I understand you correctly it doesn't seem to work, as the columns are sorted together simultaneously, and the order of the two datasets are not changed independently and sorted per individual if I do that. I might be doing this wrong tho...

Ok, I tested my solution, and the last part doesn't work, so scratch the Remove Duplicates. You could use Conditional Formatting to highlight the cells that you don't need to fill, because the feedback rating is already given in the next cell. For this you need a custom rule =IF(H6=H7;IF(I6<1; TRUE; FALSE))

The rule works, but you cannot sort the data afterwards, because it messes with the rule.

Thank you for your reply as well! I will try move the formulas around a little bit.

I also made a sample dataset as you suggested to show what I mean. The data on the left is the first dataset (including the feedback delivered), the data in the middle is the second dataset (including the ratings given), and the data on the right is the desired output (highlighted in grey).

Best wishes,

Rik

Awesome, that's working great. Thanks everybody for helping!