Formula searching last status of Transactions

[Tripleseas]

Hello community,

I want to write a formula in A2 that will always search for the last statuts of ID TRANSACTIONS in colonne B and noted it.

the ID TRSANSACTIONS contains duplicate with changing statuts that updates. example :
CBS63D3FC0B40E2B this ID is available in colonne B 3 times. the last time the statut is Transfer Success. so i want the formula to note transfer success in A2.

hope i'm clear.
I will apply this formula on huge data set.

thanks to all.

Classeur1.xlsx
	A	B	C
1	Last Status	ID OF TRANSATION	Statuts
2		CBS63D3FC0B40E2B	Transfer initiated
3		CBS63D487AEB8E94	Transfer initiated
4		CBS63D63414358C6	Transfer initiated
5		CBS63D44A664F911	Transfer initiated
6		CBS63D418DC6FA08	Transfer initiated
7		CBS63D75BAEC5E35	Payment Captured
8		CBS63D75BAEC5E35	Transfer initiated
9		CBS63D77CA6A4C5A	Payment Captured
10		CBS63D77CA6A4C5A	Transfer initiated
11		CBS63D68A7CA8E4C	Payment Captured
12		CBS63D68A7CA8E4C	Transfer initiated
13		CBS63D44305209F3	Transfer initiated
14		CBS63D531364A8E4	Transfer Pending
15		CBS63D531364A8E4	Transfer Success
16		CBS63D26D8B922A3	Transfer initiated
17		CBS63D5229F5B8E7	Transfer initiated
18		CBS63D5331E91725	Transfer Pending
19		CBS63D26D8B922A3	Transfer Pending
20		CBS63D6B3207620B	Transfer Pending
21		CBS63D5331E91725	Transfer Success
22		CBS63D26D8B922A3	Transfer Success
23		CBS63D791F20F627	Payment Captured
24		CBS63D791F20F627	Transfer initiated
25		CBS63D6B3207620B	Transfer Success
26		CBS63CBAC0297954	Refund Success
27		CBS63D7978C9E7DF	Payment Captured
28		CBS63D7978C9E7DF	Transfer initiated
29		CBS63D791F20F627	Transfer Pending
30		CBS63D562DB44695	Transfer Pending
31		CBS63D798D02BCC7	Payment Captured
32		CBS63D798D02BCC7	Transfer initiated
33		CBS63D7994428C5A	Payment Captured
34		CBS63D7994428C5A	Transfer initiated
35		CBS63D791F20F627	Transfer Success
36		CBS63D562DB44695	Transfer Success
37		CBS63D77EEE8CF5F	Payment Captured
38		CBS63D550B94C0E4	Payment Captured
39		CBS63D550B94C0E4	Transfer initiated
40		CBS63D6D3E4C5473	Transfer Pending
41		CBS63D77EEE8CF5F	Transfer initiated
42		CBS63D6D3E4C5473	Transfer Success
43		CBS63D798D02BCC7	Transfer Pending
44		CBS63D487AEB8E94	Transfer Pending
45		CBS63D3FC0B40E2B	Transfer Pending
46		CBS63D798D02BCC7	Transfer Success
47		CBS63D7A736A3DC8	Payment Captured
48		CBS63D7A736A3DC8	Transfer initiated
49		CBS63D7A7A346BEA	Payment Captured
50		CBS63D7A7A346BEA	Transfer initiated
51		CBS63D487AEB8E94	Transfer Success
52		CBS63D7A840AB47D	Payment Captured
53		CBS63D3FC0B40E2B	Transfer Success
54		CBS63D7A840AB47D	Transfer initiated
55		CBS63D7A98863858	Payment Captured
56		CBS63D7AAA33405F	Payment Captured
57		CBS63D7AAA33405F	Transfer initiated
58		CBS63D56E887B177	Transfer Pending
59		CBS63D56E887B177	Transfer Success
60		CBS63D7ADD95BDAD	Payment Captured
61		CBS63D7ADD95BDAD	Transfer initiated
62		CBS63D7B3463438B	Payment Captured
63		CBS63D7B3463438B	Transfer initiated
64		CBS63D44305209F3	Transfer Pending
65		CBS63D698DCC5C07	Transfer Pending
66		CBS63D44305209F3	Transfer Success
67		CBS63D5580C19D71	Transfer Pending
68		CBS63D7B3463438B	Transfer Pending
69		CBS63D698DCC5C07	Transfer Success
70		CBS63D5580C19D71	Transfer Success
71		CBS63D7B3463438B	Transfer Success
72		CBS63D7A840AB47D	Transfer Pending
73		CBS63D7A840AB47D	Transfer Success
74		CBS63D64C264D819	Transfer initiated
75		CBS63D64CE6253D8	Payment Captured
76		CBS63D64DE4B371D	Transfer Pending
77		CBS63D64C264D819	Transfer Pending
78		CBS63D6BC72D3048	Transfer Pending
79		CBS63D64CE6253D8	Transfer initiated
80		CBS63D52D49D0F58	Refund Success
81		CBS63D64DE4B371D	Transfer Success
82		CBS63D64C264D819	Transfer Success
83		CBS63D6BC72D3048	Transfer Success
84		CBS63D7A736A3DC8	Transfer Pending
85		CBS63D7A736A3DC8	Transfer Success
86		CBS63D7ADD95BDAD	Transfer Pending
87		CBS63D5770E2FE74	Transfer Pending
88		CBS63D7ADD95BDAD	Transfer Success
89		CBS63D7D440D703D	Payment Captured
90		CBS63D7D440D703D	Transfer initiated
91		CBS63D5770E2FE74	Transfer Success
92		CBS63D68F3DA8440	Transfer Pending
93		CBS63D7DAD28D07B	Payment Captured
94		CBS63D68F3DA8440	Transfer Success
95		CBS63D7DAD28D07B	Transfer initiated
96		CBS63D7DC0CEF9DC	Payment Captured
97		CBS63D7DC0CEF9DC	Transfer initiated
98		CBS63D5229F8DA30	Transfer Pending
99		CBS63D7DBE734AD6	Payment Captured
100		CBS63D7DBE734AD6	Transfer initiated
101		CBS63D5229F8DA30	Transfer Success
102		CBS63D77EEE8CF5F	Transfer Pending
103		CBS63D77EEE8CF5F	Transfer Success
104		CBS63D7AAA33405F	Transfer Pending
105		CBS63D7E7B27E3FD	Payment Captured
106		CBS63D7E7B27E3FD	Transfer initiated
107		CBS63D7AAA33405F	Transfer Success
108		CBS63D7A7A346BEA	Transfer Pending
109		CBS63D7ED84BA314	Payment Captured
110		CBS63D7DBE734AD6	Transfer Pending
111		CBS63D7ED84BA314	Transfer initiated
112		CBS63D7A7A346BEA	Transfer Success
113		CBS63D7DBE734AD6	Transfer Success
114		CBS63D7ED84BA314	Transfer Pending
115		CBS63D7ED84BA314	Transfer Success
116		CBS63D6EA4BC218A	Transfer Pending
117		CBS63D7DAD28D07B	Transfer Pending
118		CBS63D6EA4BC218A	Transfer Success
119		CBS63D7DAD28D07B	Transfer Success
120		CBS63D64CE6253D8	Transfer Pending
121		CBS63D7D440D703D	Transfer Pending
122		CBS63D7978C9E7DF	Transfer Pending
123		CBS63D64CE6253D8	Transfer Success
124		CBS63D7D440D703D	Transfer Success
125		CBS63D7978C9E7DF	Transfer Success
126		CBS63D8007231353	Payment Captured
127		CBS63D8007231353	Transfer initiated
128		CBS63D809833FB3A	Payment Captured
129		CBS63D809833FB3A	Transfer initiated
130		CBS63D29390D2BB3	Transfer Pending
131		CBS63D29390D2BB3	Transfer Success
132		CBS63D84EB0DA3E2	Payment Captured
133		CBS63D84EB0DA3E2	Transfer initiated
134		CBS63D8534060916	Payment Captured
135		CBS63D8534060916	Transfer initiated
136		CBS63D8A4BF4832A	Payment Captured
137		CBS63D8A4BF4832A	Transfer initiated
138		CBS63CEADC03EC01	Refund Success
139		CBS63D7A98863858	Transfer initiated
140		CBS63D8DBD7CD693	Payment Captured
141		CBS63D8DBD7CD693	Transfer initiated
142		CBS63D8DC5127226	Payment Captured
143		CBS63D8DC5127226	Transfer initiated
144		CBS63D8DD23902D2	Payment Captured
145		CBS63D8DD23902D2	Transfer initiated
146		CBS63D8E0448FE9C	Payment Captured
147		CBS63D8E0448FE9C	Transfer initiated
148		CBS63D8E18398E08	Payment Captured
149		CBS63D8E18398E08	Transfer initiated
150		CBS63D8E14C60DA5	Payment Captured
151		CBS63D8E14C60DA5	Transfer initiated
152		CBS63D8DD23902D2	Transfer Pending
153		CBS63D8DC5127226	Transfer Pending
154		CBS63D8DD23902D2	Transfer Success
155		CBS63D8DC5127226	Transfer Success
156		CBS63D8E18398E08	Transfer Pending
157		CBS63D8E18398E08	Transfer Success
158		CBS63D7A98863858	Transfer Pending
159		CBS63D7A98863858	Transfer Success
160		CBS63D8F23817138	Payment Captured
161		CBS63D8F23817138	Transfer initiated
162		CBS63D8F8C58EE1B	Payment Captured
163		CBS63D8F8C58EE1B	Transfer initiated
164		CBS63D8FD1A522D4	Payment Captured
165		CBS63D8FD1A522D4	Transfer initiated
166		CBS63D8FE220CFC0	Payment Captured
167		CBS63D8FE220CFC0	Transfer initiated
168		CBS63D9018CACCB2	Payment Captured
169		CBS63D9018CACCB2	Transfer initiated
170		CBS63D9018CACCB2	Transfer Pending
171		CBS63D2377280DCA	Transfer Success
172		CBS63D8DBD7CD693	Transfer Pending
173		CBS63D9038D745DB	Payment Captured
174		CBS63D9038D745DB	Transfer initiated
175		CBS63D8DBD7CD693	Transfer Success
176		CBS63D63414358C6	Refund Success
177		CBS63D9064DBFF70	Payment Captured
178		CBS63D9064DBFF70	Transfer initiated
179		CBS63D909ED82892	Payment Captured
180		CBS63D8FD1A522D4	Transfer Pending
181		CBS63D909CF23834	Payment Captured
182		CBS63D909CF23834	Transfer initiated
183		CBS63D8FD1A522D4	Transfer Success
184		CBS63D909ED82892	Transfer initiated
185		CBS63D91424B6ACF	Payment Captured
186		CBS63D9146E83D24	Payment Captured
187		CBS63D9146E83D24	Transfer initiated
188		CBS63D91424B6ACF	Transfer initiated
189		CBS63D916D974662	Payment Captured
190		CBS63D7994428C5A	Transfer Pending
191		CBS63D8F8C58EE1B	Transfer Pending
192		CBS63D809833FB3A	Transfer Pending
193		CBS63D8A4BF4832A	Transfer Pending
194		CBS63D7994428C5A	Transfer Success
195		CBS63D8F23817138	Transfer Pending
196		CBS63D8FE220CFC0	Transfer Pending
197		CBS63D8534060916	Transfer Pending
198		CBS63D8F8C58EE1B	Transfer Success
199		CBS63D809833FB3A	Transfer Success
200		CBS63D9146E83D24	Transfer Pending
201		CBS63D91B6FADF96	Payment Captured
202		CBS63D91B6FADF96	Transfer initiated
203		CBS63D75BAEC5E35	Transfer Pending
204		CBS63D91BB5392D9	Payment Captured
205		CBS63D91BB5392D9	Transfer initiated
206		CBS63D8A4BF4832A	Transfer Success
207		CBS63D91C030E181	Payment Captured
208		CBS63D8F23817138	Transfer Success
209		CBS63D91C030E181	Transfer initiated
210		CBS63D8FE220CFC0	Transfer Success
211		CBS63D91C38907C3	Payment Captured
212		CBS63D91C38907C3	Transfer initiated
213		CBS63D8534060916	Transfer Success
214		CBS63D9146E83D24	Transfer Success
215		CBS63D75BAEC5E35	Transfer Success
216		CBS63D8BFE6B3C37	Payment Captured
217		CBS63D8BFE6B3C37	Transfer initiated
218		CBS63D91B6FADF96	Transfer Pending
219		CBS63D8E0448FE9C	Transfer Pending
220		CBS63D91B6FADF96	Transfer Success
221		CBS63D8E0448FE9C	Transfer Success
222		CBS63D916D974662	Transfer initiated
223		CBS63D932D926CEE	Payment Captured
224		CBS63D932D926CEE	Transfer initiated
225		CBS63D91424B6ACF	Transfer Pending
226		CBS63D93539D9220	Payment Captured
227		CBS63D91424B6ACF	Transfer Success
228		CBS63D937141641D	Payment Captured
229		CBS63D937141641D	Transfer initiated
230		CBS63D91C030E181	Transfer Pending
231		CBS63D91C38907C3	Transfer Pending
232		CBS63D84EB0DA3E2	Transfer Pending
233		CBS63D91C030E181	Transfer Success
234		CBS63D91C38907C3	Transfer Success
235		CBS63D84EB0DA3E2	Transfer Success
236		CBS63D91BB5392D9	Transfer Pending
237		CBS63D93FAE1CDCC	Payment Captured
238		CBS63D93FAE1CDCC	Transfer initiated
239		CBS63D91BB5392D9	Transfer Success
240		CBS63D909ED82892	Transfer Pending
241		CBS63D9456F420BD	Payment Captured
242		CBS63D9456F420BD	Transfer initiated
243		CBS63D909ED82892	Transfer Success
244		CBS63D94D2E08447	Payment Captured
245		CBS63D94D2E08447	Transfer initiated
246		CBS63D9064DBFF70	Transfer Pending
247		CBS63D9038D745DB	Transfer Pending
248		CBS63D93FAE1CDCC	Transfer Pending
249		CBS63D932D926CEE	Transfer Pending
250		CBS63D9064DBFF70	Transfer Success
251		CBS63D9038D745DB	Transfer Success
252		CBS63D93FAE1CDCC	Transfer Success
253		CBS63D932D926CEE	Transfer Success
254		CBS63D937141641D	Transfer Pending
255		CBS63D937141641D	Transfer Success
256		CBS63D916D974662	Transfer Pending
257		CBS63D916D974662	Transfer Success
258		CBS63D964BC270D3	Payment Captured
259		CBS63D964BC270D3	Transfer initiated
260		CBS63D965649A457	Payment Captured
261		CBS63D965649A457	Transfer initiated
262		CBS63D973A5B41A6	Payment Captured
263		CBS63D9A3705801A	Payment Captured
264		CBS63D9A3705801A	Transfer initiated
265		CBS63D973A5B41A6	Transfer initiated
266		CBS63DA1C99DD9B1	Payment Captured
267		CBS63DA1CB2D23A1	Payment Captured
268		CBS63DA1C99DD9B1	Transfer initiated
269		CBS63DA1CB2D23A1	Transfer initiated
270		CBS63DA256BB46DE	Payment Captured
271		CBS63DA256BB46DE	Transfer initiated
272		CBS63DA26223ECD8	Payment Captured
273		CBS63DA26223ECD8	Transfer initiated
274		CBS63DA2CB22212E	Payment Captured
275		CBS63DA2CB22212E	Transfer initiated
276		CBS63D93539D9220	Refund Success
277		CBS63DA2F4C63769	Payment Captured
278		CBS63DA2F74DD596	Payment Captured
279		CBS63DA2F4C63769	Transfer initiated
280		CBS63DA2F74DD596	Transfer initiated
281		CBS63D965649A457	Transfer Pending
282		CBS63D965649A457	Transfer Success
283		CBS63DA368FD1FE8	Payment Captured
284		CBS63DA368FD1FE8	Transfer initiated
285		CBS63DA39199A585	Payment Captured
286		CBS63DA39199A585	Transfer initiated
287		CBS63DA39E417780	Payment Captured
288		CBS63DA39E417780	Transfer initiated
289		CBS63DA2CB22212E	Transfer Pending
290		CBS63DA2CB22212E	Transfer Success
291		CBS63DA2F74DD596	Transfer Pending
292		CBS63D7BC5330A42	Payment Captured
293		CBS63D7BC5330A42	Transfer initiated
294		CBS63D94D2E08447	Transfer Pending
295		CBS63DA2F74DD596	Transfer Success
296		CBS63D94D2E08447	Transfer Success
Feuil3

 

