Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 4th 19, 03:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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

  #2   Report Post  
Old September 4th 19, 04:29 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #3   Report Post  
Old September 5th 19, 05:17 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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
  #4   Report Post  
Old September 5th 19, 05:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #5   Report Post  
Old September 5th 19, 05:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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


  #6   Report Post  
Old September 6th 19, 04:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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
  #7   Report Post  
Old September 7th 19, 12:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 12
Default 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 D2500001 (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



  #8   Report Post  
Old September 7th 19, 05:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default Reporting the maximum date within rows with the same keydata

Good
What is the exact use of using array in formula.
  #9   Report Post  
Old September 8th 19, 08:10 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 30
Default 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
  #10   Report Post  
Old September 8th 19, 11:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 12
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date reporting in cell Boenerge Excel Worksheet Functions 1 September 12th 07 01:48 PM
How do I view the maximum rows in Excel 2007 (Million Rows)? shanth Excel Discussion (Misc queries) 2 January 15th 07 05:45 PM
Problem with USEDRANGE.ROWS.COUNT reporting one row too many Peter Rooney Excel Programming 9 January 18th 06 03:51 PM
Reporting number of rows on a userform dht Excel Programming 1 June 23rd 04 11:13 AM
Selecting multiple rows from listbox for reporting Brett9[_2_] Excel Programming 3 November 5th 03 11:07 AM


All times are GMT +1. The time now is 02:50 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017