Consolidate rows

L

lYoung

New Member
Joined
Mar 1, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I have a table, I need to consolidate the rows per criteria in 2 columns, "payee" and "cost code" so "estimate total" and "actual costs" total.
CompanyJob IDClaim #AddressJob ContactPhonePhone TypePhone2Phone2 TypeSecondarySecondary PhoneSecondary Phone TypeSecondary Phone2Secondary Phone2 TypeEstimatorProject ManagerJob Created DateJob Completed DateLoss TypeFile ClosedCost CodeCost TypeCost DescriptionPayeeActual AmountBudget AmountEstimate AmountSource
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossAPP:SUBCONTRACTSub/TechSUBCONTRACTCleanMaster Services38.400QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossAPP:LaborLaborLabor00150.94
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossAPP:LaborLabor Burden %Labor0041.38
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialsMATERIALHome Depot613.800QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialscredit on third page. https://s.veryfi.com/s/lPPbqZHome Depot-289.1700QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialshttps://s.veryfi.com/s/giwnVKHome Depot342.9300QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialshttps://s.veryfi.com/s/JuXvd3Home Depot341.6100QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialshttps://s.veryfi.com/s/9ZnpypHome Depot145.2900QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MATERIALMaterialshttps://s.veryfi.com/s/bSSsm0Home Depot356.5200QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:SUBCONTRACTSub/TechSUBCONTRACTAlfredo Tovar503.600QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:SUBCONTRACTSub/TechSUBCONTRACTB2N Services125.4600QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:LaborLaborLabor00576.66
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:LaborLabor Burden %Labor00121.9
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCAB:MaterialMaterialsMaterial001379.5
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCLN:LaborLaborLabor00314.63
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCLN:LaborLabor Burden %Labor0071.49
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:GENERALOther/MileageGENERALHaulaway Storage Containers35800QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:SUBCONTRACTSub/TechSUBCONTRACT3 Stars Painting & Drywall62.6300QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:LaborLaborLabor00206.5
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:LaborLabor Burden %Labor0044
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:MaterialMaterialsMaterial0076.5
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:EquipmentEquipmentEquipment00458.39
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:LaborLaborLabor00614.7
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossCON:LaborLabor Burden %Labor00168.54
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDOR:LaborLaborLabor00194.29
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDOR:LaborLabor Burden %Labor0039.82
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDOR:LaborOther/MileageLabor0012.68
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDRY:MATERIALMaterialshttps://s.veryfi.com/s/4MctNnHome Depot108.1800QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDRY:MATERIALMaterialshttps://s.veryfi.com/s/jiNjS7Home Depot31.5400QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDRY:MATERIALMaterialsMATERIALHome Depot221.0200QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDRY:SUBCONTRACTSub/TechSUBCONTRACT3 Stars Painting & Drywall699.8400QuickBooks
USAAD18-1225006142241-01129 S Holman Way, Golden, CO 80401Matthew Smedberg(574) 217-5827Home(574) 217-5827CellTodd A. BergmanJeremy D. DobsonWater LossDRY:SUBCONTRACTSub/TechSUBCONTRACTAlfredo Tovar1766.7500QuickBooks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
like this?

with Power Query
Cost CodePayeeActualEstimate
APP:SUBCONTRACTCleanMaster Services38.40
APP:Labor0192.32
CAB:MATERIALHome Depot1510.980
CAB:SUBCONTRACTAlfredo Tovar503.60
CAB:SUBCONTRACTB2N Services125.460
CAB:Labor0698.56
CAB:Material01379.5
CLN:Labor0386.12
CON:GENERALHaulaway Storage Containers3580
CON:SUBCONTRACT3 Stars Painting & Drywall62.630
CON:Labor01033.74
CON:Material076.5
CON:Equipment0458.39
DOR:Labor0246.79
DRY:MATERIALHome Depot360.740
DRY:SUBCONTRACT3 Stars Painting & Drywall699.840
DRY:SUBCONTRACTAlfredo Tovar1766.750
 
Upvote 0
I'm sorry, I can do it in Power Query, however I need to write in VBA so someone can click a cmd button and run all the code I have for this spreadsheet. Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Delete Duplicates values in row and transfer to new excel sheet
Replies
0
Views
403
poonamSS
P
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
161
XLfriend777
XLfriend777
S
  • Question
Avoid Circular Ref
Replies
4
Views
411
ShalDRH
S
F
  • Solved
Filter leaves last row of data visible regardless of what is filtered
Replies
2
Views
586
frank_AL
F
A
  • Solved
Clubbing 2 if conditions into one
Replies
9
Views
682
ansvk1
A

Forum statistics

Threads
1,214,998
Messages
6,122,643
Members
449,093
Latest member
Ahmad123098

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