take a column of daily transaction numbers with duplicates and show in different table with its corresponding date and no duplicates

[turn7or5]

the duplicate transaction numbers are a result of the same number being applied to each part of a multi-part transaction.
the goal is formulas to show a single count and date for each transaction in a new table whose functions need to know where to look in first table.

the table on the Left (Columns A-B) is the current data. Right table (Columns M-N) has no formulas but is a model of what the end result should look like.

I tried work it out with countif and finding the max transaction count per date knowing that each day begins with no. 1 but did not get far.

If someone knows any solution I would greatly appreciate it.

 

[hagia_sofia]

Hello, are aiming at this:

=UNIQUE(A3:B44)

 

[turn7or5]

I am new to excel so was glad to see a simple solution however it produced a spill error when used in the table.
It seems Excel can't accommodate this type of function in a table.
Is there a modification or even a different formula that will work but from within a table?

 

[Dave Patton]

N.B. You posted just an image of your data.
In future, please post an extract of your data with the forum's tool named XL2BB.

Unique.xlsm
	A	B	C	D	E	F
20
21		Transaction	Date
22		1	29-Aug		1	29-Aug
23		1	29-Aug		2	29-Aug
24		2	29-Aug		1	30-Aug
25		1	30-Aug
26		1	30-Aug		1	29-Aug
27		1	30-Aug		2	29-Aug
28					1	30-Aug
29
30
9h

Cell Formulas
Range	Formula
E22:F24	E22	=UNIQUE(B22:C27)
E26:F28	E26	=UNIQUE(Table8)
Dynamic array formulas.

 

[Peter_SSs]

In future, please post an extract of your data with the forum's tool named XL2BB.

Not sure, but that may not be possible for the OP since their Excel version is not one listed in the XL2BB specifications

@turn7or5
Whilst you possibly may not be able to use XL2BB as mentioned above, you could consider just copy/paste a small set of sample data into your post so that helpers would find it a bit easier to get some sample data to work with rather than having to type it out manually.

Anyway, assuming the tables already exist and, left to right, are Table1 and Table2, see if this type of formula would work for you.

turn7or5.xlsm
	A	B										L	M	N
1	Transaction Number	Date											Transaction	Date
2
3	1	29/09/2022											1	29/09/2022
4	1	29/09/2022											2	29/09/2022
5	2	29/09/2022											3	29/09/2022
6	2	29/09/2022											4	29/09/2022
7	3	29/09/2022											5	29/09/2022
8	3	29/09/2022											6	29/09/2022
9	4	29/09/2022											1	21/06/2023
10	4	29/09/2022
11	5	29/09/2022
12	5	29/09/2022
13	6	29/09/2022
14	6	29/09/2022
15	6	29/09/2022
16	6	29/09/2022
17	1	21/06/2023
18	1	21/06/2023
19	1	21/06/2023
20	1	21/06/2023
21	1	21/06/2023
22	1	21/06/2023
23
24
Sheet1

Cell Formulas
Range	Formula
M2:M22	M2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),1)&"",IFERROR(--v,""))
N2:N22	N2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),2)&"",IFERROR(--v,""))

 

[Dave Patton]

I may be reading the request differently.

turn7or5 stated "the table on the Left (Columns A-B) is the current data. Right table (Columns M-N) has no formulas but is a model of what the end result should look like"

Unique.xlsm
	A	B	C										M	N
1	Transaction Number	Date											Transaction Number	Date
2	1	29-Sep-22											1	29-Sep-22
3	1	29-Sep-22											2	29-Sep-22
4	2	29-Sep-22											3	29-Sep-22
5	2	29-Sep-22											4	29-Sep-22
6	3	29-Sep-22											5	29-Sep-22
7	3	29-Sep-22											6	29-Sep-22
8	4	29-Sep-22											1	21-Jun-23
9	4	29-Sep-22
10	5	29-Sep-22
11	5	29-Sep-22
12	6	29-Sep-22
13	6	29-Sep-22
14	6	29-Sep-22
15	6	29-Sep-22
16	1	21-Jun-23
17	1	21-Jun-23
18	1	21-Jun-23
19	1	21-Jun-23
20	1	21-Jun-23
21	1	21-Jun-23
9hh

Cell Formulas
Range	Formula
M2:N8	M2	=UNIQUE(Table9)
Dynamic array formulas.

 

[Peter_SSs]

turn7or5 stated ...

They also stated (post 3) in relation to a spilling dynamic array formula suggestion very similar to yours

I am new to excel so was glad to see a simple solution however it produced a spill error when used in the table.
It seems Excel can't accommodate this type of function in a table.
Is there a modification or even a different formula that will work but from within a table?

Makes me think the formula is to put in a table.

 

[Dave Patton]

Excel may have built-in features to do what you require.
Have your tried the following?

Select the table that is in columns A and B
Then Data Filter Advanced and select
- copy to another location
- unique records only

 

[turn7or5]

Not sure, but that may not be possible for the OP since their Excel version is not one listed in the XL2BB specifications

View attachment 116223

@turn7or5
Whilst you possibly may not be able to use XL2BB as mentioned above, you could consider just copy/paste a small set of sample data into your post so that helpers would find it a bit easier to get some sample data to work with rather than having to type it out manually.

Anyway, assuming the tables already exist and, left to right, are Table1 and Table2, see if this type of formula would work for you.

