Sum Data of Non-Consistent AlphaNumeric String

mrmmickle1

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
I have Parent Jobs (Always 7 characters, alpha numeric) and Sub Jobs(Job Numbers with single dashes or two dashes). The Subs have revenue attached to them. I would like to sum up the value of the Parent(The Job without a dash) and all of its sub jobs (Jobs with single and two dashes) and insert the total in column K adjacent to the Parent (7 character , alphanumeric Job) After this is complete I would like to delete all Sub Jobs.
Note: Not All Jobs Have Subs. If Jobs do not have a Parent(7 character unique alphanumeric code) then the sum would need to be rolled into the next lowest heirarchy.

I have the following Data:

Excel 2010
ABCDEFGHI
HeaderHeaderHeaderHeaderHeaderHeaderHeaderHeaderHeader
SKYSWAN200512704458-1Waiting PartsMOTOR ENGINE STARTAPU-LRU
SKYSWAN20051-1515955-10AssemblySTATOR
SKYSWAN200522704458-1Waiting PartsMOTOR ENGINE START47-380APU-LRU
SKYSWAN20052-1515955-10AssemblySTATOR
GENEAAN20071-10160186-1RETURN AS ISOIL TEMP REG.86-1191APU-LRU
GENEAAN20071-113880200-6QuotePUMP ASSEMBLY GENERATOR SCAVENGENAAPU-LRU
GENEAAN20071-12397728-8InvoicingLOAD CONTROL VALVE3573CAPU-LRU
GENEAAN20071-133790076-105RETURN AS ISIGV ACTUATORAPU-LRU
GENEAAN20071-143882550-3InvoicingVALVE FL DIV AND DRA416CAPU-LRU
GENEAAN20071-153880190-10Outside VendorLUBE PUMPA3873APU-LRU
GENEAAN20071-163888076-3InvoicingLEAD ELEC INGNTR PLN/AAPU-LRU
GENEAAN20071-173882492-5QuoteNOZZLE AND MANF ASSYNAAPU-LRU
GENEAAN20071-183882491-3QuoteNOZZLE AND MANF ASSYNAAPU-LRU
GENEAAN20071-19979786-4InvoicingVALVE, SHUTOFFP-223APU-LRU
GENEAAN20071-23614400-8RETURN AS ISTRANSDUCER ASSYNAAPU-LRU
GENEAAN20071-203876035-5QuoteSENSOR PRESSAPU-LRU
GENEAAN20071-213882500-17QuoteFUEL CONTROL14363CAPU-LRU
GENEAAN20071-223888208-2QuoteHARNESSNAAPU-LRU
GENEAAN20071-233876026-3InvoicingTRANSDUCER, MOTIONAL (MONOPOLE)NA
GENEAAN20071-243876026-3InvoicingTRANSDUCER, MOTIONAL (MONOPOLE)N/A
GENEAAN20071-253876027-4RETURN AS ISTHERMOCOUPLE IMRS2.2E+10
GENEAAN20071-263876027-4ARETURN AS ISTHERMOCOUPLE2.2E+10
GENEAAN20071-273876027-4BRETURN AS ISTHERMOCOUPLE2.2E+10
GENEAAN20071-283876027-4CRETURN AS ISTHERMOCOUPLE04-01AU3-241
GENEAAN20071-293876072-2RETURN AS ISSENSOR TEMP1.2E+10
GENEAAN20071-33888058-5QuoteEXCITER IGNITIONAPU-LRU
GENEAAN20071-303876023-2ReceivedSWITCH TEMP
GENEAAN20071-313876024-6InspectionSWITCH PRESS LOW OIL
GENEAAN20071-323616848-2RETURN AS ISVALVE SHUTOFF GRBXNA
GENEAAN20071-333876166-1QuoteVALVE DEOILING SOLAPU-LRU
GENEAAN20071-34979806-12QuoteVALVE, SURGE CONTROL1509CAPU-LRU
GENEAAN20071-4519858-8QuoteMOTOR OUTLINE, ENGINE STARTER58-1619CAPU-LRU
GENEAAN20071-53616959-6QuoteFAN ASSEMBLYP-839CAPU-LRU
GENEAAN20071-5-13616959-6ReceivedFAN ASSEMBLYP-839C
GENEAAN20071-63611170-1ReceivedTUBING & PLUMBINGNA
GENEAAN20071-73801003-7AssemblyPOWER SECTIONP-555APU-LRU
GENEAAN20071-7-13840006-1ReceivedWHEEL ASSY1.04E+10
GENEAAN20071-7-23840022-4ReceivedWHEEL ASSEMBLY, TURBINE, AXIAL FLO
GENEAAN20071-83804002-10ApprovedCOMP ASSY-DRIVENP-404
GENEAAN20071-8-13822074-3ReceivedCOMPRESSOR ROTOR, CENTRIFUGAL, GASLN00P300
GENEAAN20071-8-23840002-5ReceivedWHEEL ASSY TURB 1STG9.80E+11
GENEAAN20071-9381389-1ReceivedENGINE ASSEMBLYP-1057AAPU
PRENAAN20073380754-1-2Waiting ApprovaGTCP36-4A APUP-37454APU
PRENAAN20073-1697952-2Waiting ApprovaROTOR
PRENAAN20073-103603850-1Waiting ApprovaHOUSINGID-U016819
PRENAAN20073-11695967-1Waiting ApprovaBODYN/A
PRENAAN20073-12695968-1Waiting ApprovaADAPTER
PRENAAN20073-13696117-4Waiting ApprovaHOUSING
PRENAAN20073-143601169-12Waiting ApprovaPLENUM
PRENAAN20073-15695022-2Waiting ApprovaCAP
PRENAAN20073-16695962-2Waiting ApprovaSHAFT
PRENAAN20073-23603115-5Waiting ApprovaWHEEL AND SHAFT ASSEMBLY, TURBINEUNKNOWN
PRENAAN20073-3977500-4Waiting ApprovaIMPELLER1.04E+10
PRENAAN20073-4976143-5Waiting ApprovaTORUS
PRENAAN20073-53606700-1Waiting ApprovaFCUP-122C
PRENAAN20073-63606810-1Waiting ApprovaTANKP-1255APU-LRU
PRENAAN20073-6-13606810-1Waiting ApprovaTANKP-1255APU-LRU
PRENAAN20073-73605812-21InvoicingSTARTERP 13090APU-LRU
PRENAAN20073-7-13605812-21Post TestSTARTERP 13090APU-LRU
PRENAAN20073-8369945-19Waiting ApprovaATOMIZERN/AAPU-LRU
PRENAAN20073-93600067-4Waiting ApprovaADAPTERN/A

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3/12/2012[/TD]