[Fluff]

How about

=INDEX(C:C,AGGREGATE(14,6,ROW($C$2:$C$500)/($B$2:$B$500=B2),1))

 

[Tripleseas]

How about

=INDEX(C:C,AGGREGATE(14,6,ROW($C$2:$C$500)/($B$2:$B$500=B2),1))

Thank for replying i got an error #NOM? when applying

 

[Fluff]

Did you change it to suit your language version?

 

[Tripleseas]

Did you change it to suit your language ver

Did you change it to suit your language version?

thanks it finally worked like charm thanks alot.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Tripleseas]

You're welcome & thanks for the feedback.

hello Mr.Fluff
I'm coming back to you for a change on the formula. I forgot to mention a very important detail. and i don't know if i need to start a new thread.

the current formula is working good but is there a possibility to have the formula visible only on the last id transaction because now it indicate the result for every status of id transations.

i tried to explain with this example . thank you so much for your help.

example :

Last Status	ID OF TRANSATION	Statuts
Blank	CBS63D3FC0B40E2B	Transfer initiated
Blank	CBS63D3FC0B40E2B	Transfer Pending
formula with result here	CBS63D3FC0B40E2B	Transfer Success

 

[shinigamilight]

Try this

=IF(COUNTIFS($B$2:$B$296,B2)=COUNTIFS($B$2:B2,B2),LOOKUP(2,1/($B$2:$B$296=B2),$C$2:$C$296), "")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Last Status	ID OF TRANSATION	Statuts
2		CBS63D3FC0B40E2B	Transfer initiated
3		CBS63D487AEB8E94	Transfer initiated
4		CBS63D63414358C6	Transfer initiated
5	Transfer initiated	CBS63D44A664F911	Transfer initiated
6	Transfer initiated	CBS63D418DC6FA08	Transfer initiated
7		CBS63D75BAEC5E35	Payment Captured
8		CBS63D75BAEC5E35	Transfer initiated
9		CBS63D77CA6A4C5A	Payment Captured
10	Transfer initiated	CBS63D77CA6A4C5A	Transfer initiated
11		CBS63D68A7CA8E4C	Payment Captured
12	Transfer initiated	CBS63D68A7CA8E4C	Transfer initiated
13		CBS63D44305209F3	Transfer initiated
14		CBS63D531364A8E4	Transfer Pending
15	Transfer Success	CBS63D531364A8E4	Transfer Success
16		CBS63D26D8B922A3	Transfer initiated
17	Transfer initiated	CBS63D5229F5B8E7	Transfer initiated
18		CBS63D5331E91725	Transfer Pending
19		CBS63D26D8B922A3	Transfer Pending
20		CBS63D6B3207620B	Transfer Pending
21	Transfer Success	CBS63D5331E91725	Transfer Success
22	Transfer Success	CBS63D26D8B922A3	Transfer Success
23		CBS63D791F20F627	Payment Captured
24		CBS63D791F20F627	Transfer initiated
25	Transfer Success	CBS63D6B3207620B	Transfer Success
26	Refund Success	CBS63CBAC0297954	Refund Success
27		CBS63D7978C9E7DF	Payment Captured
28		CBS63D7978C9E7DF	Transfer initiated
29		CBS63D791F20F627	Transfer Pending
30		CBS63D562DB44695	Transfer Pending
31		CBS63D798D02BCC7	Payment Captured
32		CBS63D798D02BCC7	Transfer initiated
33		CBS63D7994428C5A	Payment Captured
34		CBS63D7994428C5A	Transfer initiated
35	Transfer Success	CBS63D791F20F627	Transfer Success
36	Transfer Success	CBS63D562DB44695	Transfer Success
37		CBS63D77EEE8CF5F	Payment Captured
38		CBS63D550B94C0E4	Payment Captured
39	Transfer initiated	CBS63D550B94C0E4	Transfer initiated
40		CBS63D6D3E4C5473	Transfer Pending
41		CBS63D77EEE8CF5F	Transfer initiated
42	Transfer Success	CBS63D6D3E4C5473	Transfer Success
43		CBS63D798D02BCC7	Transfer Pending
44		CBS63D487AEB8E94	Transfer Pending
45		CBS63D3FC0B40E2B	Transfer Pending
46	Transfer Success	CBS63D798D02BCC7	Transfer Success
Main

