Adding values from multiple rows with duplicate acount numbers?

Stellar · Jun 17, 2010

I do not even know how to properly request an answer but....

I have a large worksheet that is currently assorted by account numbers. There is currently an entry for EACH delinquent year.

example

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CDAVIDS%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CDAVIDS%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CDAVIDS%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style> <table class="MsoNormalTable" style="width: 414.3pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="552"> <tbody><tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]ACCOUNT<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]Year<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]DELINQUENT TOTAL<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]NAMEANDADD1<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]1228800020034<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2008<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000095413<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]ZAPP CHARLES L & LIZABETH I<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]1228800020034<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000139205<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]ZAPP CHARLES L & LIZABETH I<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]0201700000034<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000504740<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]5325 TELEPHONE ROAD<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]0202270000021<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000316112<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]EST OF RUBEN C MARTINEZ<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]0262150000016<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000249888<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]BREAULT ROBERT O<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]0391990000142<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000000060<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]PRAZAK JO ANN<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]0451430000104<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000024121<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]SANDERS ROSANNA ALFORD<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]1033760000019<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2007<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000017890<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]SCOTT LINWOOD J & SANDRA J<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 83.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="111" nowrap="nowrap"> [FONT=&quot]1211880050010<o

></o

>[/FONT]
</td> <td style="width: 35pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="47" nowrap="nowrap"> [FONT=&quot]2009<o

></o

>[/FONT]
</td> <td style="width: 98pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="131" nowrap="nowrap"> [FONT=&quot]000006000<o

></o

>[/FONT]
</td> <td style="width: 2.75in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="264" nowrap="nowrap"> [FONT=&quot]GLADYS E FOREMAN REVOCABLE TRU<o

></o

>[/FONT]
</td> </tr> </tbody></table>
I need to be able to end up with one entry by account number with the total sum from the 3rd column of each year (and it varies on each account).

So the first two records above are the same account number, but have (2) years of delinquent amounts. I need just (1) row with the mailing address columns but in the (3rd) column I want the sum of the two rows.

This is killing me, a) not knowing how to do it, and b) clueless how to search the answer or ask.

THANK YOU

swinglow · Jun 17, 2010

sounds like a PivotTable would be an ideal solution for you. Here's what your example looks like in PivotTable form:

<TABLE style="WIDTH: 559pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=744><COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8704" width=238><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 134pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" id=td_post_2347500 class=xl66 height=20 width=179>Sum of DELINQUENT TOTAL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 179pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" width=238></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 56pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" width=75>Year</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" width=77></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 62pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" width=82></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" width=93></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl66 height=20>ACCOUNT</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none">NAMEANDADD1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl67>2007</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl67>2008</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl67>2009</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl67>Grand Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>201700000034</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">5325 TELEPHONE ROAD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>504,740 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>504,740 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>202270000021</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">EST OF RUBEN C MARTINEZ</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>316,112 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>316,112 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>262150000016</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">BREAULT ROBERT O</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>249,888 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>249,888 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>391990000142</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PRAZAK JO ANN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>60 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>60 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>451430000104</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">SANDERS ROSANNA ALFORD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>24,121 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>24,121 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>1033760000019</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">SCOTT LINWOOD J & SANDRA J</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>17,890 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>17,890 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>1211880050010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">GLADYS E FOREMAN REVOCABLE TRU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>6,000 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>6,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>1228800020034</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">ZAPP CHARLES L & LIZABETH I</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>95,413 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>139,205 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>234,618 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 height=20>Grand Total</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68>17,890 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68>95,413 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68>1,240,126 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68>1,353,429 </TD></TR></TBODY></TABLE>

Stellar · Jun 17, 2010

That is exactly what I need the output to do, now I just need to figure out how to use that but at least I have a specific term and target

THANK YOU VERY MUCH SWINGLOW- this rocks

jc.021286 · Jun 17, 2010

Hi there,
Swing is right about the pivot table. It will make it look nice. though if you are simply running a query and don't need a table the formula to grab this is either

=sumif()
=sumif($A:$A,[FONT=&quot]1228800020034[/FONT],$C:$C)
--you can link the 1228800... to an actual cell to say, I want to find all references of this cell
or
=sumproduct()
sumproduct can be a bit more difficult, though we can test multiple conditions (ie: different years if needed, (can also be done with sumifs()))
=sumproduct(--($A:$A=1228800020034),$C$C)
this will have A:A tested against the code 12288... and return trues and falses. the "--" will convert to 1s and 0s. Multiply 0 with anything and it is zero, and 1*anything will give the anything. Thus, you will simply add those that meet your conditions.
If you wanted a specific year

=sumproduct(--($A:$A=1228800020034),--($B:$B=2009),$C$C)
this would weed out any values that did qualify though did not meet the 2009 requirement, the requirements can be linked to cells to save from having to enter.

Pivot table looks nice, if you just need a number, either of these 2 formulas will work.

jc

Stellar · Jun 17, 2010

Thank JC, I am starting to get the picture!

This is a huge database I pulled from Access, and we are using it to target a postcard mailing to home owners who need help paying their delinquent taxes in Harris county Texas. My challenge is to make sure I do not solicit someone that we cannot help - i.e., if their total tax due is to high etc.

I was able to refine the table down from 813,732 rows, to only 15k rows (much easier) but now I just need a single row with the account number, the name and address, the total tax added up from one or more years in the table, the total value and the LTV (not shown).

2/3rds of the rows are duplicates and a single sum would be fine - what ever I get figured out first! I am playing with your solution, and not sure how it removes the dupes yet but that is next.

Thanks so much, this forum is amazing.

jc.021286 · Jun 17, 2010

Ah Texas,

too hot for me...

As for your problem, that paints a much better picture,

I would create the pivot table and copy the consolidated accounts.
Paste special values elsewhere and you have effectively removed the duplicates.
Set up the sumproducts if need be to obtain the information.

You will probably need to resort to Index and Match or Vlookup formulas to pull the text based address data.

Or as swing showed, do the pivot table, copy pastespecial values gives you everything you need, then add on the Vlookup functions in the next few columns to pull the text based data and you are home free.

Once all is said and done, you can apply sorts and filters to your new data to filter out those that you cannot help/do not need.

Regards,
jc

Adding values from multiple rows with duplicate acount numbers?

Stellar

New Member

Excel Facts

swinglow

Active Member

Stellar

New Member

jc.021286

Well-known Member

Stellar

New Member

jc.021286

Well-known Member

Similar threads

Forum statistics

Share this page

Adding values from multiple rows with duplicate acount numbers?

Stellar

New Member

Excel Facts

swinglow

Active Member

Stellar

New Member

jc.021286

Well-known Member

Stellar

New Member

jc.021286

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock