Extracting most recent values greater than 0

[Smurphster16]

Hi guys,

I have a worksheet that has IDs in column A , values in column B and dates in column C.

In some cases the the value for a given ID on a given date is 0.

Therefore on a separate sheet I want a unique list of IDS and the value for each ID with the latest date that is greater than 0.

Initially I had this formula - but it only works if all the values that are greater than 0 for a particular ID are for consecutive dates - is there a way to get this so that it doesn't matter if the values greater than 0 are not for consecutive dates?
(unique ID list starts in separate sheet at A2 and sheet below is the AM upload)

=IFNA(INDEX('AM Upload'!C:C,MATCH(A2,'AM Upload'!A:A,0)+COUNTIFS('AM Upload'!A:A,A2,'AUM Upload'!C:C,">"&0)-1),"")

b	369,154,383	30/06/2019​
b	378,582,711	31/07/2019​
b	360,129,923	31/08/2019​
b	363,685,213	30/09/2019​
b	364,375,372	31/10/2019​
b	379,777,014	30/11/2019​
b	380,403,091	31/12/2019​
b	-	31/01/2020​
d	334,440,169	30/06/2019​
d	329,340,127	31/07/2019​
d	333,324,290	31/08/2019​
d	320,730,918	30/09/2019​
d	322,417	31/10/2019​
d	324,926,841	30/11/2019​
d	294,756,141	31/12/2019​
d	290,706	31/01/2020​
c	22,000,000	30/06/2019​
c	-	31/07/2019​
c	-	31/08/2019​
c	39,186,153,000	30/09/2019​
c	-	31/10/2019​
c	-	30/11/2019​
c	37,289,080,000	31/12/2019​
c	-	31/01/2020​
g	1,796,151,592	30/06/2019​
g	1,800,487	31/07/2019​
g	1,024,939	31/08/2019​
g	1,872,347	30/09/2019​

Thanks,

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1	b	369,154,383	30/06/2019		b	380,403,091
2	b	378,582,711	31/07/2019		c	37,289,080,000
3	b	360,129,923	31/08/2019		d	290,706
4	b	363,685,213	30/09/2019		g	1,872,347
5	b	364,375,372	31/10/2019
6	b	379,777,014	30/11/2019
7	b	380,403,091	31/12/2019
8	b	-	31/01/2020
9	d	334,440,169	30/06/2019
10	d	329,340,127	31/07/2019
11	d	333,324,290	31/08/2019
12	d	320,730,918	30/09/2019
13	d	322,417	31/10/2019
14	d	324,926,841	30/11/2019
15	d	294,756,141	31/12/2019
16	d	290,706	31/01/2020
17	c	22,000,000	30/06/2019
18	c	-	31/07/2019
19	c	-	31/08/2019
20	c	39,186,153,000	30/09/2019
21	c	-	31/10/2019
22	c	-	30/11/2019
23	c	37,289,080,000	31/12/2019
24	c	-	31/01/2020
25	g	1,796,151,592	30/06/2019
26	g	1,800,487	31/07/2019
27	g	1,024,939	31/08/2019
28	g	1,872,347	30/09/2019
Sheet1

Cell Formulas
Range	Formula
F1:F4	F1	=INDEX($B$1:$B$28,MATCH(E1&MAXIFS($C$1:$C$28,$A$1:$A$28,E1,$B$1:$B$28,">"&0),$A$1:$A$28&$C$1:$C$28,0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Smurphster16]

Hi ,

thanks very much for the solution.

Unfortunately i am running excel 2016 - so i don't think the max-ifs function is available to me?

Is there an alternate solution?

Thanks very much

 

[sandy666]

another way with Power Query (Get&Tramsform)

ID	MaxDate	Value
b	31/12/2019	380403091
c	31/12/2019	37289080000
d	31/01/2020	290706
g	30/09/2019	1872347

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Table.SelectRows(Source, each [Value] <> "-"),{{"Value", Int64.Type}, {"Date", type date}}),
    Group = Table.Group(Type, {"ID"}, {{"MaxDate", each List.Max([Date]), type date}, {"Count", each _, type table}}),
    Value = Table.AddColumn(Table.Sort(Group,{{"ID", Order.Ascending}}), "Value", each List.Last([Count][Value]))
in
    Value

btw. update your profile (Account details) about Excel version. Less troubles and wasted posts.

 

[Marcelo Branco]

Try

Pasta1
	A	B
1	ID	Last Value
2	b	380403091
3	c	37289080000
4	d	290706
5	g	1872347
Plan2

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(LOOKUP(2,1/(('AM Upload'!A$1:A$1000=A2)*ISNUMBER('AM Upload'!B$1:B$1000)),'AM Upload'!B$1:B$1000),"")

M.

 

[AlanY]

Hi ,

thanks very much for the solution.

Unfortunately i am running excel 2016 - so i don't think the max-ifs function is available to me?

Is there an alternate solution?

Thanks very much

depends on the format of your cells, one of the 2 array formula should work

=INDEX($B$1:$B$28,MATCH(E1&MAX(IF($B$1:$B$28>0,IF($A$1:$A$28=E1,$C$1:$C$28))),$A$1:$A$28&$C$1:$C$28,0))

or

=INDEX($B$1:$B$28,MATCH(E1&MAX(IF(ISNUMBER($B$1:$B$28),IF($A$1:$A$28=E1,$C$1:$C$28))),$A$1:$A$28&$C$1:$C$28,0))