[TD="align: right"]824[/TD]

[TD="align: right"]$2,595.00 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3/28/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,065.00 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3/12/2012[/TD]

[TD="align: right"]$5,900.00 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3/28/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$5,028.00 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$3,878.00 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$2,319.00 [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$5,105.00 [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]921[/TD]

[TD="align: right"]$8,860.00 [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$2,276.00 [/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$5,309.00 [/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$3,430.00 [/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$4,491.00 [/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$5,748.00 [/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$641.00 [/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]4/24/2012[/TD]

[TD="align: right"]$7,738.00 [/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"][/TD]

[TD="align: right"]$2,520.00 [/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$7,679.00 [/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]$5,160.00 [/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$2,121.00 [/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$3,680.00 [/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,725.00 [/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$5,723.00 [/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$6,980.00 [/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,344.00 [/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,016.00 [/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]4/24/2012[/TD]

[TD="align: right"]474231[/TD]

[TD="align: right"]$1,755.00 [/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"]3455[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,719.00 [/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"]1156[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,852.00 [/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$3,212.00 [/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"]5253[/TD]

[TD="align: right"]$7,744.00 [/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]5/1/2012[/TD]

[TD="align: right"]$8,313.00 [/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]4/24/2012[/TD]

[TD="align: right"]$7,098.00 [/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]4/24/2012[/TD]

[TD="align: right"]$8,749.00 [/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]5/4/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,935.00 [/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]4/26/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$2,844.00 [/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]4/26/2012[/TD]

[TD="align: right"]$1,909.00 [/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$2,840.00 [/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"]1.03E+09[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,377.00 [/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]4/26/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$3,029.00 [/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$5,067.00 [/TD]

[TD="align: center"]42[/TD]

[TD="align: right"]4/27/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,995.00 [/TD]

[TD="align: center"]43[/TD]

[TD="align: right"]4/26/2012[/TD]

[TD="align: right"]$1,997.00 [/TD]

[TD="align: center"]44[/TD]

[TD="align: right"]3/7/2012[/TD]

[TD="align: right"]$5,432.00 [/TD]

[TD="align: center"]45[/TD]

[TD="align: right"]4/2/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,746.00 [/TD]

[TD="align: center"]46[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$5,147.00 [/TD]

[TD="align: center"]47[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$7,373.00 [/TD]

[TD="align: center"]48[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$948.00 [/TD]

[TD="align: center"]49[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,389.00 [/TD]

[TD="align: center"]50[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,257.00 [/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]4/4/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$5,704.00 [/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]4/4/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$968.00 [/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]4/2/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$9,407.00 [/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]4/2/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$8,499.00 [/TD]

[TD="align: center"]55[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$8,880.00 [/TD]

[TD="align: center"]56[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$3,378.00 [/TD]

[TD="align: center"]57[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"]$9,116.00 [/TD]

[TD="align: center"]58[/TD]

[TD="align: right"]4/10/2012[/TD]

[TD="align: right"]$2,216.00 [/TD]

[TD="align: center"]59[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"]$9,247.00 [/TD]

[TD="align: center"]60[/TD]

[TD="align: right"]4/11/2012[/TD]

[TD="align: right"]$1,215.00 [/TD]

[TD="align: center"]61[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"]$5,489.00 [/TD]

[TD="align: center"]62[/TD]

[TD="align: right"]4/3/2012[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$3,422.00 [/TD]

</TBODY>
Sheet1​



A few Mr. Excel Members have helped me to do something similar. Is there away to adapt this code by adding some kind of string dimension to get this end result:
Code: 
Sub InsertSums()
    Dim c As Range
    For Each c In Range("M:M").SpecialCells(xlConstants).Areas
        If c(1).row > 1 Then c(0) = "=sum(" & c.Address & ")"
    Next
End Sub

Excel 2010
ABCDEFGHI
HeaderHeaderHeaderHeaderHeaderHeaderHeaderHeaderHeader
SKYSWAN200512704458-1Waiting PartsMOTOR ENGINE STARTAPU-LRU
SKYSWAN200522704458-1Waiting PartsMOTOR ENGINE START47-380APU-LRU
GENEAAN20071-23614400-8RETURN AS ISTRANSDUCER ASSYNAAPU-LRU
PRENAAN20073380754-1-2Waiting ApprovaGTCP36-4A APUP-37454APU

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3/12/2012[/TD]

[TD="align: right"]824[/TD]

[TD="align: right"]$11,660.00 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3/12/2012[/TD]

[TD="align: right"]$10,928.00 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4/24/2012[/TD]

[TD="align: right"]$191,178.00 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3/7/2012[/TD]

[TD="align: right"]$94,833.00 [/TD]

</TBODY>
Sheet1​

Is it feasible to get rid of the dashes first?
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I realized I forgot to specify the job # column....which is column C

It would be possible to use the first 7 characters only in column C of the Job # Is there a way to strip all the dashes off of column C to make it easier to compile the data? I can see this may cause a huge headache later. So I can use the first seven characters only. Should I create a helper column using the left function to accomplish this and then go from there?
 
Last edited:
Upvote 0
That would be how I would do something like this. You can then construct a Pivot table to total around the helper column to sum the totals for the job.

Cheers, :)
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG14Sep15
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Left(Dn.Value, 7)) [COLOR="Navy"]Then[/COLOR]
        .Add Left(Dn.Value, 7), Dn
        Dn.Offset(, 8).Value = Dn.Offset(, 6).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Left(Dn.Value, 7)).Offset(, 8).Value = .Item(Left(Dn.Value, 7)).Offset(, 8).Value + Dn.Offset(, 6).Value
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Insert a blank row when the cell in column "B" changes.
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs
E
  • Solved
Extricate strings from cells based on the non-alphanumeric char
Replies
2
Views
446
ellison
E
P
  • Question Question
Data not copying fully into ListBox2
Replies
0
Views
311
Patriot2879
P
A
  • Question Question
Lookup+sum+max
Replies
2
Views
548
aarky
A
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
388
etaf
etaf

Forum statistics

Threads
1,223,710
Messages
6,174,019
Members
452,542
Latest member
Bricklin

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