richmcgill
Board Regular
- Joined
- Feb 4, 2019
- Messages
- 86
- Office Version
- 2016
- Platform
- Windows
I have two tabs. The first tab has a list of numbers numbers with dates. The second tab has the same loan numbers but has amounts with the account number.
I know a VLOOKUP will work here but my boss is wanting a formula. I know a pivot table will work.
So if I had a cell with a date and can change at will for this instance 06/01/2019 make the the column populate with the account numbers and the amounts with the account number. I only want the accounts (not a count) that match the date and populate the amounts on the other tab.
What would this formula look like?
[TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]First Tab
Loan Number [/TD]
[TD]Paid Date[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD]8/1/2019[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD]7/1/2019[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD]6/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
Second Tab
[TABLE="width: 195"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Loan No[/TD]
[TD]Compare Interest[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]-694.17[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]-691.04[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]-630.2[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]-617.61[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]-601.83[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]-572.33[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
I know a VLOOKUP will work here but my boss is wanting a formula. I know a pivot table will work.
So if I had a cell with a date and can change at will for this instance 06/01/2019 make the the column populate with the account numbers and the amounts with the account number. I only want the accounts (not a count) that match the date and populate the amounts on the other tab.
What would this formula look like?
[TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]First Tab
Loan Number [/TD]
[TD]Paid Date[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD]8/1/2019[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD]7/1/2019[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD]6/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
Second Tab
[TABLE="width: 195"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Loan No[/TD]
[TD]Compare Interest[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]-694.17[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]-691.04[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]-630.2[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]-617.61[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]-601.83[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]-572.33[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]