combine same values three columns

[cnu_753]

Hi,
Actually I am working with excel since 5yrs but very new to <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> and very interested to learn also. Please find the following post

DATA 1 2 LHS 2 3 LHS 3 4 RHS 4 5 RHS 5 6 LHS <tbody> </tbody>

<tbody>
</tbody>

and my result will be like this

RESULT
1	3	LHS
3	5	RHS
5	6	LHS

<tbody>
</tbody>

Kindly give <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> for this.
Thanking you

 

[DushiPunda]

Assuming the sample data you provided starts in A1 and ends in C5...

I'm assuming what you want is...for each group of Column C values, you want the end result to be
1. the minimum value from Column A & Column B in the same group
2. the maximum value from Column A & Column B in the same group
3. the corresponding value from Column C

is that correct?

Where do you want the result to be? Here's a macro I created that accomplishes this:

Sub test()
Dim i As Integer, x As Integer, y As Integer

For y = 1 To 5
    x = x + 1
    
    Do
        i = i + 1
    Loop Until Worksheets("Sheet1").Range("C" & i).Value <> Worksheets("Sheet1").Range("C" & i + 1).Value
    
    Worksheets("Sheet1").Range("E" & x).Value = Application.WorksheetFunction.Min(Worksheets("Sheet1").Range("A" & y & ":B" & i))
    Worksheets("Sheet1").Range("F" & x).Value = Application.WorksheetFunction.Max(Worksheets("Sheet1").Range("A" & y & ":B" & i))
    Worksheets("Sheet1").Range("G" & x).Value = Worksheets("Sheet1").Range("C" & i).Value
    
    y = i
Next y
End Sub

There is likely a better (easier, prettier) way to do this, but with your sample data, the results I received were the results you provided.

Currently, the macro is putting the results in Columns E-G, but that can be adjusted according to your needs. Also note that it is currently only looping through rows 1-5 (y = 1 To 5)...so if you'll need to adjust that to suit your needs as well. If your range of data is dynamic, you'll need to include some lines to find the last row that has data; I can help you with that if needed.

 

[cnu_753]

Hi
what ever u said is correct, my data is dynamic and it wont be as continues numbers as i sent to you

