Indexing Problem (of sorts)

[denzo36]

Hi folks,

I have a list of policy numbers, the policy version associated with it and whether that policy version had a claim or not. A policy can have multiple policy versions which go in sequential order. You can see from my data below, each row has policy number, policy version and whether that version has had a claim.

What I'm trying to do is for each policy, only keep the policy versions that either have the claim on them and the policy versions after that version. I want to delete the versions of each policy before the version where the claim happened and only keep the policy version on and after the version where the claim occurred.

You can see from the screen shot below, my desired result (the 1 and 0 are arbitrary in terms of labelling) - I would like something like that so I can filter this column by the 0 and delete them. So for each policy I'd like the index to only count 1 on the version that had the claim and any version after that but a 0 for the versions before the claim.

Hopefully that's making sense.

Any ideas?

 

[Fluff]

How about

=COUNTIFS(A$2:A2,A2,B$2:B2,"Y")>0

this will return True/False rather than 1/0

 

[denzo36]

How about

=COUNTIFS(A$2:A2,A2,B$2:B2,"Y")>0

this will return True/False rather than 1/0

Fluff thank you so much, that has worked a treat.

Much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Kasun_slv]

Hi Fluff ,
Could you please help me to resolve this excel VBA problem? The following VBA excel sheet is used in windows XP , its working fine on XP but when I open the same sheet on windows 10 , Run-time error 429 active component can't created object error pops-up. When debug it shows " Set Objfso = CreateObject("UserAccounts.CommonDialog")"
"
Set Objfso = CreateObject("UserAccounts.CommonDialog") <<<--------- This is the place error pops-up

'ObjFSO.Filter = "|Text Documents|*.txt"

Objfso.FilterIndex = 3

Objfso.InitialDir = "c:\myscripts"

InitFSO = Objfso.ShowOpen

If InitFSO = False Then
msgbox "Error: Please select a file!"
End
Else
msgbox "You selected the file: " & Objfso.Filename
sfname = Objfso.Filename
sFileName = Split(sfname, "\")(UBound(Split(sfname, "\")))

End If

Workbooks.OpenText Filename:= _
Objfso.Filename, Origin:=437, StartRow:= _
1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True


I'm not a professional IT Person. Could you please help me.
Many thanks
Kasun

 

[Fluff]

As this has nothing to do with the original question, you need to start a thread of your own.
Also please do not include you email address (or any other contact info) in you posts.
Thanks