Hello,

Perhaps to solve with a formula instead of a macro.

In column H (=8) is text data reported and there are always more rows below with the same data in this column.

In column EY (=157) is in some records a date reported. It is possible that some records has no reported date and in some are different dates.

In column FK (=167) I need to report the maximum date that exist within the rows where in column H the same data is reported.

H EY FK
aaa 01-01-2020 01-01-2022
aaa 01-01-2022
aaa 01-01-2022 01-01-2022
aaa 01-01-2022 01-01-2022
bbb
bbb
bbb
ccc 01-01-2025 01-01-2025
ccc 01-01-2025
ccc 01-01-2025

Is somebody knows how to solve. Should be great :)

regards, Johan

Hi Johan,

Am Wed, 4 Sep 2019 07:34:55 -0700 (PDT) schrieb JS SL:

In column H (=8) is text data reported and there are always more rows below with the same data in this column.

In column EY (=157) is in some records a date reported. It is possible that some records has no reported date and in some are different dates.

In column FK (=167) I need to report the maximum date that exist within the rows where in column H the same data is reported.

H EY FK
aaa 01-01-2020 01-01-2022
aaa 01-01-2022
aaa 01-01-2022 01-01-2022
aaa 01-01-2022 01-01-2022
bbb
bbb
bbb
ccc 01-01-2025 01-01-2025
ccc 01-01-2025
ccc 01-01-2025

try:
=IF(MAX(IF($H$2:$H$1000=H2,$EY$2:$EY$1000))=0,"",M AX(IF($H$2:$H$1000=H2,$EY$2:$EY$1000)))
Insert this formula with CTRL+Shift+Enter (Array formula)


Regards
Claus B.
--
Windows10
Office 2016

Hello Claus,
Thanks.

Works oke for a few thousands records, but....... my sheet is 500.000 records and it needs a very veeeeeeeeeeery long time to get the results of the calculation. (approx 1 minute for 1% calculation result).
Perhaps, if possible, another solution that covers also the amount of records and gives with a bit more speed the results.


Regards, Johan

Hi Johan,

Am Wed, 4 Sep 2019 21:17:38 -0700 (PDT) schrieb JS SL:

Works oke for a few thousands records, but....... my sheet is 500.000 records and it needs a very veeeeeeeeeeery long time to get the results of the calculation. (approx 1 minute for 1% calculation result).
Perhaps, if possible, another solution that covers also the amount of records and gives with a bit more speed the results.

try:

Sub Test()
Dim LRow As Long, i As Long, First As Long, Cnt As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object
Dim rngD As Range, rngO As Range
Dim myMax As Date

Set myDic = CreateObject("Scripting.Dictionary")
With ActiveSheet
LRow = .Cells(.Rows.Count, "H").End(xlUp).Row
varData = .Range("H2:H" & LRow)
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
Next
varTmp = myDic.items
For i = LBound(varTmp) To UBound(varTmp)
First = Application.Match(varTmp(i), .Range("H:H"), 0)
Cnt = Application.CountIf(.Range("H:H"), varTmp(i))
Set rngD = .Cells(First, "EY").Resize(Cnt)
rngD.Select
myMax = Application.Max(rngD)
Set rngO = .Cells(First, "FK").Resize(Cnt)
If myMax 0 Then
rngO.Value = myMax
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi again,

Am Thu, 5 Sep 2019 18:22:52 +0200 schrieb Claus Busch:

try:

Sub Test()

there's one superfluous line in the code.
Try:

Sub Test()
Dim LRow As Long, i As Long, First As Long, Cnt As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object
Dim rngD As Range, rngO As Range
Dim myMax As Date

Set myDic = CreateObject("Scripting.Dictionary")
With ActiveSheet
LRow = .Cells(.Rows.Count, "H").End(xlUp).Row
varData = .Range("H2:H" & LRow)
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
Next
varTmp = myDic.items
For i = LBound(varTmp) To UBound(varTmp)
First = Application.Match(varTmp(i), .Range("H:H"), 0)
Cnt = Application.CountIf(.Range("H:H"), varTmp(i))
Set rngD = .Cells(First, "EY").Resize(Cnt)
myMax = Application.Max(rngD)
Set rngO = .Cells(First, "FK").Resize(Cnt)
If myMax 0 Then
rngO.Value = myMax
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

Thx!!
I'd used the last one. It works, so thanks for that.
To run this code for the 500.000 records took exact 58 minutes.
I have to take that into account and don't pushing the button frequently (or do it and take a coffee break).
So, thanks again. I can go on with it.

regards, Johan

"JS SL" wrote in message
Hello,

Perhaps to solve with a formula instead of a macro.

In column H (=8) is text data reported and there are always more rows
below with the same data in this column.

In column EY (=157) is in some records a date reported. It is possible
that some records has no reported date and in some are different dates.

In column FK (=167) I need to report the maximum date that exist within
the rows where in column H the same data is reported.

H EY FK
aaa 01-01-2020 01-01-2022
aaa 01-01-2022
aaa 01-01-2022 01-01-2022
aaa 01-01-2022 01-01-2022
bbb
bbb
bbb
ccc 01-01-2025 01-01-2025
ccc 01-01-2025
ccc 01-01-2025

Is somebody knows how to solve. Should be great :)

regards, Johan

I know you've already got a solution but, if I follow, a different approach
which might only take a few seconds

#1 Copy col-H and col-EY into a new sheet in columns B & C

#2 In Col-A add an index column 1,2,3 etc and fill down

#3 Custom sort col-B a-z, and col-C newset-oldest (not default oldest to
newest)

#4 In the name box select D2:D500001 (ie last row), format as date same as
col-C

#5 Still selected, formula in D2 =IF(B2=B1,D1,C2)
Ctrl-Enter to fill the formula (fill-down wouldn't work with mixed
empty cells)

#6 Still selected, Copy and paste back Values

#7 select A1 and Ctrl-A to select-all

#8 Sort on the index column-A to restore the original order

#9 Copy col-D to col-FK on the main sheet

Normally wouldn't need to copy to a new sheet, but with so many columns H to
FK and 500k rows the sort would take a long time.

Could write a little macro to do this.

Peter T

Good
What is the exact use of using array in formula.

Peter,

Thanks. Just as you mentioned, it's another solution.
I'd picked it up in the macro and it works oke.
Nice to see that with 'logical thinking' you can solve some issues with some simple formulas.

Thanks everybody that were involved to solve this !!

regards, Johan

"TIMOTHY" wrote in message
Good
What is the exact use of using array in formula.


I wasn't suggesting an array formula here. With a block of cells, typically
in a single row or column, Ctrl+Enter applies the same formula as the
activecell to the entire selection but adapted in each cell for relative
addressing as applicable.

An array formula is applied with Ctrl+Shift+Enter and the formula will be
placed between curly brackets

https://support.office.com/en-us/art...2-ecfd5caa57c7

Peter T