66+600	67+300	LHS
67+300	67+600	LHS
67+600	67+980	LHS
67+980	68+230	LHS
68+230	68+760	LHS
68+760	68+900	LHS
68+900	69+240	LHS
69+240	69+500	LHS
69+500	70+000	LHS
70+000	70+500	LHS
70+750	71+400	LHS
71+400	71+800	LHS
71+800	72+000	LHS
72+000	72+280	LHS
72+280	72+580	LHS
72+580	72+780	LHS
72+780	72+980	LHS
72+980	73+080	LHS
73+080	73+170	LHS
73+170	73+260	LHS
73+260	73+400	LHS
73+400	73+600	LHS
73+600	74+000	LHS
74+000	74+220	LHS
74+220	74+280	LHS
76+510	76+620	LHS
76+620	76+920	LHS
76+920	77+190	LHS
77+190	77+400	LHS
77+400	77+590	LHS
77+590	78+080	LHS
78+080	78+400	LHS
78+400	78+730	LHS
78+730	78+850	LHS
78+850	79+250	LHS
79+250	79+460	LHS
79+460	79+780	LHS
79+780	80+100	LHS
80+100	80+460	LHS
80+460	80+760	LHS
80+760	80+960	LHS
80+960	81+210	LHS
81+210	81+460	LHS
81+460	81+920	LHS
81+920	82+260	LHS
82+260	82+750	LHS
85+260	86+060	LHS
86+060	86+820	LHS
86+980	88+000	LHS
66+600	67+450	LHS
67+450	67+680	LHS
67+680	68+030	LHS
68+030	68+450	LHS
68+450	68+900	LHS
68+900	69+000	LHS
69+000	69+500	LHS
69+500	69+820	LHS
69+820	70+500	LHS
70+750	71+400	LHS
71+400	72+000	LHS
72+000	72+500	LHS
72+500	72+800	LHS
72+800	73+290	LHS
73+290	73+500	LHS
73+500	73+900	LHS
73+900	74+220	LHS
74+220	74+280	LHS
76+510	76+620	LHS
76+620	76+950	LHS
76+950	77+400	LHS
77+400	77+590	LHS
77+590	78+080	LHS
78+080	78+400	LHS
78+400	78+720	LHS
78+720	78+870	LHS
78+870	79+130	LHS
79+130	79+420	LHS
79+420	79+790	LHS
79+790	80+120	LHS
80+120	80+490	LHS
80+490	80+820	LHS
80+820	81+070	LHS
81+070	81+500	LHS
81+500	81+940	LHS
81+940	82+260	LHS
82+260	82+750	LHS
85+260	86+060	LHS
86+060	86+820	LHS
86+980	88+000	LHS
66+600	67+550	RHS
67+550	68+500	RHS
68+500	69+400	RHS
69+400	70+500	RHS
70+750	71+400	RHS
71+400	72+000	RHS
72+000	72+500	RHS
72+500	73+000	RHS
73+000	73+500	RHS
73+500	74+000	RHS
74+000	74+250	RHS
74+250	74+280	RHS
76+510	76+620	RHS
76+620	76+890	RHS
76+890	77+110	RHS
77+110	77+400	RHS
77+400	77+580	RHS
77+580	78+000	RHS
78+000	78+210	RHS
78+210	78+400	RHS
78+400	78+720	RHS
78+720	78+870	RHS
78+870	79+400	RHS
79+400	79+600	RHS
79+600	79+940	RHS
79+940	80+000	RHS
80+000	80+340	RHS
80+340	80+470	RHS
80+470	80+760	RHS
80+760	80+880	RHS
80+880	80+940	RHS
80+940	81+080	RHS
81+080	81+200	RHS
81+200	81+400	RHS
81+400	81+850	RHS
81+850	81+950	RHS
81+950	82+650	RHS
82+650	82+750	RHS
85+260	86+060	RHS
86+060	86+820	RHS
86+980	88+000	RHS
66+600	67+550	RHS
67+550	68+500	RHS
68+500	69+400	RHS
69+400	70+500	RHS
70+750	71+400	RHS
71+400	72+000	RHS
72+000	72+500	RHS
72+500	73+000	RHS
73+000	73+500	RHS
73+500	74+000	RHS
74+000	74+250	RHS
74+250	74+280	RHS
76+510	76+620	RHS
76+620	76+800	RHS
76+800	76+920	RHS
76+920	77+400	RHS
77+400	77+600	RHS
77+600	77+900	RHS
77+900	78+100	RHS
78+100	78+400	RHS
78+400	78+720	RHS
78+720	78+870	RHS
78+870	79+370	RHS
79+370	79+600	RHS
79+600	79+700	RHS
79+700	80+270	RHS
80+270	80+380	RHS
80+380	80+780	RHS
80+780	81+180	RHS
81+180	81+480	RHS
81+480	81+720	RHS
81+720	82+200	RHS
82+200	82+750	RHS
85+260	86+060	RHS
86+060	86+820	RHS
86+980	88+000	RHS

<colgroup><col><col><col></colgroup><tbody>
</tbody>

This is my data sir,

In some lines from value and to value wont be equal.
and my result for the above will be as follows

66600	70500	LHS
70750	74280	LHS
76510	82750	LHS
85260	85820	LHS
85980	88000	LHS
66600	70500	RHS
71400	74280	RHS
76510	82750	RHS
85260	85820	RHS
85980	88000	RHS

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

 

[cnu_753]

if i increase "Y=1 to 5" value to "Y=1 to 115" its not working. kindly suggest me.

 

[DushiPunda]

What are the + signs?

The code I provided won't work at all for your actual data...

The first line of your result is 66600/70500 LHS...why is 70500 the result?

If I'm looking at this correctly, the first group isn't actually defined by the value in the third column, but it's actually defined by the second column not being the same as the first column in the next row? Like so:

70+000	70+500	LHS
70+750	71+400	LHS

<tbody>
</tbody>

Is that right?

 

[cnu_753]

ya sir

 

[DushiPunda]

