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

 

[shift-del]

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.

Format the field "Invoice Number" in the second sheet as text.

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

Try using wildcard character *.

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.

SUMIFS() is okay.

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

Try MAX()/MIN().

 

[KRice]

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?

 

[KRice]

Here is one approach: The initial Daily data range is defined as variable "data", and assumed column order is as shown in your example. The Company Number (cnum) and Invoice Number (inum) columns are assigned to variables for convenient referencing. The main data range is filtered and assigned to variable "fd". The filtering uses an ISNUMBER(SEARCH method for determining if the Invoice Number matches the "inum" array. This method is a text-based method, so whether numbers or text are in "inum", the function treats them as text, as it does your input Invoice Number in column B of your own worksheet. Be careful, however, as it matches only the text string, so if you enter an invoice of ABC123 in B2, this will find matches for "ABC123 y" or "xABC123" or 1ABC123z"..., so you will need to be reasonably sure that these types of matches are acceptable. The filtering uses an exact match for Company Number, assumed to be the same data type between both the reference data table and your worksheet. Then the "fd" extracted data is split up into three arrays:

1. "onum" is the Order Number, and assuming all Order Numbers are shown for every entry in the main data table, we TAKE just the first one in the "fd" data;
2. "ototal" is the sum of the Order Tax and Order Subtotal for the extracted "fd" data; and
3. "pmtdt" is the maximum date of the Payment Date in the "fd" data. Because it is conceivable that no date may be shown at all, a follow-up formula "pdt" specifies whether to show "none" for the date or the actual payment date.

Finally, these results are stacked together so that all three results will spill. In the event that the original "fd" FILTER does not find any entries that match the Company Number and Invoice Number inputs, an error will be produced, and we trap this error in the final output by wrapping the results in an IFERROR function that displays "not found" in the Order Number column.

MrExcel_20240315.xlsx
	A	B	C	D	E	F	G	H
1	Company Number	Invoice Number	Order Number	Order Date	Order Tax	Order Subtotal	Currency	Payment Date
2	1111111111	ABC123 y	8190090120	45363	10	100	AUD
3	1111111111	ABC123 y	8190090120	45363	50	500	AUD
4	1111111111	ABC123 y	8190090120	45363	0	0	AUD	3/14/2024
5	1111111111	ABC123 y	8190090120	45363	0	0	AUD	3/14/2024
6
7	2222222222	111222000	8190090000	45292	0	10000	AUD
Daily

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
MyOwn

Cell Formulas
Range	Formula
C4,C2:E3	C2	=LET(data,Daily!$A$2:$H$7,cnum,CHOOSECOLS(data,1),inum,CHOOSECOLS(data,2), fd,FILTER(data,(cnum=A2)*ISNUMBER(SEARCH(B2,inum))), onum,TAKE(CHOOSECOLS(fd,3),1), ototal,SUM(CHOOSECOLS(fd,5,6)), pmtdt,MAX(CHOOSECOLS(fd,8)), pdt,IF(pmtdt=0,"none",pmtdt), IFERROR(HSTACK(onum,ototal,pdt),"not found"))
Dynamic array formulas.

 

[JEC]

A variation

Book1
	A	B	C	D	E	F	G	H
1	Company Number	Invoice Number	Order Number	Order Date	Order Tax	Order Subtotal	Currency	Payment Date
2	1111111111	ABC123 y	8190090120	45363	10	100	AUD
3	1111111111	ABC123 y	8190090120	45363	50	500	AUD
4	1111111111	ABC123 y	8190090120	45363	0	0	AUD	45365
5	1111111111	ABC123 y	8190090120	45363	0	0	AUD	45365
6
7	2222222222	111222000	8190090000	45292	0	10000	AUD
8
9
10
11
12	Company Number	Invoice Number	Order Number	Order Total	Payment Date
13	1111111111	ABC123	8190090120	660	45365
14	2222222222	111222000	8190090000	10000	none
15		33	not found
Sheet1

Cell Formulas
Range	Formula
C15,C13:E14	C13	=LET(c,CHOOSECOLS($A$2:$H$7,3,5,6,8),z,FILTER(c,ISNUMBER(SEARCH(B13,$B$2:$B$7))),IFERROR(HSTACK(MAX(INDEX(z,,1)),SUM(TAKE(DROP(z,,1),,2)),IFERROR(1/(1/(MAX(TAKE(z,,-1)))),"none")),"not found"))
Dynamic array formulas.

 

[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.