how to highlight duplicate text in columns?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

how to highlight duplicate text in columns?

bill-2
Hi all,
 
I'm thinking this would fall under conditional formatting, but have not been able to find the option i need in the program or manual. I need to search a single column containing some 12,000 rows of text and highlight any duplicates without removing anything. Any help much appreciated!
 
peace
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: how to highlight duplicate text in columns?

GnuMeric mailing list
I don't really know if the conditional format feature can do that, but you can use something like countif(A$1:A$10,A1) in another column to determine which cells are duplicates (i.e. count > 1).  Then you could use conditional formatting on the countif() column.

On Sat, Jun 30, 2018 at 5:41 PM, bill <[hidden email]> wrote:
Hi all,
 
I'm thinking this would fall under conditional formatting, but have not been able to find the option i need in the program or manual. I need to search a single column containing some 12,000 rows of text and highlight any duplicates without removing anything. Any help much appreciated!
 
peace
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list



_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: how to highlight duplicate text in columns?

Andreas J Guelzow

Let's say that A1:A10 is the region of interest.

Select A1 down to A10 so that A1 is the primary cell.
Then choose Format->Cells->Conditional Formatting
Click Edit Style and pick the Background Color Red, OK
Select as condition "Expression x evaluates to TRUE".
Enter as x: countif(A$1:A$10,A1)>1  and finish with tab
click "Add"
click "close"

Any cells with duplicate value in A1:A10 should now be highlighted in red.

Andreas


On 2018-07-01 06:13 AM, Bruce Hohl via gnumeric-list wrote:
I don't really know if the conditional format feature can do that, but you can use something like countif(A$1:A$10,A1) in another column to determine which cells are duplicates (i.e. count > 1).  Then you could use conditional formatting on the countif() column.

On Sat, Jun 30, 2018 at 5:41 PM, bill <[hidden email]> wrote:
Hi all,
 
I'm thinking this would fall under conditional formatting, but have not been able to find the option i need in the program or manual. I need to search a single column containing some 12,000 rows of text and highlight any duplicates without removing anything. Any help much appreciated!
 
peace
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list




_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list


_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: how to highlight duplicate text in columns?

GnuMeric mailing list
Thanks for that post, I did not know before how to use "Expression x evaluate to True".

On Sun, Jul 1, 2018 at 3:58 PM, Andreas J. Guelzow <[hidden email]> wrote:

Let's say that A1:A10 is the region of interest.

Select A1 down to A10 so that A1 is the primary cell.
Then choose Format->Cells->Conditional Formatting
Click Edit Style and pick the Background Color Red, OK
Select as condition "Expression x evaluates to TRUE".
Enter as x: countif(A$1:A$10,A1)>1  and finish with tab
click "Add"
click "close"

Any cells with duplicate value in A1:A10 should now be highlighted in red.

Andreas


On 2018-07-01 06:13 AM, Bruce Hohl via gnumeric-list wrote:
I don't really know if the conditional format feature can do that, but you can use something like countif(A$1:A$10,A1) in another column to determine which cells are duplicates (i.e. count > 1).  Then you could use conditional formatting on the countif() column.

On Sat, Jun 30, 2018 at 5:41 PM, bill <[hidden email]> wrote:
Hi all,
 
I'm thinking this would fall under conditional formatting, but have not been able to find the option i need in the program or manual. I need to search a single column containing some 12,000 rows of text and highlight any duplicates without removing anything. Any help much appreciated!
 
peace
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list




_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list


_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list



_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: how to highlight duplicate text in columns?

bill-2
In reply to this post by Andreas J Guelzow
Thank you so much guys, and especially Andreas (If you are not teaching this stuff somewhere, you should be). This is exactly what i needed, and your instructions are spot on. It's a little more involved than with LibreCalc or Excl, but a small sacrifice as i so much prefer Gnumeric.
 
Kudos to the list in general as well! Every time i post here i save a ton of time. I was actually considering doing a "copy" and "search" for each item in a long column.
 
peace
bill
 
Sent: Sunday, July 01, 2018 at 2:58 PM
From: "Andreas J. Guelzow" <[hidden email]>
To: [hidden email]
Subject: Re: how to highlight duplicate text in columns?

Let's say that A1:A10 is the region of interest.

Select A1 down to A10 so that A1 is the primary cell.
Then choose Format->Cells->Conditional Formatting
Click Edit Style and pick the Background Color Red, OK
Select as condition "Expression x evaluates to TRUE".
Enter as x: countif(A$1:A$10,A1)>1  and finish with tab
click "Add"
click "close"

Any cells with duplicate value in A1:A10 should now be highlighted in red.

Andreas

 
On 2018-07-01 06:13 AM, Bruce Hohl via gnumeric-list wrote:
I don't really know if the conditional format feature can do that, but you can use something like countif(A$1:A$10,A1) in another column to determine which cells are duplicates (i.e. count > 1).  Then you could use conditional formatting on the countif() column.
 
On Sat, Jun 30, 2018 at 5:41 PM, bill <[hidden email]> wrote:
Hi all,
 
I'm thinking this would fall under conditional formatting, but have not been able to find the option i need in the program or manual. I need to search a single column containing some 12,000 rows of text and highlight any duplicates without removing anything. Any help much appreciated!
 
peace
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
 
 
 
 
_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list

_______________________________________________ gnumeric-list mailing list [hidden email] https://mail.gnome.org/mailman/listinfo/gnumeric-list

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list