Conditional data cleanup

[PSD]

Hi all,

I have a question on how to implement a line of code, selecting a specific cell value (and deleting a different one).
What I mean by this is the following (see image):

I have a range of time data points in a single column (either empty or not, in time format).
Whenever there are two (or more) consecutive cells consisting data, I wish to ONLY keep the "highest" (longest) value, deleting the rest of the grouped values.

Does anyone have a suggestion on how to go about this?

 

[maabadi]

Try this:

Sub Test()
Dim i As Long, j As Long, Lr As Long, Mx As Double
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
If j = 0 Then
j = Range("A" & i).End(xlDown).Row
If Range("A" & j - 1).Value = "" Then j = j - 1
End If
If Range("A" & i) = Application.WorksheetFunction.Max(Range("A" & i & ":A" & j)) Then
Range("B" & i) = Range("A" & i)
i = j
j = 0
End If
Next i
End Sub

 

[PSD]

Hi maabadi,

Thank you for the response.
I tried to add your proposed code using the VBA macro program, but I have to say I am quite inexperienced with this. It seems that when I run it, it only shows the first two entries and afterwards only blanks (see image).
Odds are that I am just not making it run correctly but this I simply do not know at this moment.

Thank you for the posed advice anyway, I will try to make it work in the meantime!

 

[maabadi]

Code works for me Correctly.
Please upload your Data file with XL2BB Add-in.
I don't know what is range of your data at image? Rows & Column Numbers.

 

[PSD]

Hi Maabadi,

I have attached a range of data points and error times on which I would like to run your proposed macro. (2nd column)

I feel like the problem lies with me not being able to successfully use your proposed macro.
Now even when I run the code (View -> Macro -> Run Macro) it does not change anything in the worksheet.
Any suggestion on what I might be forgetting or doing wrong?

GP3109CartonPackerFaults.xlsm
	H
8
Sheet2

GP3109CartonPackerFaults.xlsm
	E	F
1	Column1	Column2
2	00:31:22	00:00:01
3	00:31:23
4	00:31:49	00:00:05
5	00:31:54
6	01:19:46	00:01:11
7	01:20:57
8	01:35:17	00:00:07
9	01:35:24
10	01:41:35	00:00:07
11	01:41:42
12	02:18:11	00:01:16
13	02:19:27
14	02:33:46	00:00:39
15	02:34:25
16	02:41:40	00:00:29
17	02:42:09
18	02:43:46	00:00:14
19	02:44:00
20	03:09:35	00:13:22
21	03:22:57
22	03:48:34	00:00:53
23	03:49:27
24	03:49:38	00:00:05
25	03:49:43
26	03:53:30	00:00:36
27	03:54:06
28	03:55:53	00:01:00
29	03:56:53
30	03:57:18	00:00:56
31	03:58:14
32	04:13:17	00:00:57
33	04:13:20	00:01:42
34	04:14:14
35	04:15:02
36	04:16:35	00:01:06
37	04:17:41
38	04:20:31	00:01:14
39	04:21:45
40	04:26:13	00:01:50
41	04:28:03
42	04:31:21	00:00:14
43	04:31:23	00:00:03
44	04:31:26
45	04:31:35
46	04:31:50	00:00:04
47	04:31:54
48	04:34:51	00:00:04
49	04:34:55
50	04:37:23	00:01:44
51	04:39:07
52	05:11:40	00:00:41
53	05:12:21
54	05:19:18	00:00:08
55	05:19:26
56	05:21:18	00:00:08
57	05:21:26
58	05:28:11	00:00:04
59	05:28:15
60	05:28:23	00:00:06
61	05:28:29
62	05:28:39	00:00:51
63	05:29:30
64	05:33:13	00:00:07
65	05:33:20
66	06:15:45	00:00:11
67	06:15:56
68	06:20:35	00:00:09
69	06:20:44
70	06:23:27	00:00:09
71	06:23:36
72	06:23:54	00:00:09
73	06:24:03
74	06:24:09	00:00:16
75	06:24:25
76	06:24:31	00:01:09
77	06:25:40
78	06:28:04	00:00:08
79	06:28:12
80	06:44:29	00:00:33
81	06:45:02
82	06:46:32	00:00:53
83	06:47:25
84	06:54:37	00:00:14
85	06:54:51
86	07:09:09	00:00:29
87	07:09:38
88	07:10:05	00:00:11
89	07:10:16
90	07:10:26	00:00:16
91	07:10:28	00:00:00
Sheet2

Cell Formulas
Range	Formula
F2:F91	F2	=IF(C2=0,"",IFERROR(SUM(INDEX(FILTER(D3:E$20178,(B3:B$20178=B2)*(C3:C$20178=0)),1,))-SUM(D2:E2),"Open"))

 

[maabadi]

You should change column letters within code to your data column names.
Try this:

Sub Test()
Dim i As Long, j As Long, Lr As Long, Mx As Double
Lr = Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
If j = 0 Then
j = Range("F" & i).End(xlDown).Row
If Range("F" & j - 1).Value = "" Then j = j - 1
End If
If Range("F" & i) = Application.WorksheetFunction.Max(Range("F" & i & ":F" & j)) Then
Range("H" & i) = Range("F" & i)
i = j
j = 0
End If
Next i
End Sub

 

[PSD]

Hi Maabadi,

That makes sense and is for sure one of the reasons why it did not work as intended.
However, trying to run the script with the correct column labelling did not necessarily do the trick.
Attached is a picture on where I have currently saved the macro, I feel like I am doing something wrong there. The labelling being for columns A and B is correct is that case (sheet 4). I ran and saved the macro after this. The script is also saved in Modules, under Modules 1.

Going towards the Excel sheet and running the macro from the View or Developer tabs does not show changes in the file.

Sorry for turning the original problem in to a sort of user experience trouble shoot problem, but I am curious as to why I seem unable to get a macro script running properly.

 

[Fluff]

You need to put the macro in a standard module, not a sheet module.

 

[PSD]

Hi Fluff,

Thank you for the response.
I have removed the scripts from the separate sheet module, only having it saved under Modules in Module1. This did not solve the problem, or is this not what you meant as a standard module?

 

[Fluff]

Putting it in Module1 is fine. Was the sheet you wanted the macro to work on the active sheet when you ran it?