Cell Formulas
Range	Formula
A2:A46	A2	=IF(COUNTIFS(B$2:B2,B2)=COUNTIFS(B:B,B2),INDEX(C:C,AGGREGATE(14,6,ROW($C$2:$C$500)/($B$2:$B$500=B2),1)),"")

 

[Tripleseas]

How about

Fluff.xlsm
	A	B	C
1	Last Status	ID OF TRANSATION	Statuts
2		CBS63D3FC0B40E2B	Transfer initiated
3		CBS63D487AEB8E94	Transfer initiated
4		CBS63D63414358C6	Transfer initiated
5	Transfer initiated	CBS63D44A664F911	Transfer initiated
6	Transfer initiated	CBS63D418DC6FA08	Transfer initiated
7		CBS63D75BAEC5E35	Payment Captured
8		CBS63D75BAEC5E35	Transfer initiated
9		CBS63D77CA6A4C5A	Payment Captured
10	Transfer initiated	CBS63D77CA6A4C5A	Transfer initiated
11		CBS63D68A7CA8E4C	Payment Captured
12	Transfer initiated	CBS63D68A7CA8E4C	Transfer initiated
13		CBS63D44305209F3	Transfer initiated
14		CBS63D531364A8E4	Transfer Pending
15	Transfer Success	CBS63D531364A8E4	Transfer Success
16		CBS63D26D8B922A3	Transfer initiated
17	Transfer initiated	CBS63D5229F5B8E7	Transfer initiated
18		CBS63D5331E91725	Transfer Pending
19		CBS63D26D8B922A3	Transfer Pending
20		CBS63D6B3207620B	Transfer Pending
21	Transfer Success	CBS63D5331E91725	Transfer Success
22	Transfer Success	CBS63D26D8B922A3	Transfer Success
23		CBS63D791F20F627	Payment Captured
24		CBS63D791F20F627	Transfer initiated
25	Transfer Success	CBS63D6B3207620B	Transfer Success
26	Refund Success	CBS63CBAC0297954	Refund Success
27		CBS63D7978C9E7DF	Payment Captured
28		CBS63D7978C9E7DF	Transfer initiated
29		CBS63D791F20F627	Transfer Pending
30		CBS63D562DB44695	Transfer Pending
31		CBS63D798D02BCC7	Payment Captured
32		CBS63D798D02BCC7	Transfer initiated
33		CBS63D7994428C5A	Payment Captured
34		CBS63D7994428C5A	Transfer initiated
35	Transfer Success	CBS63D791F20F627	Transfer Success
36	Transfer Success	CBS63D562DB44695	Transfer Success
37		CBS63D77EEE8CF5F	Payment Captured
38		CBS63D550B94C0E4	Payment Captured
39	Transfer initiated	CBS63D550B94C0E4	Transfer initiated
40		CBS63D6D3E4C5473	Transfer Pending
41		CBS63D77EEE8CF5F	Transfer initiated
42	Transfer Success	CBS63D6D3E4C5473	Transfer Success
43		CBS63D798D02BCC7	Transfer Pending
44		CBS63D487AEB8E94	Transfer Pending
45		CBS63D3FC0B40E2B	Transfer Pending
46	Transfer Success	CBS63D798D02BCC7	Transfer Success
Main

Cell Formulas
Range	Formula
A2:A46	A2	=IF(COUNTIFS(B$2:B2,B2)=COUNTIFS(B:B,B2),INDEX(C:C,AGGREGATE(14,6,ROW($C$2:$C$500)/($B$2:$B$500=B2),1)),"")

Thank you mr fluff that's amazing. worked very good ! thank you so much.