Using FILTER/UNIQUE on text, numbers

[danhendo888]

I download the below spreadsheet daily from our software. It shows details of invoices paid/booked.
*It doesn't show it below but the first three columns have the little green ribbon on the top left corner of the cell and is aligned to the left of the cell and has General formatting.

Company Number	Invoice Number	Order Number	Order Date	Order Tax	Order Subtotal	Currency	Payment Date
1111111111	ABC123 y	8190090120	3/12/2024	10.00	100.00	AUD
1111111111	ABC123 y	8190090120	3/12/2024	50.00	500.00	AUD
1111111111	ABC123 y	8190090120	3/12/2024	0.00	0.00	AUD	3/14/2024
1111111111	ABC123 y	8190090120	3/12/2024	0.00	0.00	AUD	3/14/2024
2222222222	111222000	8190090000	1/1/2024	0.00	10,000.00	AUD

Below is my own spreadsheet. I type in the invoice number and retrieve the Order Number, Order Total, and Payment Date from the downloaded file.
The first column has the little green ribbon at the top left corner also.
The last three columns is the output I wish to achieve

Company Number	Invoice Number	Order Number	Order Total	Payment Date
1111111111	ABC123	8190090120	660	3/14/2024
2222222222	111222000	8190090000	10,000.00

Order Number:
To retrieve the Order Number, I use FILTER and UNIQUE. It retrieves it successfully when the invoice number in my spreadsheet has text formatting e.g. ABC123
But it doesn't work when the invoice number in my spreadsheet has number formatting because the invoice numbers in the downloaded file does not have number formatting. How can I make the formula work for both text and numbers.

Also, how can I retrieve the correct order number when the correct invoice number is "ABC123" but it was incorrectly booked as "ABC123 y"

Order Total:
I use two SUMIFS to add the Order Tax and Order Subtotal columns together.
Is there a more efficient way to add these? Or is this okay to use.

Payment Date:
How can I retrieve the payment date for invoice ABC123? The date does not necessarily show in all rows in the extracted data

 

[danhendo888]

Is it true that when you filter using Company Number and Invoice Number that you will obtain multiple entries from the Daily sheet, and...

1. all of those entries will have the same Order Number, and
2. the Payment Date for that subset of entries will either be blank or share the same date?

Hi Kirk, thank you very much for your help, I appreciate it

1. all of those entries will have the same Order Number --> Some invoice numbers will have multiple/different Order Numbers
2. the Payment Date for that subset of entries will either be blank or share the same date? Yes, correct. The Max formula will work great.

For invoice numbers which have multiple Order Numbers, what would you change in your formula?

 

[KRice]

