View Single Post
  #7   Report Post  
Old September 7th 19, 12:34 PM posted to microsoft.public.excel.programming
Peter T[_8_] Peter T[_8_] is offline
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