I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

Using your example data in Cells A1:F10, try this:

H1: Report
H2: Code
H3: Start
H4: Finish
H5: Distance

I1: 100
I2: B
I3: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

Does that give you something to work with?

***********
Regards,
Ron


"ryesworld" wrote:

I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

Yes, I can customize this formula to provide the total length for each
continuous code on each row, and then tweak it so that it only calulates the
distance on rows that have the Finish Codes (to prevent summing the lengths
twice).
You're the Excel Master. Brilliant! Thank You!

"Ron Coderre" wrote:

Using your example data in Cells A1:F10, try this:

H1: Report
H2: Code
H3: Start
H4: Finish
H5: Distance

I1: 100
I2: B
I3: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

Does that give you something to work with?

***********
Regards,
Ron


"ryesworld" wrote:

I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

I'm glad I got you pointed in the right direction.....
Thanks for the feedback

***********
Regards,
Ron


"ryesworld" wrote:

Yes, I can customize this formula to provide the total length for each
continuous code on each row, and then tweak it so that it only calulates the
distance on rows that have the Finish Codes (to prevent summing the lengths
twice).
You're the Excel Master. Brilliant! Thank You!

"Ron Coderre" wrote:

Using your example data in Cells A1:F10, try this:

H1: Report
H2: Code
H3: Start
H4: Finish
H5: Distance

I1: 100
I2: B
I3: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

Does that give you something to work with?

***********
Regards,
Ron


"ryesworld" wrote:

I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

Hi, I ran into a problem with the formulas, the Start and Finish Cells are
not always returning the right numbers. Below is the table I get with your
formulas, and the Start and Finish Cells for Report 200 are summing the wrong
values (E4:F7). The formula I used for these is shown below the table.
(Copied from E2 & E3)

A B C D E F
G
1 REPORT DIST CONT CODE START FINISH DISTANCE
2 100 1 S1 A 1 5 -
3 100 5 F1 A 1 5 4
4 200 1 S1 A 11 35 -
5 200 10 S2 A 11 35 -
6 200 15 F2 A 11 35 24
7 200 20 F1 A 11 35 24

Start:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000)

Any idea how to fix this?

Thanks,
Ryan

"Ron Coderre" wrote:

I'm glad I got you pointed in the right direction.....
Thanks for the feedback

***********
Regards,
Ron


"ryesworld" wrote:

Yes, I can customize this formula to provide the total length for each
continuous code on each row, and then tweak it so that it only calulates the
distance on rows that have the Finish Codes (to prevent summing the lengths
twice).
You're the Excel Master. Brilliant! Thank You!

"Ron Coderre" wrote:

Using your example data in Cells A1:F10, try this:

H1: Report
H2: Code
H3: Start
H4: Finish
H5: Distance

I1: 100
I2: B
I3: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

Does that give you something to work with?

***********
Regards,
Ron


"ryesworld" wrote:

I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

See if this fixes it:

Start should be:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000)

Finish should be:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000)


Does that make it right?

***********
Regards,
Ron


"ryesworld" wrote:

Hi, I ran into a problem with the formulas, the Start and Finish Cells are
not always returning the right numbers. Below is the table I get with your
formulas, and the Start and Finish Cells for Report 200 are summing the wrong
values (E4:F7). The formula I used for these is shown below the table.
(Copied from E2 & E3)

A B C D E F
G
1 REPORT DIST CONT CODE START FINISH DISTANCE
2 100 1 S1 A 1 5 -
3 100 5 F1 A 1 5 4
4 200 1 S1 A 11 35 -
5 200 10 S2 A 11 35 -
6 200 15 F2 A 11 35 24
7 200 20 F1 A 11 35 24

Start:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000)

Any idea how to fix this?

Thanks,
Ryan

"Ron Coderre" wrote:

I'm glad I got you pointed in the right direction.....
Thanks for the feedback

***********
Regards,
Ron


"ryesworld" wrote:

Yes, I can customize this formula to provide the total length for each
continuous code on each row, and then tweak it so that it only calulates the
distance on rows that have the Finish Codes (to prevent summing the lengths
twice).
You're the Excel Master. Brilliant! Thank You!

"Ron Coderre" wrote:

Using your example data in Cells A1:F10, try this:

H1: Report
H2: Code
H3: Start
H4: Finish
H5: Distance

I1: 100
I2: B
I3: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

Does that give you something to work with?

***********
Regards,
Ron


"ryesworld" wrote:

I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and
end of a code is indicated in the CONT. column. (Start of first code = S1,
end of first Code = F1, Start of second code = S2, end of second Code = F2,
etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start
& Finish Codes. Two conditions need to be met before the length (in DIST
column) is to be calculated, the Report numbers and the Codes must be the
same for both rows.

A B C D E F
1 S1 S2
2 REPORT DIST. CONT. CODE F1 F2
3 100 5 S1 B
4 100 10 S2 A
5 100 12 F1 B
6 100 14 C
7 100 16 F2 A
8 200 2 B
9 200 4 S1 B
10 200 10 F2 B

In the table above:
- Code B in report 100 would go for a distance of 7 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan