2

Background

I have a sheet that is sorted by Country Names, all unique Country Names are associated with more than one City.

Question

How do I alternate the color of every other Country Name, such that I can more easily distinguish which Cities belong to which Countries?

Data sample:

     A       B
1 Finland   Helsinki
2 Finland   Turku
3 France    Lyon
4 France    Metz
5 France    Paris
6 Germany   Aachen
7 Germany   Berlin
8 Germany   Bielefeld
9 Italy     Milan
10 Italy    Pavia
11 Italy    Pesaro
12 Italy    Rimini
13 Italy    Rome
14 Norway   Bergen
15 Norway   Oslo
16 Norway   Stavanger
17 Norway   Trondheim
Improve this question
1

1 Answer 1

Reset to default
3

Please select ColumnsA:B and apply apply a Custom formula is of:

=and(A1<>"",isodd(counta(unique($A$1:$A1)))=TRUE)

with one formatting of choice, and:

=and(A1<>"",isodd(counta(unique($A$1:$A1)))=FALSE)

with a different formatting of your choice.

AND
ISODD
COUNTA
UNIQUE

Improve this answer
5
  • It works! Does COUNTA provide the information sheets need to know, about which country is odd and which country is even?
    – Nowak
    Nov 24, 2017 at 2:09
  • But where is the logic behind this? To my understanding, COUNTA only provides us with the number of, in this case, unique text values, not whether France is even or Finland is odd. Could someone explain this further?
    – Nowak
    Nov 24, 2017 at 9:57
  • 1
    Tank you for the explanation @pnuts!
    – Nowak
    Nov 24, 2017 at 22:13
  • Hi again pnuts, This custom formula only formats the cells that are not empty. How do i make it format the entire specifiedt range?
    – Nowak
    Nov 28, 2017 at 15:50
  • I accidently put a $ here and($A1<>"", ) and now it does what i want it to do xD
    – Nowak
    Nov 28, 2017 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.