Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
"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 |
Reporting the maximum date within rows with the same keydata
Good
What is the exact use of using array in formula. |
Reporting the maximum date within rows with the same keydata
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 |
Reporting the maximum date within rows with the same keydata
"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 |
All times are GMT +1. The time now is 09:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com