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

 

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

 

[KRice]

Here is one idea:
Initial data on Daily worksheet...

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	3/12/2024	10	100	AUD
3	1111111111	ABC123 y	8190090120	3/12/2024	50	500	AUD	3/13/2024
4	1111111111	ABC123	8190090144	3/12/2024	5	10	AUD	3/14/2024
5	1111111111	ABC123 y	8190090222	3/12/2024	6	20	AUD
6		ABC123	8190090222	3/12/2024	7	100	AUD	3/15/2024
7	2222222222	111222000z	8190090000	1/1/2024	3	33	AUD
8	2222222222	111222000 x	8190092222	1/1/2024	4	44	AUD	3/16/2024
9	2222222222	11144000z	8190090111	1/1/2024	5	55	AUD	3/17/2024
10	2222222222	11144000	8190090888	1/1/2024	6	66	AUD
11	2222222222	111552000 g	8190090000	1/1/2024	7	77	AUD	3/8/2024
Daily

Then spilling horizontal arrays with results:

MrExcel_20240315.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Company Number	Invoice Number	Order Number	Order Total	Payment Date	Order Number	Order Total	Payment Date	Order Number	Order Total	Payment Date
2	1111111111	ABC123	8190090120	660.00	3/13/2024	8190090144	15.00	3/14/2024	8190090222	133.00	3/15/2024
3		111222000	8190090000	36.00	none	8190092222	48.00	3/16/2024
4		111552000	8190090000	84.00	3/8/2024
5		11144000	8190090111	60.00	3/17/2024	8190090888	72.00	none
6			not found
MyOwn2

