How to retrieve two dates for one invoice number

D

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows
[TABLE="width: 199"]
<tbody>[TR]
[TD]Invoice Number[/TD]
[TD]Payment Date[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]7/01/2019[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]7/25/2019[/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I have instances where a customer will pay one invoice split into multiple payments.

E.g.
Half of invoice 1001 was paid on 1 July.
Other half was paid on 25 July.

In this example, I want to be able to search for invoice 1001 and have a formula that shows me the payment dates corresponding to that invoice number.

How do I go about this?

Thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
this is one way


Book1
ABCDEFGH
1Invoice NumberPayment DateInvoice NumberPayment Date
2100101/07/2019100101/07/201925/07/201908/08/201922/08/2019
3100125/07/2019100201/08/201929/08/201905/09/201912/09/2019
4100201/08/2019100315/08/2019
5100108/08/2019
6100315/08/2019
7100122/08/2019
8100229/08/2019
9100205/09/2019
10100212/09/2019
Sheet1
Cell Formulas
RangeFormula
E2{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
AlanY said:
this is one way

ABCD
EFGH
Invoice NumberPayment DateInvoice NumberPayment Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]01/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]01/07/2019[/TD]
[TD="align: right"]25/07/2019[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD="align: right"]22/08/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]25/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"]05/09/2019[/TD]
[TD="align: right"]12/09/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]15/08/2019
[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]15/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]22/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]05/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]12/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thank you very much, I appreciate it.

Question - how did you paste the excel table into your post?
 
Upvote 0
AlanY said:
this is one way

ABCDEFGH
Invoice NumberPayment DateInvoice NumberPayment Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]01/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]01/07/2019[/TD]
[TD="align: right"]25/07/2019[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD="align: right"]22/08/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]25/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"]05/09/2019[/TD]
[TD="align: right"]12/09/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]15/08/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]15/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]22/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]05/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]12/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

For the formula in E2:
Why doesn't adding ​
"*"&​
work? As in:

{=IFERROR(​
SMALL(IF($A$2:$A$10="*"&$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),""​
)}


For example, suppose the invoice list was:
M1000
M1001
M1002

And my search criteria was 1001

Is there a way to incorporate *?
​
 
Upvote 0
You could try this version, copied across and down.


Excel 2016
ABCDEFGHI
1Invoice NumberPayment DateInvoice NumberPayment Date
2ABC10011/07/201910011/07/201925/07/20198/08/201922/08/2019
3ABC100125/07/201910021/08/201929/08/20195/09/201912/09/2019
4M10021/08/2019100315/08/2019
5ABC10018/08/2019
6XX100315/08/2019
7ABC100122/08/2019
8M100229/08/2019
9M10025/09/2019
10M100212/09/2019
11
Invoice Dates
Cell Formulas
RangeFormula
E2=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(RIGHT($A$2:$A$10,LEN($D2))=$D2&""),COLUMNS($E:E))),"")
 
Upvote 0
Peter_SSs said:
You could try this version, copied across and down.

Excel 2016
ABCDEFGHI
Invoice Number
ABC1001
ABC1001
M1002
ABC1001
XX1003
ABC1001
M1002
M1002
M1002

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Payment Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Invoice Number[/TD]
[TD="align: right"]Payment Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1/07/2019[/TD]
[TD="align: right"]25/07/2019[/TD]
[TD="align: right"]8/08/2019[/TD]
[TD="align: right"]22/08/2019[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]25/07/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]1/08/2019[/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"]5/09/2019[/TD]
[TD="align: right"]12/09/2019[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]15/08/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]15/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]22/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]29/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]12/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Invoice Dates​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(RIGHT($A$2:$A$10,LEN($D2))=$D2&""),COLUMNS($E:E))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
That works beautifully.
Thank you, Peter_SSs, I appreciate it :D
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
A semi-advanced matching problem which may be solved with formulization or VBA
Replies
1
Views
340
The Godfather
T
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
305
John_w
J
A
  • Solved
Filter data in listbox on form based on matching part of item in two comboboxes
Replies
9
Views
114
Ali M
A
megera716
  • Solved
Return one of two dates depending on (numeric) day
Replies
3
Views
412
megera716
megera716
D
  • Solved
Using FILTER/UNIQUE on text, numbers
2 3 4
Replies
32
Views
2K
KRice
K

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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