VBA to help change the value from 1 to 0 based on a value of another cell

[UmairKamal]

Dear Users,

I have the following dataset. If the values in column A are duplicate, and if the value in Column D has the wording "Review" in it, then the number in column E for the first result are converted to zero.

Reference	Decision Date	Outcome	Outcome Reason	Number
2220235664	26/04/2022	Refused		1
2220235664	26/05/2022	Allowed	Review	1

The idea is that the data will be added on a daily basis and overtime it will be difficult to change the results of the "Number" column based on the duplicates. I was hoping to have a VBA code that I can run on a weekly or monthly basis to update the Number column.

Please assist in this matter.

 

[mumps]

Can there be more than 2 occurrences of the Reference number in column A? Are the duplicate Reference numbers always grouped together, one underneath the other?

 

[UmairKamal]

Dear Mumps,

Thank you for your response.

There can be more than one occurrence of the reference number in Column A. so whether it is occurring twice or more than twice, it should always convert all the other 1's to 0s except the latest one (which you can see is based on the second column "Decision Date")

The reference numbers will not always be grouped together, in fact, there is 99.99% chance that they will be randomly placed in the worksheet.

 

[mumps]

Can you post data that is more representative of your actual data? It would be best if if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[UmairKamal]

I have used the XL2BB.

As mentioned in my earlier post, I will be pasting the data into the sheet on a daily basis. sometimes, the Ref. No. in column B will have more than one occurrence. let us say, if the Ref No. has two occurrences, one in April-22, second in May-22, and the second occurrence has "Review" in "Outcome Reason" Columns, then when the code is run, it should exclude the previous occurrence(s) by converting its corresponding 1 in the Number Column to 0.

I hope this helps in explaining the requirement.

