I have a list of films with a column for titles (A) and dates (B). Some films have duplicate titles but are made on different dates. How do I highlight identical duplicates and not simply duplicate film titles? Eg. only the two first occurrences should be highlighted below:
Solaris, 1972
Solaris, 1972
Solairs, 1968
Secondly, is it possible to extend this to three columns, distinguishing films of the same name and made on the same date but by a different director?
IHMO the Issue “behind” your Question can simply resolved by →→Data→Filter… …Option:without Duplicates but anyway, put into (eg. E1) the Formula:
=COUNTIF(($A$1:$A$1000)&($B$1:$B$1000);($A$1:$A$1000)&($B$1:$B$1000))>1
enter as Array-formula with:ctrl+shift+enter or the [x]array -option in the Formula-wizard.
Select Column A and B and enter into →Format→Conditional format→Condition→Formula: $E1
I tried this and it worked. Is there a way of doing this without having an additional column created though? I want to avoid this extra data if possible.
I want to highlight duplicates as they’re entered so filtering wouldn’t work so well.
Details of what I did (for people coming later, it was a bit tricky to figure out): I selected cell E2, pressed Ctrl+F2 to bring up the Function Wizard, checked “Array” and entered that formula, and it resulted in the whole E column having values of 0 or 1. Then the conditional formatting outlined above worked.
A database would circumvent the entire problem. You can setup a database table so it does not store any duplicate pairs of title and year.
If you don’t like additional columns, you don’t like spreadsheets anyway.
Ha you are probably right. The thing I like with spreadsheets is I can see everything at a glance and it looks nicer than text printed on a terminal. If you have a good resource for moving this data set to a database for a beginner I’d like to see that.
Until then I’m still holding out for a multi-input conditional formatting solution that doesn’t involve the creation of extra data.
Developing a database is development work. Development work means that the resulting priduct should be end user friendly but not the development process.
FilterData_LO7.odb (107.8 KB)
My simple demo database has nothing to do with text printed on a terminal.
The attached document opens with LibreOffice. The embedded input form “Filter Data” filters the data intuitively by category or date range and shows some calculated aggregatetions for the filtered data.
Reports print the filtered set.
The tables are set up to not store incomplete records.
Table “Catgegories” accepts no duplicate category name. Same with person names in “Persons”.
Thank you. Coincidentally I discovered LibreOffice Base. It’s going to take some time to learn this, but moving to databases looks like a much better option than continuing using spreadsheets.
Simple demo with one table where I look for duplicates in the numeric columnm “FK”. The form shows all FKs having any duplicates. While navigating through the form, the subform shows the respective table rows with the same FK value. You can delete and edit the subform’s table row.
The main form can’t be edited because it is a calculated table where each row in that form represents more than one record.
Dupes.odb (24.4 KB)