Copying contents of a cell based on a 'match' from two columns

L

LMaeExcel

New Member
Joined
Jan 3, 2017
Messages
14
Hello,

I am trying to collect data from different sheets into a table. I’dlike the table to auto-fill based on the date of the day, and matching the name. The formula shouldn’t count entries, but rather report or copy the number entered in the cell for that person on that date.

Example:

Table should populate from a sheet that looks something like this:

[TABLE="width: 284"]
<tbody>[TR]
[TD="width: 379, bgcolor: transparent, colspan: 4"]
ORDERS​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
BILL​
[/TD]
[TD="width: 92, bgcolor: transparent"]
JOHN​
[/TD]
[TD="width: 92, bgcolor: transparent"]
GARY​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
7/10/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[TD="width: 92, bgcolor: transparent"]
4​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/15/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/16/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/17/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[TD="width: 92, bgcolor: transparent"]
4​
[/TD]
[TD="width: 92, bgcolor: transparent"]
3​
[/TD]
[/TR]
[TR]
[TD="width: 103"]
8/18/2018​
[/TD]
[TD="width: 92"]
0​
[/TD]
[TD="width: 92"]
0​
[/TD]
[TD="width: 92"]
0​
[/TD]
[/TR]
[TR]
[TD="width: 103"]
8/19/2018​
[/TD]
[TD="width: 92"]
0​
[/TD]
[TD="width: 92"]
1​
[/TD]
[TD="width: 92"]
0​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/20/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
0​
[/TD]
[TD="width: 92, bgcolor: transparent"]
3​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/21/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[TD="width: 92, bgcolor: transparent"]
2​
[/TD]
[TD="width: 92, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]
8/22/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"]
0​
[/TD]
[TD="width: 92, bgcolor: transparent"]
8​
[/TD]
[TD="width: 92, bgcolor: transparent"]
10​
[/TD]
[/TR]
</tbody>[/TABLE]

And auto-fill the table that looks something like this:

[TABLE="width: 366"]
<tbody>[TR]
[TD="width: 103, bgcolor: transparent"]
8/22/2018​
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
REFERRALS​
[/TD]
[TD="width: 92, bgcolor: transparent"]
ORDERS​
[/TD]
[TD="width: 92, bgcolor: transparent"]
OUTREACH​
[/TD]
[TD="width: 92, bgcolor: transparent"]
COMPLETED ​
[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]BILL
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]JOHN
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]GARY
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

So, if my formula were to be working properly with the example data above, my table would look like:

[TABLE="width: 366"]
<tbody>[TR]
[TD="width: 103, bgcolor: transparent"]
8/22/2018
​
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
REFERRALS​
[/TD]
[TD="width: 92, bgcolor: transparent"]
ORDERS​
[/TD]
[TD="width: 92, bgcolor: transparent"]
OUTREACH​
[/TD]
[TD="width: 92, bgcolor: transparent"]
COMPLETED ​
[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]BILL
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
0​
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]JOHN
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
8​
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]GARY
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]
10​
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated!
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi,

May be something like this may help:


Book1
ABCDE
1ORDERS
2BILLJOHNGARY
37/10/2018241
48/15/2018212
58/16/2018212
68/17/2018143
78/18/2018000
88/19/2018010
98/20/2018031
108/21/2018221
118/22/20180810
12
13
148/22/2018
15REFERRALSORDERSOUTREACHCOMPLETED
16BILL 0
17JOHN8
18GARY10
Sheet199
Cell Formulas
RangeFormula
B16=IF(B$15=$A$1,SUMPRODUCT(($A$3:$A$11=$A$14)*($B$2:$D$2=$A16)*$B$3:$D$11),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
153
Ltuk85
L
V
  • Question Question
Need help with excel formulas
Replies
5
Views
233
victoriar
V
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
337
chris_bosten
C
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
186
kvsrinivasamurthy
K
k3yn0t3
  • Solved
XLOOKUP (multiple criteria) not working
Replies
3
Views
497
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top