Unique in a Range

[How_Do_I]

How how would I count the unique (Return in A1) and extract the unique from H6:N16 please (Returns A3 downwards)... Range H6:N16 contains blanks...

 

[James006]

Hello,

Your questions are not extremely precise ...

To count Unique values, you can test following :

=[URL="https://exceljet.net/excel-functions/excel-sumproduct-function"]SUMPRODUCT[/URL](1/[URL="https://exceljet.net/excel-functions/excel-countif-function"]COUNTIF[/URL]([SIZE=3]yourRange,yourRange[/SIZE]))

Hope this will help

 

[How_Do_I]

Hello there, I adjusted the ranges to make the Jeanie a little smaller... Your solution doesn't seem to be working James006...

Sheet1

	A	B	C	D	E	F	G	H
#DIV/0!	Apple		Fish
	Dog
	Orange	Dog
	Cat
		Cat
					Duck
		Bird
	Rabbit						Rabbit

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A1 =SUMPRODUCT(1/COUNTIF(B1:H11,B1:H11)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[James006]

Hello,

Since you have many Blank cells ...

=SUMPRODUCT(1/COUNTIF(B1:H11,B1:H11&""))

HTH

 

[How_Do_I]

Thanks for that James...

Has anyone got any ideas how to extract the data please...?

 

[James006]

You are welcome ...

How to extract what ...?

 

[Bo_Ry]

Please try at J2 and press Ctrl+Shift+Enter and drag down

=IFERROR(INDEX(INDEX($A$1:$H$11,N(IF(1,AGGREGATE(15,6,(ROW($A$1:$H$11)*100+COLUMN($A$1:$H$11))/($A$1:$H$11<>""),ROW(J1:J20))/100)),N(IF(1,MOD(AGGREGATE(15,6,(ROW($A$1:$H$11)*100+COLUMN($A$1:$H$11))/($A$1:$H$11<>""),ROW(J1:J20)),100)))),MATCH(0,COUNTIF(J$1:J1,INDEX($A$1:$H$11,N(IF(1,AGGREGATE(15,6,(ROW($A$1:$H$11)*100+COLUMN($A$1:$H$11))/($A$1:$H$11<>""),ROW(J1:J20))/100)),N(IF(1,MOD(AGGREGATE(15,6,(ROW($A$1:$H$11)*100+COLUMN($A$1:$H$11))/($A$1:$H$11<>""),ROW(J1:J20)),100))))),)),"")

 

[How_Do_I]

Thanks Bo_Ry, I couldn't get your solution to work... I had a good look around the Internet and found the following which seems to be working and extracting the unique alphabetically (cell A3 dragged down)... I think your count solution counts a blank cell James006 (see cell I1 below)…?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	14	Apple		Fish					15
2	Returns	Dog
3	Apple	Orange	Dog	Fritz			Tommy
4	Bird	Cat			Fritz
5	Cat			Duck
6	Cooper		Cat	Rhino		Fish
7	Dave					Duck
8	Dog						Fritz
9	Duck		Bird	Dave
10	Fish				Cooper		Gus
11	Fritz	Rabbit						Rabbit
12	Gus
13	Orange
14	Rabbit
15	Rhino
16	Tommy
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[James006]

Glad you could fix your problem ...