Need to count consecutive blanks for a long range!

[Chrican]

Hi all, I've been following this forum for a while now, couldn't do my job without it! Anyways, I finally came upon something that I can't seem to find from a search. Basically, I have two columns, A and B with about 20,000 entries in B. Column A has a list of service orders, and column B has a list of unit numbers associated with the service orders. What I need to do is create a new column that lists a count of how many units are associated with each individual service order number. These SO#'s in column A are from a pivot table, so they only list the number in the first instance and then leave blanks for each additional unit. So how can I count how many units? I've tried using sumif's, countif's, countblanks, a million different if statements, nothing seems to work very good. Any tips?

p.s. how do I post a table onto here? It'd be a lot easier to illustrate that way...

 

[Matty]

Welcome to the Board!

Indeed, a small illustration always helps. To post data, easiest way is to ensure you have black borders around your Cells, select the data, hit copy and then paste into your message.

Matty

 

[Chrican]

Ah, I was overthinking things again! Anyways, this should clarify it alot. Basically, I have the first table right now, and then I have a supplemental table listing all the service order numbers which I've sampled as the second one here. You can see the values I need there in the last column, but there are literaly thousands of these and I cannot count them all by hand!
So for example, the first few SO's are only one unit each, so the formula would give me "1" next to that number, but then the later ones like '752 or '56 would bring back "3", etc. Any tips?


<TABLE style="WIDTH: 175pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=234 border=0><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 122pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=163 height=20>Service Order Number</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=71>Unit No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526744</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526745</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526746</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526747</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526748</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526749</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526750</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526752</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620033</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526753</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620033</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526754</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620033</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526755</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620033</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">6202</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526756</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620033</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620050</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>526757</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">620041</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>SO#</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64># Unit's</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526744</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526745</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526746</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526747</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526748</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526749</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526750</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526752</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526753</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526754</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526755</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526756</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>526757</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR></TBODY></TABLE>

 

[Matty]

Given the number of Rows, it'd be easier if we could populate the empty 'Service Order Number' Cells with values.

To do this, based on the first table being in A1:B23, select A10:A23, hit F5 > Special > Blanks. Now hit =, click in Cell A9, and while holding down CTRL, hit ENTER. If done correctly, this will fill all the empty Cells with the associated 'Service Order Number'.

Now you can use SUMPRODUCT, like:

=SUMPRODUCT(--(A$2:A$23=D2),--(B$2:B$23<>""))

In E2 copied down, where your second table resides in D1:E14.

Is this something you can work with?

Matty

 

[Chrican]

That works perfectly! It may not be as neat as what I'd hoped to do, but I'll take what I can get. Thank you sir!

 

[Matty]

That works perfectly! It may not be as neat as what I'd hoped to do, but I'll take what I can get. Thank you sir!

No problem. I'm also interested to see a solution that does this without the need for populating the empty Cells. Let's see if anyone comes up with one...

Matty