Macro to combine data under 3rd and 4th Column if condn satisfy for 1st , 2nd column

[shansakhi]

Hello Everybody,

Good Morning!!

I am looking for a macro which will combine the data available in 3rd and 4th Column if conditions satisfied for 1st and 2nd Column.

<tbody>
</tbody>

Below Input and Output data for your reference..

INPUT
KM	Leg	Org	Dest
2,019,769,184	1	BOM	DEL
2,019,769,184	2	DEL	BLR
2,019,770,095	1	BOM	MAA
2,019,770,675	2	MAA	DEL
2,019,770,213	1	SXR	IXJ
2,019,770,213	2	IXJ	DEL
2,019,770,213	3	DEL	BOM
2,019,770,213	4	BOM	SXR
2,019,770,816	2	DEL	MAA
2,019,770,816	3	MAA	IXC
OUTPUT					Rule
KM	Leg	Org	Dest	OUTPUT
2,019,769,184	1	BOM	DEL	BOM - DEL - BLR	KM are same for Leg 1 & 2 hence Org and Dest combined in column E
2,019,769,184	2	DEL	BLR
2,019,770,095	1	BOM	MAA	BOM - MAA	KM is only one and not matching with any one hence Org and Dest taken as it is in column E
2,019,770,675	2	MAA	DEL	MAA - DEL	KM is only one and not matching with any one hence Org and Dest taken as it is in column E
2,019,770,213	1	SXR	IXJ	SXR - IXJ - DEL - BOM - SXR	KM are same for Leg 1, 2, 3 & 4 hence Org and Dest combined in column E
2,019,770,213	2	IXJ	DEL
2,019,770,213	3	DEL	BOM
2,019,770,213	4	BOM	SXR
2,019,770,816	2	DEL	MAA	DEL - MAA - IXC	KM are same for Leg 2 & 3 hence Org and Dest combined in column E
2,019,770,816	3	MAA	IXC
2,019,770,800	6	KLH	BOM	KLH - BOM	KM is only one and not matching with any one hence Org and Dest taken as it is in column E

<tbody>
</tbody>

Thank You,

Shan

 

[MickG]

Try this:-
NB:- Numbers in column"A" assumed to start "A2".

[COLOR="Navy"]Sub[/COLOR] MG18Dec06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    Dic.Add Dn.Value, Array(Dn.Offset(, 2).Value & "-" & Dn.Offset(, 3).Value, Dn)
    Dn.Offset(, 4).Value = Dn.Offset(, 2).Value & "-" & Dn.Offset(, 3).Value
[COLOR="Navy"]Else[/COLOR]
Q = Dic(Dn.Value)
[COLOR="Navy"]For[/COLOR] n = 2 To 3
    [COLOR="Navy"]If[/COLOR] Not InStr(Q(0), Dn.Offset(, n).Value) > 0 [COLOR="Navy"]Then[/COLOR]
        Q(0) = Q(0) & "-" & Dn.Offset(, n).Value
        Q(1).Offset(, 4) = Q(0)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Dic(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[shansakhi]

Thank you Sir for your reply its working fine but few challenges I am facing in code..

If I have same KM for two different passengers for same Org and Dest code is combining Org and Dest only for one.

KM	Leg	Org	Dest
2,019,770,816	1	DEL	MAA	DEL-MAA
2,019,770,816	1	DEL	MAA

<colgroup><col width="121" span="5" style="width:91pt"> </colgroup><tbody>
</tbody>

It should pickup as below

KM	Leg	Org	Dest
2,019,770,816	1	DEL	MAA	DEL-MAA
2,019,770,816	1	DEL	MAA	DEL-MAA

<colgroup><col width="121" span="5" style="width:91pt"> </colgroup><tbody>
</tbody>

I think code is not considering Leg no. it is considering only KM....

I need a code which will consider both KM and Leg ...


Thank you

Regards,
Shan

 

[MickG]

What would the result of this look like:-

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A)     [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR]
1.      KM          Leg     Org     Dest   
2.      2019770816  2       DEL     MAA    
3.      2019770816  3       MAA     IXC    
4.      2019770816  1       DEL     MAA    
5.      2019770816  1       DEL     MAA

Regards Mick

 

[shansakhi]

DEL-MAA--IXC

DEL-MAA

DEL-MAA

The Leg No and KM will always be in Sorting order from Lowest to highest..

 

[shansakhi]

KM	Leg	Org	Dest
2,019,770,816	2	DEL	MAA	DEL-MAA-IXC
2,019,770,816	3	MAA	IXC
2,019,770,816	1	DEL	MAA	DEL-MAA
2,019,770,816	1	DEL	MAA	DEL-MAA

<colgroup><col width="121" span="5" style="width:91pt"> </colgroup><tbody>
</tbody>

The Leg No and KM will always be in Sorting order from Lowest to highest..

 

[MickG]

Would this then be the result if Key 1 had come before Key 2, or something else. ????

KM	Leg	Org	Dest
2,019,770,816	1	DEL	MAA	DEL-MAA-IXC
2,019,770,816	1	DEL	MAA	DEL-MAA-IXC
2,019,770,816	2	DEL	MAA
2,019,770,816	3	MAA	IXC

<tbody>
</tbody>

 

[shansakhi]

Sir ...I dont think so I will face such scenario..