combine same values three columns

C

cnu_753

New Member
Joined
Apr 30, 2017
Messages
10
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

[TABLE="class: cms_table, width: 195"]
<tbody>[TR]
[TD="colspan: 3"][TABLE="class: cms_table, width: 195"]
<tbody>[TR]
[TD="colspan: 3"]DATA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]LHS[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and my result will be like this

[TABLE="class: cms_table, width: 195"]
<tbody>[TR]
[TD="colspan: 3"]RESULT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]LHS[/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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:

Code: 
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.
 
Last edited:
Upvote 0
Hi
what ever u said is correct, my data is dynamic and it wont be as continues numbers as i sent to you
[TABLE="width: 177"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]66+600[/TD]
[TD]67+300[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]67+300[/TD]
[TD]67+600[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]67+600[/TD]
[TD]67+980[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]67+980[/TD]
[TD]68+230[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+230[/TD]
[TD]68+760[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+760[/TD]
[TD]68+900[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+900[/TD]
[TD]69+240[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]69+240[/TD]
[TD]69+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]69+500[/TD]
[TD]70+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]70+000[/TD]
[TD]70+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]71+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]71+400[/TD]
[TD]71+800[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]71+800[/TD]
[TD]72+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+000[/TD]
[TD]72+280[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+280[/TD]
[TD]72+580[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+580[/TD]
[TD]72+780[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+780[/TD]
[TD]72+980[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+980[/TD]
[TD]73+080[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+080[/TD]
[TD]73+170[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+170[/TD]
[TD]73+260[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+260[/TD]
[TD]73+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+400[/TD]
[TD]73+600[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+600[/TD]
[TD]74+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]74+000[/TD]
[TD]74+220[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]74+220[/TD]
[TD]74+280[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]76+620[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+620[/TD]
[TD]76+920[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+920[/TD]
[TD]77+190[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]77+190[/TD]
[TD]77+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]77+400[/TD]
[TD]77+590[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]77+590[/TD]
[TD]78+080[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+080[/TD]
[TD]78+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+400[/TD]
[TD]78+730[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+730[/TD]
[TD]78+850[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+850[/TD]
[TD]79+250[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+250[/TD]
[TD]79+460[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+460[/TD]
[TD]79+780[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+780[/TD]
[TD]80+100[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+100[/TD]
[TD]80+460[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+460[/TD]
[TD]80+760[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+760[/TD]
[TD]80+960[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+960[/TD]
[TD]81+210[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+210[/TD]
[TD]81+460[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+460[/TD]
[TD]81+920[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+920[/TD]
[TD]82+260[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]82+260[/TD]
[TD]82+750[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+060[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]86+060[/TD]
[TD]86+820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]66+600[/TD]
[TD]67+450[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]67+450[/TD]
[TD]67+680[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]67+680[/TD]
[TD]68+030[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+030[/TD]
[TD]68+450[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+450[/TD]
[TD]68+900[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]68+900[/TD]
[TD]69+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]69+000[/TD]
[TD]69+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]69+500[/TD]
[TD]69+820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]69+820[/TD]
[TD]70+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]71+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]71+400[/TD]
[TD]72+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+000[/TD]
[TD]72+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+500[/TD]
[TD]72+800[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]72+800[/TD]
[TD]73+290[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+290[/TD]
[TD]73+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+500[/TD]
[TD]73+900[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]73+900[/TD]
[TD]74+220[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]74+220[/TD]
[TD]74+280[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]76+620[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+620[/TD]
[TD]76+950[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+950[/TD]
[TD]77+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]77+400[/TD]
[TD]77+590[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]77+590[/TD]
[TD]78+080[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+080[/TD]
[TD]78+400[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+400[/TD]
[TD]78+720[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+720[/TD]
[TD]78+870[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]78+870[/TD]
[TD]79+130[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+130[/TD]
[TD]79+420[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+420[/TD]
[TD]79+790[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]79+790[/TD]
[TD]80+120[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+120[/TD]
[TD]80+490[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+490[/TD]
[TD]80+820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]80+820[/TD]
[TD]81+070[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+070[/TD]
[TD]81+500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+500[/TD]
[TD]81+940[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]81+940[/TD]
[TD]82+260[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]82+260[/TD]
[TD]82+750[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+060[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]86+060[/TD]
[TD]86+820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]66+600[/TD]
[TD]67+550[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]67+550[/TD]
[TD]68+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]68+500[/TD]
[TD]69+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]69+400[/TD]
[TD]70+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]71+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]71+400[/TD]
[TD]72+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]72+000[/TD]
[TD]72+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]72+500[/TD]
[TD]73+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]73+000[/TD]
[TD]73+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]73+500[/TD]
[TD]74+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]74+000[/TD]
[TD]74+250[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]74+250[/TD]
[TD]74+280[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]76+620[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+620[/TD]
[TD]76+890[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+890[/TD]
[TD]77+110[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+110[/TD]
[TD]77+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+400[/TD]
[TD]77+580[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+580[/TD]
[TD]78+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+000[/TD]
[TD]78+210[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+210[/TD]
[TD]78+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+400[/TD]
[TD]78+720[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+720[/TD]
[TD]78+870[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+870[/TD]
[TD]79+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+400[/TD]
[TD]79+600[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+600[/TD]
[TD]79+940[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+940[/TD]
[TD]80+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+000[/TD]
[TD]80+340[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+340[/TD]
[TD]80+470[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+470[/TD]
[TD]80+760[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+760[/TD]
[TD]80+880[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+880[/TD]
[TD]80+940[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+940[/TD]
[TD]81+080[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+080[/TD]
[TD]81+200[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+200[/TD]
[TD]81+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+400[/TD]
[TD]81+850[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+850[/TD]
[TD]81+950[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+950[/TD]
[TD]82+650[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]82+650[/TD]
[TD]82+750[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+060[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+060[/TD]
[TD]86+820[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]66+600[/TD]
[TD]67+550[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]67+550[/TD]
[TD]68+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]68+500[/TD]
[TD]69+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]69+400[/TD]
[TD]70+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]71+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]71+400[/TD]
[TD]72+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]72+000[/TD]
[TD]72+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]72+500[/TD]
[TD]73+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]73+000[/TD]
[TD]73+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]73+500[/TD]
[TD]74+000[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]74+000[/TD]
[TD]74+250[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]74+250[/TD]
[TD]74+280[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]76+620[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+620[/TD]
[TD]76+800[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+800[/TD]
[TD]76+920[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+920[/TD]
[TD]77+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+400[/TD]
[TD]77+600[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+600[/TD]
[TD]77+900[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]77+900[/TD]
[TD]78+100[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+100[/TD]
[TD]78+400[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+400[/TD]
[TD]78+720[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+720[/TD]
[TD]78+870[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]78+870[/TD]
[TD]79+370[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+370[/TD]
[TD]79+600[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+600[/TD]
[TD]79+700[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]79+700[/TD]
[TD]80+270[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+270[/TD]
[TD]80+380[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+380[/TD]
[TD]80+780[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]80+780[/TD]
[TD]81+180[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+180[/TD]
[TD]81+480[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+480[/TD]
[TD]81+720[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]81+720[/TD]
[TD]82+200[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]82+200[/TD]
[TD]82+750[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+060[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+060[/TD]
[TD]86+820[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]RHS[/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 189"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]66600[/TD]
[TD]70500[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]70750[/TD]
[TD]74280[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76510[/TD]
[TD]82750[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]85260[/TD]
[TD]85820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]85980[/TD]
[TD]88000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]66600[/TD]
[TD]70500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]71400[/TD]
[TD]74280[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76510[/TD]
[TD]82750[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85260[/TD]
[TD]85820[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85980[/TD]
[TD]88000[/TD]
[TD]RHS[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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:


[TABLE="class: cms_table, width: 177"]
<tbody>[TR]
[TD]70+000
[/TD]
[TD]70+500
[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]70+750
[/TD]
[TD]71+400[/TD]
[TD]LHS
[/TD]
[/TR]
</tbody>[/TABLE]

Is that right?
 
Upvote 0
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?

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]66+600
[/TD]
[TD="width: 64"]70+500[/TD]
[TD="width: 64"]LHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]74+280[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]82+750[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+820[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]66+600
[/TD]
[TD]70+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]74+280[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]82+750[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+820
[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]RHS
[/TD]
[/TR]
[TR]
[TD]66+600
[/TD]
[TD]70+500[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]70+750[/TD]
[TD]74+280[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]76+510[/TD]
[TD]82+750[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]85+260[/TD]
[TD]86+820[/TD]
[TD]RHS[/TD]
[/TR]
[TR]
[TD]86+980[/TD]
[TD]88+000[/TD]
[TD]RHS[/TD]
[/TR]
</tbody>[/TABLE]

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:

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 = 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:

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.
 
Last edited:
Upvote 0
Try this:-
Results start "F1"
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
491
VBAEXCELNew
V
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
596
John_w
J
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
310
Minh Tuan VN
M
I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
85
Alex Blakenburg
A

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top