Forum Discussion
Checking data in two rows, and if the same then take the latest time and date form another row
I have a question
I am looking for a formula of VBA for the following comparisation.
I want to filter or mark only the latest date and time in column G, from which data in column C and D are the same.
Basicly we have a registration lf KL0915Hn of date 10-08-2023. But it happend on different dates and time. I want to filter the row with the latest date and time.
So in case of yellow, i want to see only row4
in green i should see only row8
and in blue i want to see only row11
PLease help
You could create a PivotTable and move the date field into 'values' and then set field to 'max'. Then it's a matter of adding the desired fields in rows or columns to fill out the detail you need.
Attached is a sample.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue table or in another worksheet.
=IF(G2=LARGE(IF(($C$2:$C$11=C2)*($D$2:$D$11=D2),$G$2:$G$11),1),"X","")You can try this formula in order to mark the row with the highest value in column G. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
