macro needed to sum specific data in specific cells

N

nf24eg

Board Regular
Joined
Nov 15, 2009
Messages
152
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
I attached a file which explains in the comments of the cells what i exactly i try to do, as a simple example >>

<table border="0" cellpadding="0" cellspacing="0" width="899"><col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col style="mso-width-source:userset;mso-width-alt:8301;width:170pt" width="227"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" width="64"> <tbody><tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl66" style="height:31.5pt;width:47pt" height="42" width="63">A</td> <td class="xl66" style="width:108pt" width="144">B</td> <td class="xl66" style="width:170pt" width="227">C</td> <td class="xl66" style="width:62pt" width="82">D</td> <td class="xl65" style="width:55pt" width="73">E</td> <td class="xl65" style="width:54pt" width="72">F</td> <td class="xl65" style="width:60pt" width="80">G</td> <td class="xl65" style="width:71pt" width="94">H</td> <td class="xl65" style="width:48pt" width="64">I</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl64" style="height:30.0pt" align="right" height="40">97301003</td> <td class="xl64" align="left">International Calls</td> <td class="xl64" align="left">SYRIA 0096316236443</td> <td class="xl64" align="right">0.21</td> <td class="xl65" style="width:55pt" width="73">Other Charges</td> <td class="xl65" style="width:54pt" width="72">Rental VPN Mon.</td> <td class="xl65" style="width:60pt" width="80">Local Calls VPN</td> <td class="xl65" style="width:71pt" width="94">International Calls</td> <td class="xl65" style="width:48pt" width="64">SMS</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">97331379</td> <td class="xl64" align="left">Short Message</td> <td class="xl64" align="left">ON SMS ORIGINATING</td> <td class="xl64" align="right">0.02</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99398047</td> <td class="xl64" align="left">Roaming Forward</td> <td class="xl64" align="left">ITALY 66930613</td> <td class="xl64" align="right">3.5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99011639</td> <td class="xl64" align="left">Other Charges</td> <td class="xl64" align="left">OFFNET MMS</td> <td class="xl64" align="right">0.075</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="right" height="20">97166333</td> <td class="xl64" align="left">Local Calls</td> <td class="xl64" align="left">VPN PKG3 OFNT OPK</td> <td class="xl64" align="right">0.232</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99229841</td> <td class="xl64" align="left">International Calls</td> <td class="xl64" align="left">EGYPT 0020100339333</td> <td class="xl64" align="right">1.05</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="right" height="20">99423349</td> <td class="xl64" align="left">Short Message</td> <td class="xl64" align="left">OFF SMS MO TO NMTC</td> <td class="xl64" align="right">0.04</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99398047</td> <td class="xl64" align="left">Roaming SMS</td> <td class="xl64" align="left">KUWAIT 96396000303</td> <td class="xl64" align="right">0.15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99398047</td> <td class="xl64" align="left">Roaming Abroad</td> <td class="xl64" align="left">ITALY 393337736003</td> <td class="xl64" align="right">1.2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="right" height="20">97301003</td> <td class="xl64" align="left">International Calls</td> <td class="xl64" align="left">SYRIA 0096316236443</td> <td class="xl64" align="right">3.99</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="left" height="20">99443614</td> <td class="xl64" align="left">International Calls</td> <td class="xl64" align="left">JORDAN 00962796878170</td> <td class="xl64" align="right">0.442</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

so i want the total of JOHN S. (5) to be in the Col. C and to delete the line of JOHN S. 3 to not get duplicated amount

this is a brief of what i try to get ,but the file that i attached is more bigger , and it will be better if it can be a MACRO not a formula

Thank you in advance
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
**Can't Edit the Original Thread so I explained here**
it's a telephone sheet that i try to do in MACRO to fill the cells under Col.s (E , F , G ,H , I)
Col. A contains the telephone numbers which repeat the number more than one time because of the services that works on it
which mean to fill
*Clm. (E) SUM the Values(from Clm. D) if it Titled as "Other Charges" in Clm.(B).
*Clm. (F) SUM the Values(from Clm. D) if it Titled as "Rental" in Clm.(B) AND Begin by "VPN Mon" OR "Data" OR "Monthly" OR "Wiyana" in Clm. (C).
*Clm. (G) SUM the Values(from Clm. D) IF it Titled as "Local Calls" in Clm.(B) AND Begin by "VPN "
in Clm. (C).
*Clm. (H) SUM the Values (from Clm. D) IF it Titled as "International Calls" in Clm.(B) Plus "Roaming Abroad" Plus "Roaming Forward".
*Clm.(I) SUM the values (from Clm. D) IF it Titled as "Short Message" in Clm.(D) Plus "Roaming SNS"

<table border="0" cellpadding="0" cellspacing="0" width="1023"> <col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:6838;width:140pt" width="187"> <col style="mso-width-source:userset;mso-width-alt:8923;width:183pt" width="244"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" width="64"> <tbody><tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl68" style="height:31.5pt;width:85pt" height="42" width="113">A</td> <td class="xl68" style="width:140pt" width="187">B</td> <td class="xl68" style="width:183pt" width="244">C</td> <td class="xl68" style="width:72pt" width="96">D</td> <td class="xl67" style="width:55pt" width="73">E</td> <td class="xl67" style="width:54pt" width="72">F</td> <td class="xl67" style="width:60pt" width="80">G</td> <td class="xl67" style="width:71pt" width="94">H</td> <td class="xl67" style="width:48pt" width="64">I</td> </tr> <tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl68" style="height:31.5pt;width:85pt" height="42" width="113">number</td> <td class="xl68" style="width:140pt" width="187">service</td> <td class="xl68" style="width:183pt" width="244">description</td> <td class="xl68" style="width:72pt" width="96">amount</td> <td class="xl67" style="width:55pt" width="73">Other Charges</td> <td class="xl67" style="width:54pt" width="72">Rental VPN Mon.</td> <td class="xl67" style="width:60pt" width="80">Local Calls VPN</td> <td class="xl67" style="width:71pt" width="94">International Calls</td> <td class="xl67" style="width:48pt" width="64">SMS</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97243731</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">Data U.Std-Corp.-M.Subsc. 1 1</td> <td class="xl69">13</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97300308</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon.Subs. (101-200)-ME3 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97300308</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon. Subsc-ME3-Add on 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">0.21</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">0.63</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">3.99</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">0.84</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">OFF SMS ORIGINATING</td> <td class="xl69">0.02</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">2.52</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SAUDI ARABIA </td> <td class="xl69">1.55</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA </td> <td class="xl69">2.73</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon.Subs. (101-200)-ME3 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon. Subsc-ME3-Add on 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Local Calls</td> <td class="xl66" align="left">VPN PKG3 OFNT OPK</td> <td class="xl69">5.307</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Local Calls</td> <td class="xl66" align="left">VPN PKG3 OFNT PK</td> <td class="xl69">3.206</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">0.42</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">6.51</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">Miss U Service</td> <td class="xl69">0.06</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">BAHRAIN</td> <td class="xl69">0.155</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">ON SMS ORIGINATING</td> <td class="xl69">0.58</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">International Calls</td> <td class="xl66" align="left">SYRIA</td> <td class="xl69">2.94</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301003</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">OFF SMS MO TO NMTC</td> <td class="xl69">0.08</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">Monthy Subsc.handset 1GB 1</td> <td class="xl69">7</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">SMS INTERNATIONAL</td> <td class="xl69">1.15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Short Message</td> <td class="xl66" align="left">ON SMS ORIGINATING</td> <td class="xl69">0.3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon.Subs. (101-200)-ME3 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Rental</td> <td class="xl66" align="left">VPN Mon. Subsc-ME3-Add on 1</td> <td class="xl69">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Local Calls</td> <td class="xl66" align="left">VPN PKG3 OFNT PK</td> <td class="xl69">4.858</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">97301219</td> <td class="xl66" align="left">Local Calls</td> <td class="xl66" align="left">VPN PKG3 OFNT OPK</td> <td class="xl69">8.787</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody> </table>

and after get the totals in the empty columns the duplicated number must be deleted to not get the total twice

IS that possible ??

Thank you in advance:confused:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top