Speed up calculation with alternative formula(s)

[ahmetgns]

I have to speed up calculation in a table, I'll explain it below. If possible the fastest solution "but without VBA" is preferred.

The below is a small sample of the table. Actual table now have 15,000 rows and is growing. The aim is to have the last column reflect the latest name on all the rows, corresponding to the same ID. I mean, if an added row has the same ID value of any row above, the last column for all the rows which have that ID, will change to the Name of the latest row, so all will be the same. Pay attention to the ID on the last row, it is same as the first and the second rows. Although name for each of them is different, the last column always shows "KKK", since the one on the last row is "KKK".

I tried XLOOKUP but it is very slow for the reverse order (You know, I have to run it in reverse order since the table grows to the below). That code is also below (2nd one). Actually it is sufficient to search from the curent row to the end of the table, so I also tried the 3rd example below but it is still slow. Any fast solutions are welcome. If I use absolute reference (4st code) it becomes a problem, as the "calculated column" of the table when the table is growing to the below.

bb2c.xlsx
	F	G	H
3	Name	ID	Name_Unique
4	AAA	481	KKK
5	BBB	481	KKK
6	VYZ	717	VYZ
7	KKK	481	KKK
Liste

Cell Formulas
Range	Formula
H4:H7	H4	=LOOKUP(2,1/(G4:INDIRECT("g"&ROWS(Tablo1)+ROW(Tablo1)-1)=G4),F4:INDIRECT("F"&ROWS(Tablo1)+ROW(Tablo1)-1))

bb2c.xlsx
	F	G	H
3	Name	ID	Name_Unique
4	AAA	481	KKK
5	BBB	481	KKK
6	VYZ	717	VYZ
7	KKK	481	KKK
Liste

Cell Formulas
Range	Formula
H4:H7	H4	=XLOOKUP([@ID],[ID],[Name],,-1)

bb2c.xlsx
	F	G	H
3	Name	ID	Name_Unique
4	AAA	481	KKK
5	BBB	481	KKK
6	VYZ	717	VYZ
7	KKK	481	KKK
Liste

Cell Formulas
Range	Formula
H4:H7	H4	=XLOOKUP([@ID],[@ID]:INDIRECT("G"&ROWS(Tablo1)+ROW(Tablo1)-1),[@Name]:INDIRECT("F"&ROWS(Tablo1)+ROW(Tablo1)-1),,-1)

bb2c.xlsx
	F	G	H
3	Name	ID	Name_Unique
4	AAA	481	KKK
5	BBB	481	KKK
6	VYZ	717	VYZ
7	KKK	481	KKK
Liste

Cell Formulas
Range	Formula
H4:H7	H4	=XLOOKUP([@ID],[@ID]:G$7,[@Name]:F$7,,-1)

 

[Fluff]

How about

=INDEX(FILTER([Name],[ID]=[@ID]),COUNTIF([ID],[@ID]))

 

[offthelip]

If you don't have the filter function ( which I don't) try this in C3:

=IFERROR(INDEX(OFFSET(A4,0,0,COUNTA(B:B)-ROW()),(MATCH(B3,OFFSET(B4,0,0,COUNTA(B:B)-ROW()),0))),A3)

 

[Fluff]

As your data is in a table, for your fourth formula you can just use

=XLOOKUP([@ID],[ID],[Name],,0,-1)

 

[ahmetgns]

Thanks for quick replies. I am replying you in descending order:

As your data is in a table, for your fourth formula you can just use

=XLOOKUP([@ID],[ID],[Name],,0,-1)

My second sample was like that but it was very slow. However since I have Excel 2019 and 2019 doesn't include XLOOKUP function, I am using the add-in that makes those 2 functions possible to use, I got it from github website. By the way, I've noticed that the syntax it uses is different, it omits the [if_not_found] item in the fourth place. That is why the [search_mode] item in my sample formula is in the fifth place, not in the sixth. Also since [match_mode] defaults to 0 already, I omitted it in my formula. Thanks again.

=IFERROR(INDEX(OFFSET(A4,0,0,COUNTA(B:B)-ROW()),(MATCH(B3,OFFSET(B4,0,0,COUNTA(B:B)-ROW()),0))),A3)

Thanks. As you guessed, I don't have FILTER function on my desktop Excel, but I think the formula is wrong. Its result is effective only on 1 adjacent cell with a different name in Name column, the below or upper one correspondingly.

How about

=INDEX(FILTER([Name],[ID]=[@ID]),COUNTIF([ID],[@ID]))

Thanks. I said above, I don't have FILTER function on my desktop Excel, however I uploaded my file on Online Excel, run the code, it took ~20 seconds to calculate. Then I tried the XLOOKUP function online, it took only 1 or 2 seconds, independently whether the [match_mode] is 1 or -1. So the add-in I am using may be problematic, especially the [match_mode] selection has an effect on the calculation period. If only I had 365 subscription to try it on my desktop...

Any new suggestion is welcome.

 

[Fluff]

However since I have Excel 2019

In that case I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=LOOKUP(2,1/([ID]=[@ID]),[Name_Unique])

 

[ahmetgns]

In that case I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=LOOKUP(2,1/([ID]=[@ID]),[Name_Unique])

Thanks. It is fast really, about 6 seconds. How could not I think about that in the first place, I don't know.

 

[Fluff]

Glad we could help & thanks for the feedback.