Hello,

This is me again. I will need your help again to design me macro which can
give me output as below.

I have huge excel file, which includes around 6000 lines. In column E I have
"TPDoc Number" & Column F I have "Shipment number" which are similar to TP
doc no most of the time.

In column N I have "Date/Time". I want you to design macro which can give me
difference between all dates for the same bill no... (I.e. How many days did
we took to complete task for specific bills) & please keep in mind there one
TP Doc number can have 4, 5, 6 and so on different lines. See beloe example :
Column E Column F Column N
Column O
TPDoc Number Shipment Number Status Date/Time Difference
526829737 526829737 20 08-11-07 03:10
526829737 526829737 20 08-12-12 19:10
526829737 526829737 20 08-12-15 15:26
526829737 526829737 20 08-12-15 15:36
526829737 526829737 20 08-12-16 10:33 -39
526829750 526829750 20 08-11-07 03:11
526829750 526829750 20 08-12-12 19:10
526829750 526829750 20 08-12-15 15:26
526829750 526829750 20 08-12-15 15:35
526829750 526829750 20 08-12-16 10:35 -39
526836030 526836030 20 08-12-09 14:54
526836030 526836030 20 08-12-12 11:09
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-22 11:03
526836030 526836030 20 09-01-07 16:17 -29

Please let me know if you need any further info. I can also send you example
file if you need.

Kam.

Assuming your data is all sorted by bill # like in your example, you don't
need a macro, could do this with a formula. In cell O2:

=IF(E2=E3,"",MAX(IF($E$2:$E$7000=E2,$N$2:$N$7000," "))-MIN(IF($E$2:$E$7000=E2,$N$2:$N$7000,"")))

Input this as an array (Use Ctrl+Shift+Enter to confirm). Then copy down as
needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kamlu" wrote:

Hello,

This is me again. I will need your help again to design me macro which can
give me output as below.

I have huge excel file, which includes around 6000 lines. In column E I have
"TPDoc Number" & Column F I have "Shipment number" which are similar to TP
doc no most of the time.

In column N I have "Date/Time". I want you to design macro which can give me
difference between all dates for the same bill no... (I.e. How many days did
we took to complete task for specific bills) & please keep in mind there one
TP Doc number can have 4, 5, 6 and so on different lines. See beloe example :
Column E Column F Column N
Column O
TPDoc Number Shipment Number Status Date/Time Difference
526829737 526829737 20 08-11-07 03:10
526829737 526829737 20 08-12-12 19:10
526829737 526829737 20 08-12-15 15:26
526829737 526829737 20 08-12-15 15:36
526829737 526829737 20 08-12-16 10:33 -39
526829750 526829750 20 08-11-07 03:11
526829750 526829750 20 08-12-12 19:10
526829750 526829750 20 08-12-15 15:26
526829750 526829750 20 08-12-15 15:35
526829750 526829750 20 08-12-16 10:35 -39
526836030 526836030 20 08-12-09 14:54
526836030 526836030 20 08-12-12 11:09
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-22 11:03
526836030 526836030 20 09-01-07 16:17 -29

Please let me know if you need any further info. I can also send you example
file if you need.

Kam.

Thank you very much for your help.

"Luke M" wrote:

Assuming your data is all sorted by bill # like in your example, you don't
need a macro, could do this with a formula. In cell O2:

=IF(E2=E3,"",MAX(IF($E$2:$E$7000=E2,$N$2:$N$7000," "))-MIN(IF($E$2:$E$7000=E2,$N$2:$N$7000,"")))

Input this as an array (Use Ctrl+Shift+Enter to confirm). Then copy down as
needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kamlu" wrote:

Hello,

This is me again. I will need your help again to design me macro which can
give me output as below.

I have huge excel file, which includes around 6000 lines. In column E I have
"TPDoc Number" & Column F I have "Shipment number" which are similar to TP
doc no most of the time.

In column N I have "Date/Time". I want you to design macro which can give me
difference between all dates for the same bill no... (I.e. How many days did
we took to complete task for specific bills) & please keep in mind there one
TP Doc number can have 4, 5, 6 and so on different lines. See beloe example :
Column E Column F Column N
Column O
TPDoc Number Shipment Number Status Date/Time Difference
526829737 526829737 20 08-11-07 03:10
526829737 526829737 20 08-12-12 19:10
526829737 526829737 20 08-12-15 15:26
526829737 526829737 20 08-12-15 15:36
526829737 526829737 20 08-12-16 10:33 -39
526829750 526829750 20 08-11-07 03:11
526829750 526829750 20 08-12-12 19:10
526829750 526829750 20 08-12-15 15:26
526829750 526829750 20 08-12-15 15:35
526829750 526829750 20 08-12-16 10:35 -39
526836030 526836030 20 08-12-09 14:54
526836030 526836030 20 08-12-12 11:09
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-12 19:10
526836030 526836030 20 08-12-22 11:03
526836030 526836030 20 09-01-07 16:17 -29

Please let me know if you need any further info. I can also send you example
file if you need.

Kam.