Dummy Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Notification Date	Ref No.	Appeal reference	Hearing Type	Hearing date	Decision Date	Outcome	Outcome Reason	Allowed Reason	Direction	Case Type	Static/Moving	Code	Location	Pack completed by	Date Evidence submitted	Nor Created By	Date NoR Created	Additional information	Ref Type	Number	Date	Month	Year
2	04/04/2022	UK05689651	2221127412	Postal	25/03/2022	02/04/2022	Appeal Refused					Moving	33	Sachin Tendulkar Road	LW	16/03/2022	GBU	05/01/2022		Up	1	02	April	2022
3	04/04/2022	UK05605196	2221141515	Postal	02/04/2022	02/04/2022	Appeal Refused					Moving	33	Shane Ware Road	TG	28/03/2022	KMO	06/01/2022		Down	1	02	April	2022
4	04/04/2022	UK07011046	2221171944	Postal	02/04/2022	02/04/2022	Appeal Refused					Moving	33	Curtly Ambrose Road	AW	23/02/2022	KMO	06/01/2022		Left	1	02	April	2022
5	04/04/2022	UK07303234	2221153151	Postal	02/04/2022	02/04/2022	Appeal Refused					Static	12	Mark Waugh Road	TG	28/03/2022	JBU	24/01/2022		Left	1	02	April	2022
6	07/07/2022	UK07086555	2221181185	Postal	03/04/2022	07/07/2022	Appeal Allowed					Static	40	Steve Waugh Road	MU	07/03/2022				Up	1	07	July	2022
7	07/04/2022	UK03524140	2221115239	Postal	06/04/2022	06/04/2022	Appeal Allowed					Moving	33	Brian Charles Lara Road						Down	1	06	April	2022
8	07/04/2022	UK06942613	2221241111	Postal	06/04/2022	06/04/2022	Appeal Allowed					Static	1	Wasim Akram Road			VC	04/03/2022		Down	1	06	April	2022
9	07/04/2022	UK07295384	2221241937	Postal	06/04/2022	06/04/2022	Appeal Allowed					Static	19	Imran Khan Road			VC	04/03/2022		Down	1	06	April	2022
10	08/04/2022	UK05486789	2221164238	personal	07/04/2022	07/04/2022	Appeal Allowed					Moving	33	Waqar Younis Road	TG	09/03/2022	TH	15/02/2022		Down	1	07	April	2022
11	08/04/2022	UK05844084	2221252516	personal	07/04/2022	07/04/2022	Appeal Refused					Moving	33	Javed Miandad Road			PH	18/03/2022		Down	1	07	April	2022
12	08/04/2022	UK07213175	2221241812	Postal	07/04/2022	07/04/2022	Appeal Refused					Static	40	Glen Mcgrath Road			VC	04/03/2022		Left	1	07	April	2022
13	08/04/2022	UK05891572	2221214517	Postal	07/04/2022	07/04/2022	Appeal Refused					Moving	32d	Virat Kohli Road			GBU	04/03/2022		Right	1	07	April	2022
14	08/04/2022	UK05861695	2221151972	Postal	07/04/2022	07/04/2022	Appeal Refused					Moving	52	Jaques Kallis Road	TG	04/03/2022	JBE	28/01/2022		Up	1	07	April	2022
15	08/04/2022	UK05844084	2221252516	Postal	07/04/2022	07/04/2022	Appeal Allowed	Review				Moving	33	Javed Miandad Road	MU	04/03/2022				Up	1	07	April	2022
16	08/04/2022	UK07011046	2221171944	personal	08/04/2022	07/04/2022	Appeal Allowed	Review				Static	01	Curtly Ambrose Road	MU	03/03/2022				Up	1	07	April	2022
17	08/04/2022	UK07292615	2221186489	personal	08/04/2022	08/04/2022	Appeal Refused					Static	12	Sir Garfield Sobers Road	AW	14/03/2022	AJ	18/02/2022		Up	1	08	April	2022
18	11/04/2022	UK05851895	222118754A	Postal	08/04/2022	09/04/2022	Appeal Refused					Moving	33	Harsha Bhogle Road	AW	11/03/2022	KMO	10/02/2022		Down	1	09	April	2022
19	11/04/2022	UK04438204	2221162935	Postal	04/04/2022	09/04/2022	Appeal Allowed					Moving	52	Richie Benaud Road	TG	30/03/2022	JN	18/02/2022		Down	1	09	April	2022
20	11/04/2022	UK05872534	2221182818	Postal	09/04/2022	09/04/2022	Appeal Allowed					Moving	52	Alistair Cook Road	TG	01/04/2022	KMO	15/02/2022		Down	1	09	April	2022
21	11/04/2022	UK04430803	2221162979	Postal	04/04/2022	09/04/2022	Appeal Allowed					Moving	52M	Graeme Swann Road	TG	30/03/2022	JN	18/02/2022		Down	1	09	April	2022
22	12/04/2022	UK07192789	2221156433	Postal	29/03/2022	11/04/2022	Appeal Allowed					Static	12	James Anderson Road	LW	21/03/2022	VC	25/02/2022		Down	1	11	April	2022
23	12/04/2022	UK05919423	2221139717	Postal	11/04/2022	11/04/2022	Appeal Refused					Moving	52G	Stuart Broad Road	AW	25/02/2022	JBU	16/02/2022		Down	1	11	April	2022
24	27/04/2022	UK06000088	2221235664	Postal	26/04/2022	26/04/2022	Appeal Refused		LST			Moving	50	Babar Azam Road	LW	22/04/2022	JBE	21/03/2022		Left	1	26	April	2022
25	12/05/2022	JK06006073	222123316A	Postal	26/04/2022	11/05/2022	Appeal refused							Andy Roberts Road						Right	1	11	May	2022
26	12/05/2022	JK06027594	2221251638	personal	11/05/2022	11/05/2022	Appeal allowed							Rahul Dravid Road						Right	1	11	May	2022
27	12/05/2022	JK06022411	2221251513	personal	11/05/2022	11/05/2022	Appeal allowed							Allan Donald Road						Down	1	11	May	2022
28	12/05/2022	JK06010423	2221251615	personal	11/05/2022	11/05/2022	Appeal allowed							Shoaib Akhtar Road						Up	1	11	May	2022
29	12/05/2022	JK05946040	2221214966	personal	11/05/2022	11/05/2022	Appeal refused							Brett Lee Road						Up	1	11	May	2022
30	01/05/2022	UK05844084	2221252516	personal	11/05/2022	23/05/2022	Appeal Allowed	Review				Moving	33	Javed Miandad Road	MU	04/03/2022				Up	1	23	May	2022
31	27/04/2022	UK06000088	2221235664	personal	26/04/2022	26/05/2022	Appeal Allowed	Review				Moving	50	Babar Azam Road	LW	22/04/2022	JBE	21/03/2022		Down	1	26	May	2022
Data

Cell Formulas
Range	Formula
V2:V31	V2	=TEXT(F2, "DD")
W2:W31	W2	=IF(F2="",0,TEXT(F2, "MMMM"))
X2:X31	X2	=IF(F2="",0,TEXT(F2, "YYYY"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	duplicates	text	NO

 

[mumps]

Try:

Sub ChangeValue()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, fVisRow As Long, lVisRow As Long, rng As Range
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("B2:B" & lRow).Value
    With CreateObject("scripting.dictionary")
        For i = LBound(v) To UBound(v)
            If Not .exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                Range("A1").CurrentRegion.AutoFilter 2, v(i, 1)
                fVisRow = Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
                lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
                If fVisRow <> lVisRow Then
                    Set rng = Range("U2:U" & lVisRow - 1).SpecialCells(xlVisible)
                    rng = 0
                End If
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[UmairKamal]

Dear mumps,

You are a life saver. Initial testing of the data shows that the code works. Thank you so much for your assistance.

 

[mumps]

You are very welcome.

 

[UmairKamal]

You are very welcome.

dear mumps,

you were kind enough to help me with this code which was working fine up until today when it has started giving me a runtime error 1004 method range of object _global failed" VBA error. when i press debug, the following line of the code is highlighted.

Set rng = Range("T2:T" & lVisRow - 1).SpecialCells(xlVisible)

can you please advise a solution?

 

[mumps]

The original code referred to column U instead of column T. Has your file changed? Please use the XL2BB add-in (icon in the menu) to attach an updated screenshot (not a picture) of your sheet.