I wanted to caution you about something with the ISNUMBER(SEARCH feature. It can cause false positives when the 1st argument of SEARCH (find_text) is blank...then it returns a match nearly everywhere. I realized this when I tried reworking the formula a bit to use the Payment Date method shown by @JEC...the double reciprocal is a clever way to force an error when no date is present, yet return the date when one is. That formula too would be vulnerable to this same issue. If you'll never need to worry about a blank Invoice Number in your worksheet, then this isn't an issue. If you might have a blank, then another error trap is probably needed. Also, I must not have read the initial problem clearly, as I also included Company Name in the filter. That may not be an issue either, as the Company Name and Invoice Number may always be a 1:1 relationship. But if an invoice number is associated with more than one company, then that is an issue.
To clean up the formula, and condense it further, and borrowing the payment date idea from @JEC something like this will handle the blank Invoice Number...which intentionally throws an error result that is later converted to "not found":

MrExcel_20240315.xlsx
	A	B	C	D	E
1	Company Number	Invoice Number	Order Number	Order Total	Payment Date
2	1111111111	ABC123	8190090120	660.00	3/14/2024
3	2222222222	111222000	8190090000	10,000.00	none
4	333		not found
MyOwnWkSht

Cell Formulas
Range	Formula
C4,C2:E3	C2	=LET(d,Daily!$A$2:$H$7,f,IF(B2="",#N/A,FILTER(d,ISNUMBER(SEARCH(B2,INDEX(d,,2))))),IFERROR(HSTACK(INDEX(f,1,3),SUM(CHOOSECOLS(f,5,6)),IFERROR(1/(1/MAX(TAKE(f,,-1))),"none")),"not found"))
Dynamic array formulas.

For invoice numbers which have multiple Order Numbers, what would you change in your formula?

What do you want to do with multiple order numbers? Right now, you enter just the Invoice Number and extract all data that matches it. Do you want subtotals for each of the unique order numbers in that subset? Or are you saying that you'd like to initially filter on both the Invoice Number and an inputted Order Number?

 

[danhendo888]

Daily
Company Number	Invoice Number	Order Number	Order Tax	Order Subtotal	Payment Date
1111111111	DEF789	7777777777	2	22	12/12/2024
1111111111	ABC123 z	8888888888	50	500
1111111111	ABC123 y	9999999999	100	1000	3/14/2024
2222222222	ZZZZZZ	AAAAAAAAA	1	10
MyOwn(start)
Company Number	Invoice Number		Order Number	Order Total	Payment Date
1111111111	DEF789
1111111111	ABC123
2222222222	ZZZZZZ
MyOwn(end)
Company Number	Invoice Number	Count of Order Numbers	Order Number	Order Total	Payment Date
1111111111	DEF789	1	7777777777	24	12/12/2024
1111111111	ABC123	2	8888888888	550
			9999999999	1100	3/14/2024
2222222222	ZZZZZZ	1	AAAAAAAAA	11

Yes, the same output for each order number.

Since some invoice numbers can have multiple order numbers, I'm not sure how best to achieve the desired output.
Where there are multiple order numbers, since the results will spill but cannot spill if there is data in the rows below, perhaps I should insert a new column notifying me of the count of order numbers? So I can manually insert new rows for the formula to spill? Not sure though

Noted on the revised formula regarding the ISNUMBER feature, thank you.

 

[KRice]

I agree about the spill issue. There are a few options. You could do a count as you've suggested, which tells you to dig deeper. You could spill sets of results horizontally...a set for each order number. Or you could abandon reporting on the table structure defined by your inputted Invoice Numbers and create an entirely new results table that spills down, shifting lower result blocks so that that don't interfere with those above...I think that's doable, but more complicated. Do you have a preference?

Tell me about the order 888... result line. The Payment Date is blank? So that's an extension of the same idea. The filtered data set would be based on a unique order number and a, perhaps, common invoice number. ...and the order total and payment date would be reflect only those items.

 

[danhendo888]

I agree about the spill issue. There are a few options. You could do a count as you've suggested, which tells you to dig deeper. You could spill sets of results horizontally...a set for each order number. Or you could abandon reporting on the table structure defined by your inputted Invoice Numbers and create an entirely new results table that spills down, shifting lower result blocks so that that don't interfere with those above...I think that's doable, but more complicated. Do you have a preference?

Tell me about the order 888... result line. The Payment Date is blank? So that's an extension of the same idea. The filtered data set would be based on a unique order number and a, perhaps, common invoice number. ...and the order total and payment date would be reflect only those items.

I don't have a preference on the spill ideas. I am happy to apply any method.

Regarding invoice number ABC123.
Our supplier sends us this invoice and I would enter the invoice into the accounting software. We can book the whole amount of the invoice once (i.e. 1 x Order Number for 1 x invoice)
Or we can book half of it now, half of it later (2 x Order Numbers for 1 x invoice).

And that's why the payment date exists for one order number but not another. Because they can be paid at separate times.

 

[danhendo888]

Works beautifully, thank you.

Can it also be spilled horizontally like the below? (Ignoring all other issues)

Company Number	Invoice Number	Order Number	Order Total	Payment Date
1111111111	ABC123	8190090120	110	3/13/2024
		8190090144	15	3/14/2024

 

[danhendo888]

Much appreciated Kirk!
I am learning so much from your posts, thank you

And sincere apologies for not including all the necessary information in the OP

 

[KRice]

You're welcome...I'm happy to help. I don't quite have the same error trapping in the vertical spilling versions, but I'm not sure if that is a problem either. Have a look and let me know if you encounter any problems or have any questions about the formula. One thing I noted is that a blank invoice number on the input side produces an error, but the cause is fairly obvious as there is nothing to lookup.

 

[danhendo888]

intres, BYROW(m, LAMBDA(r, LET(cn, INDEX(r, 1), in, INDEX(r, 2)

Regarding Option 2 in cell Y2, specifically the part above.
It looks like it's defining cn and in within m but m is the below (correct?):

1111111111	ABC123
	111222000
3333333333	111552000
	11144000

Why is the INDEX (r,1) and (r,2) targeting the rows? And not the columns?

 

[danhendo888]

Also, is it possible to search not just for the invoice number but by company number AND the invoice number (for instances where the same invoice number is issued by two different companies?
I tried adding INDEX(d,,1)=cn* to f but it's not quite right