[Solved] Conditional Format if text is same as another cell

[dejador]

Hi,

I am making a list of unique stock codes etc for my business, and this turns into a long list.

Is there a way to check if the exact same text that I am entering in column B has already been entered earlier in the sheet?

I know I could sort it, but I am sorting by Product Name, so I just want some sort of visual alert that this code has been used before. It would always be in column B

[Matareuz]

Hey,

Welcome to the forum,

I think that this could be userful for you.

viewtopic.php?f=9&t=105574&p=512564&hil ... te#p513521

[Mountaineer]

You may use

Code: Select all

COUNTIF(range;value)

To check only, if the code is used above, you should use a mixture of absolute and relative addressing:

Code: Select all

=COUNTIF($B$1:$B5;B5)

for a value in B5 - adapts to other rows.

So your condition may be

Code: Select all

COUNTIF($B$1:$B5;B5)>1

If this works for the single cell you may extend the range for the condition.

J

PS: You can also use validation of data.
A dialog will appear, if your condition is not met.

I use conditional formatting, if the collision just needs double checks and validation, when false values are not allowed.

PPS: A database will prevent this, if column is set UNIQUE.

[dejador]

Hi,

Thank you Matareuz but I am really not good with spreadsheets and that post just confused me even more than I already am.

~@mountaineer - =COUNTIF($B$1:$B5;B5) just turned B5 red (I have the conditional formula set up to turn the background red, am hoping if I enter a duplicate that both cells will highlight), the 2nd one didn't seem to do anything (I put the conditional formatting on B5)

I am sorry though I do not know how to extend the range? I am really quite new to all this fancy spreadsheet business and can really only do addition subtraction and multiplication formulas

I honestly don't know what my options are, I know it has to be a spreadsheet (working out profit margins and cost prices, so need those add subtract and multiplication cells) I just want it to alert me (and ideally highlight the duplicate cell) if I type the same code in twice, as I have quite a large stock list to go through and it will take me forever if I have to manually check each one, I thought conditional formula would work but if theres an easier way I'm all ears!

[Villeroy]

Select the whole range you want to format conditionally so that B5 is the active input cell and apply the conditional formatting to the selected range based on the formula for B5.
Or copy B5, select the whole range, Edit>Paste-Special... formatting only.

[MrProgrammer]

Hi, and welcome to the forum.

I am really quite new to all this fancy spreadsheet business and can really only do addition subtraction and multiplication formula

[Tutorial] Ten concepts that every Calc user should know
Section 8 explains why the formula below has several $ characters and why $B$2 is different than $B2.

I just want some sort of visual alert that this code has been used before. It would always be in column B

To alert you if the code has been used before, in cell B3, use Conditional Format formula SUMPRODUCT($B$2:$B2=$B3) as shown in the attachment. It means "are any of the previous cells in column B equal to the current cell?"
B2, the first cell after the column headings, does not need conditional formatting since the first stock code cannot be a duplicate.

202109091109.ods

(12.79 KiB) Downloaded 116 times

[Tutorial] The SUMPRODUCT function

I am sorry though I do not know how to extend the range?

Conditional formatting is considered formatting so you can enter it into the first cell where it is needed, B3. Then Edit → Copy (for B3), select the cells in column B where you want that formatting applied, say B4:B999, Edit → Paste Special → ☑Format (uncheck everything else) → OK. You could also use the Format Paintbrush on the Standard Toolbar.

… turned B5 red (I have the conditional formula set up to turn the background red, am hoping if I enter a duplicate that both cells will highlight)

No, B5 can highlight, but the previous cell cannot because there is no way for its formatting to know that B5 is now the active cell. Formula calculations cannot determine which cell is active; they only access cell values. A filter can show all of the rows where the value in column B has some specified value, or the Find command can locate values for you. Read about those suggestions in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum. Learning how to use filters is important if you have a long list.

the exact same text

The formula will detect a duplicate if it matches exactly. Value "Foo" is different than "Foo " (with a trailing space) even though the cell contents will look identical.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.