compare previous column entries against last entry

[skyport]

Hoping you can help with this one.

In column AL there will be continuous data added into the next available cell. As each new cell gets data inputted, I need a formula that can flag the most recent cell entry in that column AL when it is a duplicate of an already existing entry by color filling the cell in the same row but in column G along with a small text warning such as "Duplicate Entry". So, the color fill and text warning would show up in Column G on the same row as AL

 

[Teeroy]

You could do this by Formula and Conditional Formatting.

In G (I was testing in Row 7 but fill as required) e.g.
G7: =IF(COUNTIF(AL$2:AL7,"="&AL7)>1,"Duplicate Entry","")

And on that cell apply Conditional format:
G7: Format only cells that contain|cellvalue|equal to|="Duplicate Entry" Format|Fill|Red

 

[skyport]

Hi Teeroy, Thanks for taking time to help me. The formula puts the "Duplicate Entry in every cell in column G. What I need it to do is just fill the one cell in column G and leave the rest of the column G as is. As an example, assume in column AL the following entries occur:


Column AL
[TABLE="width: 276"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1 Irwin Savodnik, M.D. 40448[/TD]
[/TR]
[TR]
[TD]2 Khalid B.Ahmed, M.D. 41554[/TD]
[/TR]
[TR]
[TD]3 Khalid B.Ahmed, M.D. 41596[/TD]
[/TR]
[TR]
[TD]4 Andrew Roth, M.D. 41673[/TD]
[/TR]
[TR]
[TD]5 Khalid B.Ahmed, M.D. 41752[/TD]
[/TR]
[TR]
[TD]6 Khalid B.Ahmed, M.D. 41554

Considering that the final entry in row 6 matched the one in row 2, Column G would only have cell 6 filled with red with "Duplicate Entry" as the warning. All other cells in Column G would remain untouched because they may contain data.


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Teeroy]

So if I understand you correctly column G contains data? Please confirm.

If column G does contain data then we need to do something different and use VBA.

 

[skyport]

yes, what happens is if there is no red flag warning, data is put into the cell in column G and then a new entry goes into the next cell in AL. The idea is if the program causes the red flag in column G, then the data is not put in that cell in Column G because that data was entered previously. Consequently, more new data goes into column AL and if and when no red warning comes up in G, then data goes in there as well.

 

[skyport]

VBA might work of course if it were not something that needed to be ran as a macro after each data entry. Need to have something that auto functions as data is entered into AL

The closest I could come was the following formula,
=IF(ISERROR(MATCH(AL6,AL:AL,0)),"","ALERT")

which I thought COULD work if I only had a way to have cell AL6 compared for a match against the range of AL:AL-1 instead of AL:AL, in which case if that were possible, the function would search everything in AL for a match except the last entry and that would work however, don't know if this type of formula can be written with a AL:AL-1 concept

 

[skyport]

Regarding the above posting, my thinking had been that such a formula could be in each cell of column G even though data is put in that column, simply because for each row, the alert would be triggered in column G when applicable and then new data would then be put into that row/cell of column G eliminating the formula from that specific cell and the formula itself for that cell would not need to exist any longer. that same process would also have applied for all previous G column cells since G1