VBA code to find most recent date in column A that match a value in column B

[georgeu]

I'm trying to find VBA code to be able to set the value in column U to "TRUE" when the Date in column A is the "most recent" based on a value in column B.

So what I have is a list of numbers in column B and a date in column A against each number. The numbers in column B may be repeated once or several times, but with different dates in column A against each instance of that number. I need the code to find the most recent entry for each number based on the date in column A and then set the value in column U to "TRUE" for that entry. It then needs to process the next number in column B in the same way, until it reaches the end of the record set.

It would be greatly appreciated if somebody could help me with this.

 

[iliace]

Is there a reason you need VBA code to do this? You can accomplish this with a formula - possibly a more elegant one than this, but it would work.

Do you have some sort of application where it is necessary to make this calculation as a VBA function instead?

Sheet1

	A	B	U

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]8-Nov-13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]11-Dec-13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]18-Dec-13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3-Jan-14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]29-Nov-13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]8-Dec-13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]11-Dec-13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]19-Dec-13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]29-Dec-13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2-Jan-14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]8-Nov-13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]17-Nov-13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]5-Dec-13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]31-Dec-13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]1-Jan-14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]21-Nov-13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]5-Dec-13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]19-Dec-13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]29-Dec-13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel U1 {=A1=INDEX($A$1:$A$19, MATCH(MAX($A$1:$A$19*($B$1:$B$19=B1)),$A$1:$A$19*($B$1:$B$19=B1),0))} <tbody> </tbody>

Enter the formula as an array. Use Ctrl+Shift+Enter, instead of just Enter, after you've inputted the formula.

Matrix verstehen

<tbody>
</tbody>

 

[georgeu]

Thank you for your quick reply. Unfortunately, I do need this to be done in vba code. However, I have been having some issue with getting the provided formula to work. I have 13,500 rows to process and trying to drag down on cell U1 to copy the formula with an appropriate row number adjustment, does not leave me with functional formulas in all the other rows. Short of having to manually perform a shift control enter on each of 13,500 rows (exceedingly laborious) I would need to do it in VBA for that reason alone. In any case when I have finished processing all the data I need to import it into an SQL database and don't want to be importing the formulas, only the TRUE or FALSE values from column U.

Thank you for your help with this, it is really appreciated as I was getting no where fast doing google searches for an answer.

 

[georgeu]

Incidentally, I just noticed that the formula doesn't seem to always work as expected but I can't see why. Sometimes it will mark both instances of a number as TRUE even though they clearly have different dates. Sometimes it will mark an older instance of a number as TRUE and the newer instance as false, which is quite strange. I should point out that the numbers in column B are alpha- numeric not just digits, in case that makes any difference to how the formula (and any possible VBA code) works. Maybe that is why the formula sometimes gets it wrong???

 

[georgeu]

Oops! It seems I was using the wrong method to copy the array into the other rows. I did some Google searching and found the correct method which does seem to work correctly. So, the formula works perfectly, thank you. I am just concerned that the formula and not the values may be what gets imported into the SQL database later? If the values are imported and not the formulas then I an live with this solution perfectly well.

 

[iliace]

I highly doubt that will be an issue, but if it's a concern, you can always copy/paste as values.

 

[georgeu]

iliace,

Yes, thank you, I had realised that, eventually, I could just copy and paste values as a way to get rid of the formulas. Your help with this has been immensely appreciated and has solved the problem for me. I have used an array once previously as a solution and yet did not realise that a formula could do what I needed so I am surprised and grateful to you. Thank you again.