Excel - fill in data from one table based on the value in 2 columns 1

[Jeanie]

I have a spreadsheet where I have data such as shown below:

Name Case Date Number
John 123 01/01/2016 ABC12345
Judy 567 01/02/2016 DEF56789
Jo 912 01/15/2016 HIJ12345
Jo 789 01/17/2016 LMN12345
Jane 864 01/22/2016 MNO67890

Then I have another sheet that just has name and date. As you can see above, a person can be on the table more than once, however, the value in the number column is always unique and the same person will never have more than 1 row with the same Date. I have another sheet that just has the Name and Date unduplicated.

I want to bring the number onto the sheet with just the name and date. So, basically, If column a and column C equal the values in column A and b on my new sheet, then enter the value found in column D.

Thanks in advance for your help!

Jeanie

 

[Deniall]

If your source data starts in cell A1 of "Sheet1" and your result data starts in cell A1 of "Sheet2", then enter

=OFFSET(Sheet1!$A$1,SUMPRODUCT(--(Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$5=B2),ROW(Sheet1!$D$2:$D$5))-1,3)

into cell C2 of Sheet2 and copy down.[ ] You'll get #REF! errors where there is no answer.[ ] I haven't investigated the result if (when?) there is more than one qualifying entry in the source data, since you assure us that cannot happen.

There are probably heaps of more elegant ways to achieve what you are after, but this was the first idea that popped into my head.

 

[Jeanie]

Thank you! That works. I appreciate your help

Jeanie

 

[Deniall]

A moment's thought, which I should have given when I did my original reply, tells me that if you do have more than one "qualifying entry" then the ROW() numbers for all the qualifying entries will be summed.[ ] This means you will get a result that is essentially random.

One way to test, using a similar approach to the above, would be to use
[tt]SUMPRODUCT(--(Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$5=B2))[/tt]
This returns the count of qualifying entries, so you require it to return the value 1.[ ] Use it in an IF() wrapped around the first formula.

Another comment.[ ] The OFFSET() and ROW() functions are "volatile".[ ] This means that their presence in a worksheet prevents Excel's recalculation algorithm from being clever in what it recalculates and what it does not recalculate, which in turn means that the spreadsheet will run slower (a lot slower if it is a large spreadsheet).

Maybe someone can suggest an alternative solution to your original problem.

 

[Jeanie]

This will never be a very large spreadsheet so the slowdown will probably be OK

Thanks,

Jeanie

 

[Andrzejek]

In order to align your data, please use TGML tags [ignore][Pre][/ignore] to show your data like this:

[pre]
Name Case Date Number
John 123 01/01/2016 ABC12345
Judy 567 01/02/2016 DEF56789
Jo 912 01/15/2016 HIJ12345
Jo 789 01/17/2016 LMN12345
Jane 864 01/22/2016 MNO67890
[/pre]
Use Preview before you Submit your post.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Jeanie]

Sorry - but I have no idea how to do what you just requested. I know my ignorance is showing - is there some place that describes this?

Thanks,

Jeanie

 

[Andrzejek]

When you type a post, above the box where you type you have several icons to format the text of your post: B (bold), I (italic), U (Underline), ..., Pre, ... etc.

Just highlight the text you want to format and click on the icon. Use Preview button to see what's going on before you (click on) Submit Post.

Give it a try

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.