Combine/Aggregate lines into one where multiple. With common field.

[onmyway]

Hi guys,

I hope you can help me.

I have an excel sheet witch has multiple lines for the same job. How can I combine these lines to show a single line per reference, and aggregate the fields with values?

Example; There are 4 lines for this JobNumber: 201139433

OnsiteTime TravelTime TravelMiles SLATicks
INC000167981605 201139433 2014.01.31 2015.12.01 60 120 250 0
INC000167981605 201139433 2014.01.31 2015.12.07 120 120 250 0
INC000167981605 201139433 2014.01.31 2015.12.18 60 120 300 0
INC000167981605 201139433 2014.01.31 2015.12.28 60 120 250 0

And the idea is to have a single line for these 4 items, with the values of these 4 columns aggregated: OnsiteTime TravelTime TravelMiles SLATicks

The common denominator for the call is the JobNumber example: 201139433.

Please see attached example spread sheet.

Thank you in advance!

[hemesh]

how you want the values to show,

One way could be using pivot table.

[onmyway]

Hi hemesh. I want it to be in the same format, but all related items combined into a single line. I suppose one would have to create a loop that loops through the data and copy the data to a new sheet?

[hemesh]

I am not good in VBA but solution can be as below
first follow the below link
http://www.excelforum.com/tips-and-t...geravatar.html

copy the code provided
then open your sheet hit Alt+F11 click insert at the menu bar at the top and click module and paste code

Then create a new sheet copy paste all column headers and copy all job number and click remove duplicates

in C2 copy paste below then hold control and shift and then hit enter to make it array formula

Please Login or Register  to view this content.

drag down and drag to right

Change red part to your desired flavour may be "-" or "," or something

I have used char(10) to give it a line break character, after applying formula copy paste as values only and then with all selected click wrap text.

Hope that helps

[Kaper]

Hi onmyway,

I hope attachement is the same as in duplicated thread I started to answer: www.excelforum.com/showthread.php?t=1127126
If yes - Try this baby:

Please Login or Register  to view this content.

Note that it first sorts on column B to assure all records for one job are listed together, and then for duplicated jobs, mbut firlds with different values it collects all values (marked with exclamation mark). Of course it also fulfills the main task - summation of values in columns P:S

[onmyway]

hi hemesh, thanks for the feedback. Although a nice feature, it is not exactly what I want. This simply concatenates everything, where I would like the fields of the matching job numbers to be summed.

[onmyway]

@Kaper i think you are on something! Can you perhaps help to modify the function to sum the values of Columns P:S? Currently it aggregates it like this: ! 19 & 21 & 1. I want a single value, in this case 41.