Counting unique values

[HenryHenry2]

Hi all - hope someone can help.

I currently have a spreadsheet containing two columns of values like below.

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=368 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><TBODY><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>47388</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>152264</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>47388</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>152264</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>47388</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>176369</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>47388</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>152264</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>156689</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>118654</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>156689</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>156689</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>156689</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>118654</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>113232</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 118pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=157></TD></TR></TBODY></TABLE>

On another sheet I have a column of the unique values from column A and would like a count of the unique corresponding values against it in column B like below (no. 47388 has 2 different corresponding values in column B and no. 222578 has 3 different values).

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=211 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>47388</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>2</TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.6pt; BACKGROUND-COLOR: transparent" width=119 height=16>222578</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 69pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>3</TD></TR></TBODY></TABLE>

Anyone know a formula to help me achieve this?

Much appreciated.

 

[kidwispa]

Hi all - hope someone can help.

I currently have a spreadsheet containing two columns of values like below.

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=368><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><TBODY><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>47388</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>152264</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>47388</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>152264</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>47388</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>176369</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 height=16 width=119>47388</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>152264</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>156689</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>118654</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>156689</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>156689</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>156689</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>118654</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>113232</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=157></TD></TR></TBODY></TABLE>

On another sheet I have a column of the unique values from column A and would like a count of the unique corresponding values against it in column B like below (no. 47388 has 2 different corresponding values in column B and no. 222578 has 3 different values).

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=211><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>47388</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>2</TD></TR><TR style="HEIGHT: 12.6pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.6pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 height=16 width=119>222578</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=92>3</TD></TR></TBODY></TABLE>

Anyone know a formula to help me achieve this?

Much appreciated.

Assuming your data above starts in A1 on sheet1, and your second sheet is called sheet2 and data starts in A1, put this into b2 on sheet2...

=SUMPRODUCT(--(Sheet1!$A$1:$A$11=A1),1/COUNTIF(Sheet1!$B$1:$B$11,Sheet1!$B$1:$B$11))

 

[ganeshpoojary05]

hi kidwispa,

the above formula is working, but when we delete any of the rows from the sheet1 then the formula doesnt works, any suggestion on this.

 

[kidwispa]

hi kidwispa,

the above formula is working, but when we delete any of the rows from the sheet1 then the formula doesnt works, any suggestion on this.

Once you have copied the formula down for all instances you are looking to count on sheet2, highlight all formulas, press Ctrl + H to use Find & Replace:

Find What $
Replace With leave blank

So now your formula will look like this

=SUMPRODUCT(--(Sheet1!A1:A11=A1),1/COUNTIF(Sheet1!B1:B11,Sheet1!B1:B11))

This will allow you to delete rows whilst maintaining the correct answer (not ideal but the best I can do)

 

[HenryHenry2]

Thanks kidwispa, works a treat