turn7or5.xlsm
	A	B										L	M	N
1	Transaction Number	Date											Transaction	Date
2
3	1	29/09/2022											1	29/09/2022
4	1	29/09/2022											2	29/09/2022
5	2	29/09/2022											3	29/09/2022
6	2	29/09/2022											4	29/09/2022
7	3	29/09/2022											5	29/09/2022
8	3	29/09/2022											6	29/09/2022
9	4	29/09/2022											1	21/06/2023
10	4	29/09/2022
11	5	29/09/2022
12	5	29/09/2022
13	6	29/09/2022
14	6	29/09/2022
15	6	29/09/2022
16	6	29/09/2022
17	1	21/06/2023
18	1	21/06/2023
19	1	21/06/2023
20	1	21/06/2023
21	1	21/06/2023
22	1	21/06/2023
23
24
Sheet1

Cell Formulas
Range	Formula
M2:M22	M2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),1)&"",IFERROR(--v,""))
N2:N22	N2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),2)&"",IFERROR(--v,""))

Not sure, but that may not be possible for the OP since their Excel version is not one listed in the XL2BB specifications

View attachment 116223

@turn7or5
Whilst you possibly may not be able to use XL2BB as mentioned above, you could consider just copy/paste a small set of sample data into your post so that helpers would find it a bit easier to get some sample data to work with rather than having to type it out manually.

Anyway, assuming the tables already exist and, left to right, are Table1 and Table2, see if this type of formula would work for you.

turn7or5.xlsm
	A	B										L	M	N
1	Transaction Number	Date											Transaction	Date
2
3	1	29/09/2022											1	29/09/2022
4	1	29/09/2022											2	29/09/2022
5	2	29/09/2022											3	29/09/2022
6	2	29/09/2022											4	29/09/2022
7	3	29/09/2022											5	29/09/2022
8	3	29/09/2022											6	29/09/2022
9	4	29/09/2022											1	21/06/2023
10	4	29/09/2022
11	5	29/09/2022
12	5	29/09/2022
13	6	29/09/2022
14	6	29/09/2022
15	6	29/09/2022
16	6	29/09/2022
17	1	21/06/2023
18	1	21/06/2023
19	1	21/06/2023
20	1	21/06/2023
21	1	21/06/2023
22	1	21/06/2023
23
24
Sheet1

Cell Formulas
Range	Formula
M2:M22	M2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),1)&"",IFERROR(--v,""))
N2:N22	N2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),2)&"",IFERROR(--v,""))

 

[turn7or5]

I may be reading the request differently.

turn7or5 stated "the table on the Left (Columns A-B) is the current data. Right table (Columns M-N) has no formulas but is a model of what the end result should look like"

Unique.xlsm
	A	B	C										M	N
1	Transaction Number	Date											Transaction Number	Date
2	1	29-Sep-22											1	29-Sep-22
3	1	29-Sep-22											2	29-Sep-22
4	2	29-Sep-22											3	29-Sep-22
5	2	29-Sep-22											4	29-Sep-22
6	3	29-Sep-22											5	29-Sep-22
7	3	29-Sep-22											6	29-Sep-22
8	4	29-Sep-22											1	21-Jun-23
9	4	29-Sep-22
10	5	29-Sep-22
11	5	29-Sep-22
12	6	29-Sep-22
13	6	29-Sep-22
14	6	29-Sep-22
15	6	29-Sep-22
16	1	21-Jun-23
17	1	21-Jun-23
18	1	21-Jun-23
19	1	21-Jun-23
20	1	21-Jun-23
21	1	21-Jun-23
9hh

Cell Formulas
Range	Formula
M2:N8	M2	=UNIQUE(Table9)
Dynamic array formulas.

Not sure, but that may not be possible for the OP since their Excel version is not one listed in the XL2BB specifications

View attachment 116223

@turn7or5
Whilst you possibly may not be able to use XL2BB as mentioned above, you could consider just copy/paste a small set of sample data into your post so that helpers would find it a bit easier to get some sample data to work with rather than having to type it out manually.

Anyway, assuming the tables already exist and, left to right, are Table1 and Table2, see if this type of formula would work for you.

turn7or5.xlsm
	A	B										L	M	N
1	Transaction Number	Date											Transaction	Date
2
3	1	29/09/2022											1	29/09/2022
4	1	29/09/2022											2	29/09/2022
5	2	29/09/2022											3	29/09/2022
6	2	29/09/2022											4	29/09/2022
7	3	29/09/2022											5	29/09/2022
8	3	29/09/2022											6	29/09/2022
9	4	29/09/2022											1	21/06/2023
10	4	29/09/2022
11	5	29/09/2022
12	5	29/09/2022
13	6	29/09/2022
14	6	29/09/2022
15	6	29/09/2022
16	6	29/09/2022
17	1	21/06/2023
18	1	21/06/2023
19	1	21/06/2023
20	1	21/06/2023
21	1	21/06/2023
22	1	21/06/2023
23
24
Sheet1

Cell Formulas
Range	Formula
M2:M22	M2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),1)&"",IFERROR(--v,""))
N2:N22	N2	=LET(v,INDEX(UNIQUE(Table1),ROW()-ROW(Table2[#Headers]),2)&"",IFERROR(--v,""))