Find similar lines and combine

  • Thread starter Thread starter Sami82
  • Start date Start date
S

Sami82

Hi All,
I am writing a smaller part of a large macro and i need it to find
similar lines within the table and combine the totals. The first 5
columns need to be the same to combine the rows, for example:

A B C D
E F
Name Code Start Finish Amount
Total

PersonA PERA 01/10/2005 10/10/2005 $100.00 $3542.25
PersonA PERA 01/10/2005 10/10/2005 $100.00 $5487.50
PersonA PERB 22/09/2005 30/09/2005 $2451.00 $2547.78
PersonA PERA 01/10/2005 10/10/2005 $24.00 $1542.00

As above, the first two lines would be replaced with a singular row,
and the bottom rows would stay the same.

PersonA PERA 01/10/2005 10/10/2005 $100.00 $9029.75
PersonA PERB 22/09/2005 30/09/2005 $2451.00 $2547.78
PersonA PERA 01/10/2005 10/10/2005 $24.00 $1542.00

If anyone can help me with this i would be extremely happy!

Thank you!!
 
How about a little work?

Insert a new column A (shifting everything to the right one column).

Put this in A2:
=b2&"|"&c2&"|"&text(d2,"mm/dd/yyyy")&"|"&text(e2,"mm/dd/yyyy")&"|"&f2
and drag down your data.

Now sort your data (all 7 columns) by this helper column.

Then do
Data|subtotals
to get your subtotals.

You can use the outlining symbols to the right to hide the details.
Now select the range
edit|goto|special|visible cells only
edit|copy

Off to a new worksheet
select A1
Edit|paste special|values.

Now separated column A into it's components.
Select column A
data|text to columns
delimited by |

and finish up by deleting column A.

=====
You may want to experiment with a pivottable, too.
 
Sami,

Did you find a solution for this with a macro? I am trying to do basically
the same thing.

Thanks,

Scott
 
I found the best success with the post that Dave Peterson put through.
It takes a while to set up but once its done it works like a treat.
(thanks Dave)
 
Back
Top