Format the values obtained with SORT(UNIQUE(FILTER not entire column

[drom]

Hi and thanks in advance!

I have a table with many rows and columns Named Table1

I have named some of this table's databodyrange columns as eg:

• rcCountry 'This column header is country ans shows Countries

I am using in other tab/Sheet the following formula in order to get sorted and unique non blank countries 'Works FINE:

• =SORT(UNIQUE(FILTER(rcCountry,rcCountry<>"")))
• =SORT(UNIQUE(FILTER(rcCountry,--(rcCountry<>""))))
  • Does the same

If the obtained results are in sheet2 range C5: down

1. How can I format the obtained values not the entire column ?
   1. Is any way of referencing this say new non table as a table or
2. Is it possible to name this dinamic new array ? without using offset(C5;0;0:Counta(....

Thanks again !

 

[coulombevin]

Hi drom,

If you only want to add color and formating, I would use conditional formating with custom formula:

=AND(ROW($C1)>5,ISEVEN(ROW($C1)),$C1<>"")

*Sorry for the french version

Classeur1
	A	B	C
1	Portugal
2	Guatemala
3	Namibia
4	Kazakhstan
5	Sri Lanka		Unique
6	Mongolia		Colombia
7	Kazakhstan		Finland
8	Finland		Guatemala
9	Colombia		Kazakhstan
10	Guatemala		Madagascar
11	Madagascar		Mongolia
12	Colombia		Namibia
13	Colombia		New Zealand
14	New Zealand		Portugal
15	Colombia		Sri Lanka
16	Namibia
17	Namibia
18	Portugal
19	Sri Lanka
20	New Zealand
21	Colombia
22	Guatemala
23	Guatemala
24	Sri Lanka
25	Kazakhstan
26	Colombia
27	Finland
28	Madagascar
29	Portugal
30	Colombia
31	Mongolia
32	New Zealand
33	Kazakhstan
34	Guatemala
35	Guatemala
36	Colombia
Feuil1

Cell Formulas
Range	Formula
C6:C15	C6	=SORT(UNIQUE(FILTER(A:A,A:A<>"")))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=ET(LIGNE($C1)>5;EST.PAIR(LIGNE($C1));$C1<>"")	text	NO
C:C	Expression	=LIGNE($C1)=1	text	NO

Bests regards,

Vincent

 

[drom]

I do no

Hi drom,

If you only want to add color and formating, I would use conditional formating with custom formula:

=AND(ROW($C1)>5,ISEVEN(ROW($C1)),$C1<>"")

*Sorry for the french version

Classeur1
	A	B	C
1	Portugal
2	Guatemala
3	Namibia
4	Kazakhstan
5	Sri Lanka		Unique
6	Mongolia		Colombia
7	Kazakhstan		Finland
8	Finland		Guatemala
9	Colombia		Kazakhstan
10	Guatemala		Madagascar
11	Madagascar		Mongolia
12	Colombia		Namibia
13	Colombia		New Zealand
14	New Zealand		Portugal
15	Colombia		Sri Lanka
16	Namibia
17	Namibia
18	Portugal
19	Sri Lanka
20	New Zealand
21	Colombia
22	Guatemala
23	Guatemala
24	Sri Lanka
25	Kazakhstan
26	Colombia
27	Finland
28	Madagascar
29	Portugal
30	Colombia
31	Mongolia
32	New Zealand
33	Kazakhstan
34	Guatemala
35	Guatemala
36	Colombia
Feuil1

Cell Formulas
Range	Formula
C6:C15	C6	=SORT(UNIQUE(FILTER(A:A,A:A<>"")))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=ET(LIGNE($C1)>5;EST.PAIR(LIGNE($C1));$C1<>"")	text	NO
C:C	Expression	=LIGNE($C1)=1	text	NO

Bests regards,

Vincent

I do not want to format the the entire column with conditional formatting
Don't worry, (J'habite à Hendaye)

 

[coulombevin]

I do no

I do not want to format the the entire column with conditional formatting
Don't worry, (J'habite à Hendaye)

Hi drom,
Unfortunately formating an undefined range is not possible with regular formula:
Apply Conditional Format to a Named Range
Conditional formatting applied to a named range reverts to an absolute reference? | Microsoft Community Hub
Excel - named range in a conditional formatting formula

VBA could do the trick depending on your needs we could try to look into it.

Bests regards,

Vincent, from Quebec (CA)