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.

 

[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,""))

Thank you for your response.

I apologize for the delayed response. This is the earliest I could.

Yes, this version of Excel is absent the compatibility list for XL2bb. Thank you for saying so. I understand it is helpful to post the spreadsheet itself and will look into it.

I tried your formula with the real table names in my sheet. But, Excel returned a pop up error which said it is not a formula.
i then tried replacing the tables with cell ranges and a SPILL error was returned.
Is there any other change i need to make? There are more rows and columns in the actual tables versus what I posted here. Could that be a problem? This is a complicated formula to me.

Thank you.

 

[Dave Patton]

Did you try the suggestion in post #8?

 

[turn7or5]

Did you try the suggestion in post #8?

It took some maneuvering but i did get it to work and with a table.
Thank you

 

[Dave Patton]

Thanks for the feedback. Dave