Cell Formulas
Range	Formula
C2:K2,C6,C5:H5,C4:E4,C3:H3	C2	=LET(d,Daily!$A$2:$H$11,f,IF(B2="",#N/A,FILTER(d,ISNUMBER(SEARCH(B2,INDEX(d,,2))))), uon,UNIQUE(INDEX(f,,3)), son,BYROW(uon,LAMBDA(r,SUM(CHOOSECOLS(f,5,6)*(CHOOSECOLS(f,3)=r)))), pdon,BYROW(uon,LAMBDA(r,IFERROR(1/(1/MAX(CHOOSECOLS(f,8)*(CHOOSECOLS(f,3)=r))),"none"))), res,IFERROR(HSTACK(uon,son,pdon),"not found"),TOROW(res))
Dynamic array formulas.

 

[KRice]

Yes, but that becomes more complicated because we can't actually shift your input cells (Company Number and Invoice Number or your worksheet). But we can construct a new table that uses those two input columns, replicating the company number and invoice number for each Order Number subgroup associated with them. This involves spilling results across and down the page...and that's where the challenge resides.

In the above solution, we have each inputted Company Number and Invoice Number first being used to filter the Daily data to extract a subset for the inputted Invoice Number. And because the invoice number might have several order numbers associated with it, we first create a vertical array of unique order numbers (uon) for that invoice number. Then we feed the "uon" array, row-by-row, into BYROW LAMBDA functions to generate two single column spilling arrays for the sum of amounts associated with each order number (son), and for the payment dates for each order number (pdon). Those three vertical arrays are stacked together horizontally to create a 3-column array consisting of some number of rows (the number of orders numbers associated with the subject invoice number). Then the 3-column array is converted to a single horizontal spilling array using the TOROW function. This gets us the needed content in a relatively straightforward manner.

We can use this general idea down to the final steps, but instead of converting the two-dimensional array into a single horizontal spilling array, I first include the inputted company number and invoice number in each set of data and append a row delimiter "//" after the payment date. Then all results for that invoice number are joined together, each separated by a column delimiter "|". This creates a single cell output consisting of all data for the invoice number, and it looks similar to this:

1111111111|ABC123|8190090120|660|45364|//|1111111111|ABC123|8190090144|15|45365|//|1111111111|ABC123|8190090222|133|45366|//

But we want a similar result for each row (invoice number) in your input table. So everything mentioned above in this paragraph is wrapped inside a BYROW LAMBDA function, where your input table (columns A:B...company number and invoice number) are fed, row by row, into the functions that summarize the data for each of the order numbers associated with the invoice number. The end result is a single column spilling array, each row resembling the example above. I've called this single column "intres"...for intermediate results.

1111111111|ABC123|8190090120|660|45364|//|1111111111|ABC123|8190090144|15|45365|//|1111111111|ABC123|8190090222|133|45366|//
0|111222000|8190090000|36|none|//|0|111222000|8190092222|48|45367|//
3333333333|111552000|8190090000|84|45359|//
0|11144000|8190090111|60|45368|//|0|11144000|8190090888|72|none|//

This format is well-suited for spilling results down the page. For these types of problems, there are some options.

Option 1:
At times, rather than reinvent the wheel, I've relied on previously developed formulas. One that is tailor made for this problem is a LAMBDA function called ROW BYROW, or RBYROW described here: AUNIQUE
To use it, you would use the Excel Name Manager (or Advanced Formula Environment) to create the name RBYROW, and indicate that it is designed to accept two arguments (the array and some function). Then it can be used to transform the intermediate results..."intres". After the results are spilled, some cleanup and formatting is necessary to convert text to numbers where appropriate and replace 0's and errors with blanks and meaningful messages.

Option 2:
We can also condition the intermediate results, adding or deleting some of the row/column delimiters to ensure the correct structure in the next step. The entire single column array of "intres" is then combined into a single cell and then split apart using the row/column delimiters. Then the same cleanup and formatting is performed. The advantage is that the separate LAMBDA function does not need to be created. A potential disadvantage is that the CONCAT function might cause a problem should the cell size limit exceed Excel's limit of 32,767 characters. I doubt there will be so many order numbers to cause this problem, but you would know better.

One final note: I'm assuming that you may not necessarily know where the bottom of the Daily data table is...unless you look and then adjust the range in the formula. For convenience, a dynamic array is formed. We assume that we can look down the 2nd column of the Daily worksheet and the lowest non-blank cell represents the bottom of the Daily data table. That row number is assigned to the variable "lrowd"...last row daily. And a dynamic range is created of the Daily data, called "d", which assumes the data begin on row 2 of the Daily sheet...and columns A:H are where the data reside. Any of these can be adjusted to reflect reality. Similarly, I've assumed your Company Number and Invoice Number inputs are in columns A:B, and these are assigned to variable "mine"...because they are yours. Then we can look for the lowest non-blank cell in column B, assign that row number to "lrowm"...last row mine, and create a dynamically formed array of your inputs, called "m". These assignments are done first thing in the formula, and should be the only part that needs to be adapted to your workbook. Everything below this in the formula is drawn from these arrays.

For this Daily data set:

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	3/12/2024	10	100	AUD
3	1111111111	ABC123 y	8190090120	3/12/2024	50	500	AUD	3/13/2024
4	1111111111	ABC123	8190090144	3/12/2024	5	10	AUD	3/14/2024
5	1111111111	ABC123 y	8190090222	3/12/2024	6	20	AUD
6		ABC123	8190090222	3/12/2024	7	100	AUD	3/15/2024
7	2222222222	111222000z	8190090000	1/1/2024	3	33	AUD
8	2222222222	111222000 x	8190092222	1/1/2024	4	44	AUD	3/16/2024
9	2222222222	11144000z	8190090111	1/1/2024	5	55	AUD	3/17/2024
10	2222222222	11144000	8190090888	1/1/2024	6	66	AUD
11	3333333333	111552000 g	8190090000	1/1/2024	7	77	AUD	3/8/2024
12
Daily

Here are Options 1 (cell S2) and 2 (cell Y2), with your initial query inputs in columns A:B. I think Option 2 might be easier to implement. I've left Company Number, Invoice Number, and Order Number as text. If any/all should be true numbers, the final output can be adjusted in the formula (I wasn't sure if any of these might have leading 0's that would be dropped in a number format).

MrExcel_20240315.xlsx
	A	B																R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Company Number	Invoice Number																	Company Number	Invoice Number	Order Number	Order Total	Payment Date		Company Number	Invoice Number	Order Number	Order Total	Payment Date
2	1111111111	ABC123																	1111111111	ABC123	8190090120	660.00	3/13/2024		1111111111	ABC123	8190090120	660.00	3/13/2024
3		111222000																	1111111111	ABC123	8190090144	15.00	3/14/2024		1111111111	ABC123	8190090144	15.00	3/14/2024
4	3333333333	111552000																	1111111111	ABC123	8190090222	133.00	3/15/2024		1111111111	ABC123	8190090222	133.00	3/15/2024
5		11144000																		111222000	8190090000	36.00	none			111222000	8190090000	36.00	none
6																				111222000	8190092222	48.00	3/16/2024			111222000	8190092222	48.00	3/16/2024
7																			3333333333	111552000	8190090000	84.00	3/8/2024		3333333333	111552000	8190090000	84.00	3/8/2024
8																				11144000	8190090111	60.00	3/17/2024			11144000	8190090111	60.00	3/17/2024
9																				11144000	8190090888	72.00	none			11144000	8190090888	72.00	none
10
MySummary

Cell Formulas
Range	Formula
S2:W9	S2	=LET(daily, Daily!A:H, lrowd, LOOKUP(2, 1 / (INDEX(daily, , 2) <> ""), ROW(INDEX(daily, , 2))), d, Daily!A2:INDEX(Daily!A:H, lrowd, ), mine, A:B, lrowm, LOOKUP(2, 1 / (INDEX(mine, , 2) <> ""), ROW(INDEX(mine, , 2))), m, A2:INDEX(A:B, lrowm, ), intres, BYROW(m, LAMBDA(r, LET(cn, INDEX(r, 1), in, INDEX(r, 2), f, IF(in = "", #N/A, FILTER(d, ISNUMBER(SEARCH(in, INDEX(d, , 2))))), uon, SORT(UNIQUE(INDEX(f, , 3))), son, BYROW(uon, LAMBDA(r, SUM(CHOOSECOLS(f, 5, 6) * (CHOOSECOLS(f, 3) = r)))), pdon, BYROW(uon, LAMBDA(r, IFERROR(1 / (1 / MAX(CHOOSECOLS(f, 8) * (CHOOSECOLS(f, 3) = r))), "none"))), res, IFERROR(HSTACK(r, uon, son, pdon, "//"), HSTACK(r, "not found", "", "", "//")), TEXTJOIN("|", , res) ) )),sr,RBYROW(intres, LAMBDA(r, TEXTSPLIT(r, "|", "//", 1, , ""))),HSTACK(IF(CHOOSECOLS(sr,1)+0=0,"",CHOOSECOLS(sr,1)), CHOOSECOLS(sr,2,3),CHOOSECOLS(sr,4)+0,IFERROR(CHOOSECOLS(sr,5)+0,"none")))
Y2:AC9	Y2	=LET(daily, Daily!A:H, lrowd, LOOKUP(2, 1 / (INDEX(daily, , 2) <> ""), ROW(INDEX(daily, , 2))), d, Daily!A2:INDEX(Daily!A:H, lrowd, ), mine, A:B, lrowm, LOOKUP(2, 1 / (INDEX(mine, , 2) <> ""), ROW(INDEX(mine, , 2))), m, A2:INDEX(A:B, lrowm, ), intres, BYROW(m, LAMBDA(r, LET(cn, INDEX(r, 1), in, INDEX(r, 2), f, IF(in = "", #N/A, FILTER(d, ISNUMBER(SEARCH(in, INDEX(d, , 2))))), uon, SORT(UNIQUE(INDEX(f, , 3))), son, BYROW(uon, LAMBDA(r, SUM(CHOOSECOLS(f, 5, 6) * (CHOOSECOLS(f, 3) = r)))), pdon, BYROW(uon, LAMBDA(r, IFERROR(1 / (1 / MAX(CHOOSECOLS(f, 8) * (CHOOSECOLS(f, 3) = r))), "none"))), res, IFERROR(HSTACK(r, uon, son, pdon, "//"), HSTACK(r, "not found", "", "", "//")), TEXTJOIN("|", , res) ) )),sr,TEXTSPLIT(CONCAT(SUBSTITUTE(CONCAT("|",intres),"|//","//")),"|","//",1,,""),HSTACK(IF(CHOOSECOLS(sr,1)+0=0,"",CHOOSECOLS(sr,1)), CHOOSECOLS(sr,2,3),CHOOSECOLS(sr,4)+0,IFERROR(CHOOSECOLS(sr,5)+0,"none")))
Dynamic array formulas.

I've probably made this harder than it needs to be, but either of these should work.

 

[KRice]

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

Good question. After trimming down the full column references A:B to include only your data in a variable called "m", we feed the "m" data into a BYROW LAMBA function. This takes "m" data one row at a time...intres, BYROW(m, LAMBDA(r,...
So within the actual LAMBDA function, this row of the "m" data is referred to as "r"...short for "row". In this case, since we have a single row of data, we can refer to the 1st element (in the 1st column index position) as INDEX(r,1)...which incidentally could also be INDEX(r,,1). Similarly INDEX(r,2) gets us the 2nd element in the array, but we could also use INDEX(r,,2). Intuitively, it might make more sense to use the column nomenclature...and I think that would be a good revision. Interestingly, Excel interprets these the same when we have a single horizontal array/range, but not when we have a single vertical array/range.

MrExcel_20240315.xlsx
	AL	AM	AN	AO	AP
12	a	b	c	a	a
13				b	b
14				c	c
15	a	a	#REF!	#REF!	a
16	b	b			b
17	c	c			c
MySummary

Cell Formulas
Range	Formula
AO12	AO12	=INDEX($AL$12:$AN$12,1)
AO13	AO13	=INDEX($AL$12:$AN$12,2)
AO14	AO14	=INDEX($AL$12:$AN$12,3)
AM15:AM17	AM15	=INDEX($AL$15:$AL$17,,1)
AN15	AN15	=INDEX($AL$15:$AL$17,,2)
AO15	AO15	=INDEX($AL$15:$AL$17,,3)
AP12	AP12	=INDEX($AL$12:$AN$12,,1)
AP13	AP13	=INDEX($AL$12:$AN$12,,2)
AP14	AP14	=INDEX($AL$12:$AN$12,,3)
AP15	AP15	=INDEX($AL$15:$AL$17,1)
AP16	AP16	=INDEX($AL$15:$AL$17,2)
AP17	AP17	=INDEX($AL$15:$AL$17,3)
Dynamic array formulas.

 

[KRice]

I've revisited the TEXTJOIN formula and have a version that requires exact matching for Company and fuzzy matching for Invoice Number. I also stepped through each part to address some nagging error conditions, and in doing so, discovered a problem with the original "res" function that uses HSTACK. One of the arguments was "r" so that the "cn" and "in" values would be captured, but that's a problem when more than one order number satisfies the filter criteria. Recall that in "res", we are stacking vertical arrays of uon, son, and pdon (unique order number, sum of common unique order numbers, and latest payment date among the common unique order numbers)...so these arrays have the same length as the number of unique order numbers. Therefore, HSTACK(r... or HSTACK(cn,in... doesn't work. We need a vertical array of each of those values, so some new variables are found in the formula: We determine the count of unique order numbers for each by-row query of customer number/invoice number...that count is variable "con". Then four vertical arrays are formed, each having a length of "con":

1. an array of delimiters to be used at the end of each row in each order block, called "era", because an array of "//" delimiters is needed for the same reason just mentioned,
2. an array of blanks used for error trapping, just in case either "cn" or "in" is blank, called "blnks",
3. an array of "cn" values, called "cna", and
4. an array of "in" values, called "ina".

Then by changing the error trapping to deal with certain situations (nothing found by FILTER in "f" returns a blank array, and known values 0 or "none" are used by "son" and "pdon", respectively), the formula should be more robust. I also revised the text string manipulation in "sr" somewhat to sequentially tidy up the end of row delimiters that are concatenated with column delimiters during the TEXTJOIN operation.

Here is the data set I've been using for testing:

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	Tesla	ABC123 y	8190090120	3/12/2024	10	100	AUD
3	Ford	ABC123 y	8190090144	3/12/2024	50	500	AUD	3/13/2024
4	Ford	ABC123	8190090120	3/12/2024	5	10	AUD	3/14/2024
5	1133Opel	ABC123 q	8190090222	3/12/2024	6	20	AUD	3/20/2024
6	1133Opel	ABC123 y	8190090999	3/12/2024	20	99	AUD	3/18/2024
7	Fiat	ABC123	8190090222	3/12/2024	7	100	AUD	3/15/2024
8	777	111222z	8190090000	1/1/2024	3	33	AUD
9	777	111222 x	8190092222	1/1/2024	4	44	AUD	3/16/2024
10	VW44	11144z	8190090111	1/1/2024	5	55	AUD	3/17/2024
11	VW44	11144	8190090888	1/1/2024	6	66	AUD
12	333	111552 g	8190090000	1/1/2024	7	77	AUD	3/8/2024
13	888	111222
14	888	111222	ord1		1.5	12.5	AUD	3/19/2024
15	Tesla	ABC123 zzz	8190090120	3/12/2024	10	100	AUD
16	Ford	ABC123 y	8190090144	3/13/2024	5	55	AUD	3/17/2024
Daily

...and the revised formula:

MrExcel_20240315.xlsx
	A	B																						X	Y	Z	AA	AB	AC
1	Company Number	Invoice Number																							Company Number	Invoice Number	Order Number	Order Total	Payment Date
2	Ford	ABC123																							Ford	ABC123	8190090120	15.00	3/14/2024
3	777	111222																							Ford	ABC123	8190090144	610.00	3/17/2024
4	333	111552																							777	111222	8190090000	36.00	none
5	VW44	11144																							777	111222	8190092222	48.00	3/16/2024
6	Tesla	ABC123																							333	111552	8190090000	84.00	3/8/2024
7		xyz																							VW44	11144	8190090111	60.00	3/17/2024
8	1133Opel	ABc123																							VW44	11144	8190090888	72.00	none
9	Fiat	ABc123																							Tesla	ABC123	8190090120	220.00	none
10	888	111222																								xyz		-	none
11																									1133Opel	ABc123	8190090222	26.00	3/20/2024
12																									1133Opel	ABc123	8190090999	119.00	3/18/2024
13																									Fiat	ABc123	8190090222	107.00	3/15/2024
14																									888	111222	ord1	14.00	3/19/2024
15																									888	111222		-	none
MySummary2

Cell Formulas
Range	Formula
Y2:AC15	Y2	=LET(daily, Daily!A:H, lrowd, LOOKUP(2, 1 / (INDEX(daily, , 2) <> ""), ROW(INDEX(daily, , 2))), d, Daily!A2:INDEX(Daily!A:H, lrowd, ), mine, A:B, lrowm, LOOKUP(2, 1 / (INDEX(mine, , 2) <> ""), ROW(INDEX(mine, , 2))), m, A2:INDEX(A:B, lrowm, ), intres, BYROW(m, LAMBDA(r, LET( cn, INDEX(r, , 1), in, INDEX(r, , 2), f, FILTER(d, (INDEX(d, , 1) = cn) * ISNUMBER(SEARCH(in, INDEX(d, , 2))), EXPAND("",1,COLUMNS(d),"")), uon, SORT(UNIQUE(INDEX(f, , 3))), con, COUNTA(uon), era, EXPAND("//",con,,"//"), blnks, EXPAND("",con,,""), cna, IFERROR(EXPAND(cn,con,,cn),blnks), ina, IFERROR(EXPAND(in,con,,in),blnks), son, BYROW(uon, LAMBDA(r, IFERROR(SUM(CHOOSECOLS(f, 5, 6) * (CHOOSECOLS(f, 3) = r)), 0))), pdon, BYROW(uon, LAMBDA(r, IFERROR(1 / (1 / MAX(CHOOSECOLS(f, 8) * (CHOOSECOLS(f, 3) = r))), "none"))), res, HSTACK(cna,ina,uon, son, pdon,era), TEXTJOIN("|",0, res) ))), sr, DROP(TEXTSPLIT(CONCAT(SUBSTITUTE(SUBSTITUTE(intres, "|//|", "//"),"|//","//")), "|", "//",0),-1), finres, HSTACK(CHOOSECOLS(sr, 1,2,3), --CHOOSECOLS(sr,4), IFERROR(--CHOOSECOLS(sr,5),"none")), finres )
Dynamic array formulas.

 

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

 

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