Hi,
I have written a formula:
=IF(AND(COUNTIF([Account ID],[@[Account ID]])>1,(LARGE([Date],1)<>[@Date]))=TRUE,"Duplicate (Most recent is Genuine)",...
That counts the number of duplicate account numbers and marks the one with the most recent date as "Genuine" and others as "Duplicate"
I now want to automate the creation of the table but don't know how to reference the [@[Account ID]] or [@Date]).
So far everything I have tried results in the cell being blank
Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("AF2:AF" & lastRow).FormulaR1C1 = "=iferror(VLOOKUP(RC[-18],Companies_Flag[[Name]:[Customer Exists]],5,0), """")"
Range("AF2:AF" & lastRow).Value = Range("AF2:AF" & lastRow).Formula
Range("Af1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Flag"
Dim lastRow2 As Long
lastRow2 = Cells(Rows.Count, 2).End(xlUp).Row
Range("AG2:AG" & lastRow2).FormulaR1C1 = "=IF(AND(COUNTIF(RC[-15],[@RC[-15]])]>1,(LARGE(RC[-31],1)<>[@RC[-31]]))=TRUE,'Duplicate', IF(OR([@RC[-12]]="", [@RC[-12]]>TODAY())=TRUE, IF(OR([@RC[-19]]='Rec', [@RC[-19]]='Rec',[@RC[-19]]='RRT - Rec', [@RC[-19]]='Rest')=TRUE, 'R&R',IF([@RC[-19]]='BAU', 'RCST', IF([@RC[-1]]=1, 'Tech', IF([@RC[-9]]<0.001, 'Tech',IF([@RC[-11]]<28, '< 28 Days', IF([@RC[-11]]>91, '> 90 Days', IF([@RC[-9]]<350, '< £350',IF(AND(XLOOKUP([@RC[-18]]], Logs[Name], Logs[Date], "", 0)<>"",XLOOKUP([@RC[-18]], Logs[Name], Logs[Created Date], "", 0)>[@RC[-13]]),'Checked','Genuine')))))))),'< today Maturity'))"
Range("AG2:AG" & lastRow2).Value = Range("AG2:AG" & lastRow2).Formula
Range("AG1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Notes"
