Smurphster16
New Member
- Joined
- Feb 28, 2019
- Messages
- 25
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),"")
Thanks,
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 |