1. You didn't have the + signs in your results...is that intentional?
2. Looking at your data, it looks like you pasted all of the RHS values twice...is that intentional?

66+600	70+500	LHS
70+750	74+280	LHS
76+510	82+750	LHS
85+260	86+820	LHS
86+980	88+000	LHS
66+600	70+500	RHS
70+750	74+280	RHS
76+510	82+750	RHS
85+260	86+820	RHS
86+980	88+000	RHS
66+600	70+500	RHS
70+750	74+280	RHS
76+510	82+750	RHS
85+260	86+820	RHS
86+980	88+000	RHS

<tbody>
</tbody>

I have a new macro and this was the result of it...As you can see, the blue and orange is a repeat...did you accidentally paste that section twice or is that actually how your data looks?

Here's the macro I have:

Sub test()
Dim lRow As Integer
Dim i As Integer, x As Integer, y As Integer

lRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row

For y = 1 To lRow
    x = x + 1
    
    Do
        i = i + 1
    Loop Until Worksheets("Sheet1").Range("C" & i).Value <> Worksheets("Sheet1").Range("C" & i + 1).Value Or Worksheets("Sheet1").Range("B" & i).Value <> Worksheets("Sheet1").Range("A" & i + 1).Value
    
    Worksheets("Sheet1").Range("E" & x).Value = Worksheets("Sheet1").Range("A" & y).Value
    Worksheets("Sheet1").Range("F" & x).Value = Worksheets("Sheet1").Range("B" & i).Value
    Worksheets("Sheet1").Range("G" & x).Value = Worksheets("Sheet1").Range("C" & i).Value
    
    y = i
Next y
End Sub

If you want to remove the + signs from your results, you can use this modified code:

Sub test()
Dim lRow As Integer
Dim i As Integer, x As Integer, y As Integer

lRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row

For y = 1 To lRow
    x = x + 1
    
    Do
        i = i + 1
    Loop Until Worksheets("Sheet1").Range("C" & i).Value <> Worksheets("Sheet1").Range("C" & i + 1).Value Or Worksheets("Sheet1").Range("B" & i).Value <> Worksheets("Sheet1").Range("A" & i + 1).Value
    
    Worksheets("Sheet1").Range("E" & x).Value = Replace(Worksheets("Sheet1").Range("A" & y).Value, "+", "")
    Worksheets("Sheet1").Range("F" & x).Value = Replace(Worksheets("Sheet1").Range("B" & i).Value, "+", "")
    Worksheets("Sheet1").Range("G" & x).Value = Replace(Worksheets("Sheet1").Range("C" & i).Value, "+", "")
    
    y = i
Next y
End Sub

If the data was not accidentally pasted twice and that is what it actually looks like, then this will need to be modified again to account for that. Let me know.

 

[MickG]

Try this:-
Results start "F1"

[COLOR="Navy"]Sub[/COLOR] MG01Jul57
[COLOR="Navy"]Dim[/COLOR] Ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray     [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic     [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] K       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 
Ray = Range("C1").CurrentRegion.Resize(, 3)
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
       [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
            [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Ray(n, 3)) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
              [COLOR="Navy"]For[/COLOR] Ac = 1 To 2
                [COLOR="Navy"]If[/COLOR] Not Dic(Ray(n, 3)).Exists(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
                    Dic(Ray(n, 3)).Add (Ray(n, Ac)), Ac
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]If[/COLOR] Not Dic(Ray(n, 3)).Item(Ray(n, Ac)) = Ac [COLOR="Navy"]Then[/COLOR]
                        Dic(Ray(n, 3)).Remove (Ray(n, Ac))
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
             [COLOR="Navy"]Next[/COLOR] Ac
      [COLOR="Navy"]Next[/COLOR] n
   
    
c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
       Ac = 0
       [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(K)
            Ac = Ac + 1
            Cells(c, "H") = K
            Cells(c, "E").Offset(, Ac) = Replace(p, "+", "")
                [COLOR="Navy"]If[/COLOR] Ac Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
                    c = c + 1: Ac = 0
                [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[cnu_753]

Thanq very much its working perfectly.........

 

[cnu_753]

Thanq very much working fine.......