on duplicate entry in the same column - show popup

[sumesh56]

In my worksheet on column B if i make a second entry of the same name of stock it should give a popup :recurrence in cell B3
pls see the attached file

[Special-K]

Recurrence cell "B5, cell B10"
Unless you have TEXTJOIN, this is only possible via VBA.

I did see a formula that could do this last week but it was extremely complicated and most likelt data dependent.

You can use Conditional Formatting with a COUNTIF to highlight a duplicate when it's entered.

[Melvosh]

You could also use a COUNTIF formula with data validation to prevent duplicate entries, with an error message saying it's a duplicate value. It just wouldn't specify the duplicate cell:

Please Login or Register  to view this content.

Adjust your range as necessary. See attached file incorporating both solutions.

[dflak]

This should do it in the worksheet module. Note remove comments from column B.

Please Login or Register  to view this content.

[dflak]

Here is a non-vba version using the same logic. But you need the data in a table so the formula copies down and the range automatically extends with the table.

[KOKOSEK]

Into sheet module.

Please Login or Register  to view this content.

[sumesh56]

KOKOSEK,thanks for the code.
after adding the code to sheet module...........
RELIANCE is already in B7................
i entered reliance in B9. it gives a POPUP saying "recurrance B9...............
The pop up should say recurrance B7 and NOT B9 . the code should look for the previous entry and not for the present. if there are two occurances it should show all the cell addresses.

[sumesh56]

dflak, thanks for the code. i incorporated the code in the sheet module. when i enter reliance in cell B9 it does give a popup. But if there are already two entries and i am making the third it will give the same popup. code only takes the first entry reference.
i wish that the popup gives cell references.for example,,,,,,,,,,,, if "reliance" has two apperances before, then on the third entry the popup should say recurrences in cell b3 ,b9

[KOKOSEK]

Two small tweaks:

Please Login or Register  to view this content.

[sumesh56]

KOKOSEK, works fine.

if there is one comma after each cell address it would have been more convenient. can we incorporate that option also in the code?also make it case free. Uppercase/Lowercase(reliance/RELIANCE)

if reliance is there on B2
it is there in B12
then when i enter it on B20, the pop up should show ----recurrence in B2,B12

if i enter reliance in B28, the pop up should say........recurrence in B2,B12,B20

[KOKOSEK]

Please Login or Register  to view this content.

What you mean as case free?
reliance <> RELIANCE ---> remove UCase from code
reliance = RELIANCE ---> leave UCase in code

[sumesh56]

kOKOSEK, thanks for the code . it works fine. have a nice day