Tricky conditional sum in Scripting

[markgraham123]

Hi all,

I'm working on a tricky requirement where:

I have a Primary number and secondary number.

Every number will have a Secondary number.

But for few Primary ID, there exists 2 secondary IDs which resembles that both are same but its corresponding data has to be summed up.

Data:

Primary ID,	Secondary ID,	A to B,	B to C,	C to D,	Final Transit
12345,	55677,	2	1	1
12345,	55677P,	1			2
12345,	27865,	1	3	6	7
10908,	109566,	3	3	4
10908,	109566P,				1
10908,	89678,	2	2	3	1
100876,	1000555,	1	6	6	0
100876,	1000555X	0	0	0	6
145677,	167856,	2	2	9

Please find the application attached

Requirement: For Primary ID 12345, we have Secondary IDs 55677 and 55677P.

For 55677, A to B is 2, B to C is 1, C to D is 1

For 55677P, A to B is 1 and Final Transit is 2.

I'm looking for an output where the corresponding values should be summed up as the Secondary IDs points to same.

OUTPUT:

Primary ID,	Secondary ID,	A to B,	B to C,	C to D,	Final Transit
12345,	55677,	3	1	1	3
12345,	27865,	1	3	6	7
10908,	109566,	3	3	4	1
10908,	89678,	2	2	3	1
100876,	1000555,	1	6	6	6
145677,	167856,	2	2	9	0

For 55677,

A to B should be 3

B to C should be 1

C to D should be 1 and

Final Transit should be 2.

Any help is highly appreciated. Help Please !!

Message was edited by: Mark Graham

[markgraham123]

‌THanq very much for your tim and patience my friend.

i Really appreciate it

I learnt good concepts from u .

you were nailing it.