Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 list and extract continous range
Hello,
This is my first post. I have two list of data and want to extract continous range. eg. 1 2 2 3 5 6 6 7 8 10 I want continous range like 1 3 5 7 8 10 I have to handle large data. Either formula or query is required. Thanks, Panneer selvam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 list and extract continous range
I understand the 1-3 and possibly the 5-7, but how is 8 10 continuous ?
Also it is not 5-8 as continuous ? Or are there other rules ? NickHK "Tamil" wrote in message oups.com... Hello, This is my first post. I have two list of data and want to extract continous range. eg. 1 2 2 3 5 6 6 7 8 10 I want continous range like 1 3 5 7 8 10 I have to handle large data. Either formula or query is required. Thanks, Panneer selvam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 list and extract continous range
Panneer:
Is this what you want? Option Explicit Sub makelist() Dim rSel As Range Dim x1, x2 Dim lRowStart As Long, lRowEnd As Long Dim lRow As Long, lRowAns As Long Dim lCol1 As Long, lCol2 As Long Set rSel = Selection lCol1 = rSel.Column lCol2 = lCol1 + rSel.Columns.Count - 1 lRowStart = rSel.Row lRowEnd = lRowStart + rSel.Rows.Count - 1 lRowAns = lRowEnd + 3 x1 = Cells(lRowStart, lCol1) x2 = Cells(lRowStart, lCol2) For lRow = lRowStart + 1 To lRowEnd Step 1 If x2 < Cells(lRow, lCol1) Then Cells(lRowAns, lCol1) = x1 Cells(lRowAns, lCol2) = x2 lRowAns = lRowAns + 1 x1 = Cells(lRow, lCol1) x2 = Cells(lRow, lCol2) Else x2 = Cells(lRow, lCol2) End If Next lRow Cells(lRowAns, lCol1) = x1 Cells(lRowAns, lCol2) = x2 End Sub eg. 1 2 2 3 5 6 6 7 8 10 I want continous range like 1 3 5 7 8 10 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Tamil" wrote: Hello, This is my first post. I have two list of data and want to extract continous range. eg. 1 2 2 3 5 6 6 7 8 10 I want continous range like 1 3 5 7 8 10 I have to handle large data. Either formula or query is required. Thanks, Panneer selvam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 list and extract continous range
Fantastic Mr.Martin,
Thanks a lot. This is what I required. You saved much time for my routine work. Hats off to Google group. - Panneer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract & compare data on different worksheets | Excel Discussion (Misc queries) | |||
Compare and Extract | Excel Worksheet Functions | |||
compare and extract using a program. i really need help. | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming |