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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
162
Ltuk85
L
V
  • Question Question
Need help with excel formulas
Replies
5
Views
238
victoriar
V
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff
E
  • Solved
combine data from two worksheets with similar data but different formats
Replies
6
Views
99
DanteAmor
DanteAmor
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
388
chris_bosten
C

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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