formula for count unique?

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
89
Is there a formula that counts unique values in a column. I'm creating the data in col a and col b with vba


Existing output

ColA ColB ColC
User1
10.10.10.10
10.10.10.10
10.1.1.1
10.1.1.1
10.4.4.4
blank row
blank row
User2
192.1.1.1
192.1.1.2
192.1.1.2
blank row
blank row




Desired output

ColA ColB ColC
User1
10.10.10.10
10.10.10.10
10.1.1.1
10.1.1.1
10.4.4.4
3
blank row
User2
192.1.1.1
192.1.1.2
192.1.1.2
2
blank row


Thanks
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,189
Office Version
2007
Platform
Windows
Put the following formula array in B2 and copy down.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:126.42px;" /><col style="width:126.42px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >User1</td><td >Unique</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">10.10.10.10</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">10.10.10.10</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">10.1.1.1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">10.1.1.1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">10.4.4.4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">User2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">192.1.1.1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">192.1.1.2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">192.1.1.2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">User2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14.1.1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14.1.1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">15.2.2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">15.2.2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=IF(AND(A2="",A1<>""),SUMPRODUCT((MATCH(OFFSET($A$1,MAX(IF(--ISNUMBER(SEARCH("User",$A$1:A2)),ROW($A$1:A2))),0):A1, OFFSET($A$1,MAX(IF(--ISNUMBER(SEARCH("User",$A$1:A2)),ROW($A$1:A2))),0):A1,0)=(ROW(OFFSET($A$1,MAX(IF(--ISNUMBER(SEARCH("User",$A$1:A2)),ROW($A$1:A2))),0):A1)-ROW(OFFSET($A$1,MAX(IF(--ISNUMBER(SEARCH("User",$A$1:A2)),ROW($A$1:A2))),0))+1))+0),"")}</td></tr></table></td></tr></table>
Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
89
Super,

works perfectly. Now, i need to break it down so I understand it. Many thanks

Have a Great Weekend!!!!!!!!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,189
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.

Have a Great weekend too :cool:
 

Forum statistics

Threads
1,089,223
Messages
5,406,948
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top