Be Excellent To Each Other

Hello!

I'm trying to use Excel to do something at work, but I'm really shit at it.

My problem is this:

I've got a big list of words (over 1000) in a column, and I'm adding more to the same column. I want Excel to flag up if I repeat a word in the column that has already been typed in further up. I'm guessing it's some sort of if statement, telling it to change colour if I repeat a word, but I'm buggered if I know what to do.

Any ideas? Ta chaps!

That's.. kind of a databasey thing to be honest. I don't know how you'd do it in excel sorry. A quick solution might be perl or basic, just store the strings in an array and search it against the new one.

Which version of Excel? If it's 2007 the attached file does it. If it's earlier, I think we need to be more creative.

I'm not that good with excel, but you could use a count if in your B column (assuming the words are in the A column).



That is for cell B16. It reads cell A16 and checks A1-A15 for the same thing. You might not need the second bang.

[edit]Why the fuck am I using bangs? It's early. The above works fine.

Can you sort them? if you can, you can sort and then say =if(A2=A1, "Same", "") then copy that all the way down.

Well, then you might as well just use a unique records filter.

You beautiful man! That'll do the job, shame about the horrible pinky colour

Thanks everyone!

Click the column header. Select the Home tab. Select "Conditional Formatting" | "Highlight Cells Rules" | "Duplicate Values". Choose a different colour scheme from the drop-box.

This is a genuinely new and useful feature in Excel 2007.

It sure frikkin' is! I manage a spreadsheet where it needs to colour a cell based on a bundle of parameters, and the old conditional formatting tool is shite enough that I had to learn a bunch of VB code to get it to do what I wanted.

Now I can do most things with the conditional formatting tool. Woohoo!