Create ranges from discrete numbers « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Create ranges from discrete numbers

(21 posts) (9 voices)

Started 9 months ago by KPJ
Latest reply from SirJB7

Tags:

No tags yet.

KPJ

Member
Joined: Jun '12
Posts: 305

Hi Experts,

This is a situation where I can avoid to process 50K lines and will process only few line if i get able to convert strings in "A" and "B" coloum like mentioned below:

Input

---"A"--------"B"------
ABC0001------ABC0001
ABC0002------ABC0002
ABC0003------ABC0003
ABC0005------ABC0005
ABC0006------ABC0006
ABC0009------ABC0009
ABC0011------ABC0011
ABC011P------ABC011P
ABC012P------ABC012P
ABC015P------ABC015P

Output

ABC0001------ABC0003
ABC0005------ABC0006
ABC0009------ABC0009
ABC0011------ABC0011
ABC011P------ABC012P
ABC015P------ABC015P

Regards

Posted 9 months ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,168

KPJSWT

What is the rule/s between the input and output ?
There doesn't appear to be any obvious rules

Posted 9 months ago #
KPJ

Member
Joined: Jun '12
Posts: 305

Hello Hui,

Rule is just to club the incremental values to make a range. if we look to the first three data, it will give us a range as shown below

ABC0001------ABC0001
ABC0002------ABC0002
ABC0003------ABC0003
---------------------
ABC0001-----ABC0003
---------------------

Surely if there is no adjacent value range will contain the only single entity like

ABC0009------ABC0009 will be return as it ABC0009------ABC0009 only however third type of data set it tricky and have incremental value behind the suffix like "P" and should return as

ABC011P------ABC011P
ABC012P------ABC012P
------------------------
ABC011P------ABC012P
------------------------

* in case all the three does not get fit in one solution, I hope to get the solution for first two data set and will do manual =Len operation to remove the character and will apply the solution.

Regards,

Posted 9 months ago #

Debraj Roy

Excel Ninja
Joined: Mar '12
Posts: 1,066

Hi KuldeePJainSWeTa,

Can you please run the below code and let us know if its working.. :)

Option Explicit

Sub CreateRange()
Dim i As Long, j As Long
Dim stRange As String, endRange As String
j = 2
stRange = Range("A2")
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Range("G2") = FormatedTxt(Range("A" & i))
    Range("g2").AutoFill [g2:g3]
    If Range("g3") <> FormatedTxt(Range("A" & i + 1)) Then
        endRange = Range("A" & i)
        Range("d" & j) = stRange
        Range("e" & j) = endRange
            stRange = Range("A" & i + 1)
        j = j + 1
    End If
Next i
[g2:g3].Clear
End Sub

Function FormatedTxt(txt As String)
Dim i As Long
    FormatedTxt = txt
    i = Len(txt)
    If i = 0 Then Exit Function
    Do While Not IsNumeric(Mid(txt, i, 1))
        FormatedTxt = Left(FormatedTxt, i - 1)
        i = i - 1
    Loop
End Function

Regards,
Deb

Posted 9 months ago #

KPJ

Member
Joined: Jun '12
Posts: 305

Hi Deb,

Thanks for taking an attempt. However it fails :(

Just imagine you having the data set like below. Now ABC0003 is not adjacent to its range values of ABC0001, ABC0002

--"A"--------"B"------
ABC0001------ABC0001
ABC0002------ABC0002
ABC0005------ABC0005
ABC0006------ABC0006
ABC0009------ABC0009
ABC0003------ABC0003
ABC0011------ABC0011
ABC011P------ABC011P
ABC012P------ABC012P
ABC015P------ABC015P

We need to think something by taking first value of "B2" and check with all others in "A" to to see whether it makes a valid range. if yes move ahead else paste the range and pick the next value form "B" coloum.

Regards,

Posted 9 months ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,356

Couple of questions:
1. Is col B always the same value as col A?
2. Is it okay if we sort the data?

Posted 9 months ago #
KPJ

Member
Joined: Jun '12
Posts: 305

Hi Luke,

Now this question is going to resolve.....After all it has been picked up by Luke... I have no restriction of sorting, trimming, or anything. The ultimate Aim is to reduce the number of lines.

Let me explain in whole. imagine i sold a 1000 products to a customer with serial number like ABC0001K to ABC0999K but in database these can not be process as lot of 1000 as subtracting the ABC0999K-ABC0001K do not gives 1000. Thus these are entered as

ABC0001K to ABC0001K
ABC0002K to ABC0002K and so on...

representing the qty as 1 each sold on same day against same invoice number and the issue comes if the products needed to trace back against a serial number, it will return the lot quantity a 1 while sold quantity was 1000. Also during data analysis you need to process each line while these 1000 lines can be converted in range.

No value is fixed in coloum "A" or "B". Let me upload a sample file as it will take lot more time to draft. please get sample file at http://sdrv.ms/TWaCtS

I have used color just to indicate variation in data-set nothing else.

Regards,

Posted 9 months ago #

Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,356

I admit I wasn't able to look at the uploaded workbook, so there may be opportunity to trim this down, but with the small data posted so far, I believe this will work. Before running the macro, will need to have the data sorted by col A. Note that this is 1 macro and 3 functions. Interesting comment, while working this code, learned that "log" in XL means base 10, but in VB it's base e. Nice, huh?

Sub CondenseLines()
Dim BeginRecord As Integer
Dim EndRecord As Integer
Dim LastRow As Integer
Dim MyWord As String

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
For i = 2 To LastRow
    BeginRecord = i
    EndRecord = BeginRecord
    MyWord = Cells(BeginRecord, "A").Value

    If MyWord = "" Then Exit For

    'Search for nect increment
    Do
        MyWord = IncValue(MyWord)
        EndRecord = EndRecord + 1
    Loop Until Cells(EndRecord, "A") <> MyWord

    Cells(BeginRecord, "B").Value = Cells(EndRecord - 1, "B").Value
    If EndRecord - BeginRecord > 1 Then 'need to delete rows
        Range(BeginRecord + 1 & ":" & EndRecord - 1).EntireRow.Delete
    End If
Next

Application.ScreenUpdating = True

End Sub

Function IncValue(r As String) As String
Dim MyNum As Integer

MyNum = LastNumber(r)
'Check to see if multiple instances exist
xCount = (Len(r) - Len(WorksheetFunction.Substitute(r, MyNum, ""))) / (Int(Log10(MyNum)) + 1)

'Replace only the last value to find the new value we want
If Int(Log10(MyNum + 1)) > Int(Log10(MyNum)) Then
    IncValue = WorksheetFunction.Substitute(r, "0" & MyNum, MyNum + 1, xCount)
Else
    IncValue = WorksheetFunction.Substitute(r, MyNum, MyNum + 1, xCount)
End If
End Function

Static Function Log10(X)
    Log10 = Log(X) / Log(10#)
End Function

Function LastNumber(s As String) As Double
Dim xNumber As String
Dim NoNumYet As Boolean
Dim TempValue As String

'No value input
If s = "" Then Exit Function
'No numbers in string
If Not s Like "*[0-9]*" Then Exit Function

NoNumYet = True

For i = Len(s) To 1 Step -1
    xNumber = Mid(s, i, 1)
    If xNumber Like "[0-9]" Then
        NoNumYet = False
        TempValue = xNumber & TempValue
    Else
        'Escape once we have the last number
        If Not NoNumYet Then Exit For
    End If
Next

LastNumber = TempValue * 1

End Function

Posted 9 months ago #

KPJ

Member
Joined: Jun '12
Posts: 305

Thanks Luke,

I knew it that you will do it....

Let me also admit that code went beyond my head....

I needed to just needed to defined variable as Long in place of integer. However there are few exception to handle,

1) Data to be sorted by coloum "A" but if we only sort coloum "A" and left the other...something goes wrong.
2)Code is deleting rows while I would prefer to get this new list in different coloum while keeping original data in place. may be starting from active cell or predefined cell.
3) Results after running this macro is as follows :

98989499--------98989999
ABC0001---------ABC0029---(This must be ABC0001K - ABC0029K). This mistake i even left in my sample workbook
ABC5000---------ABC6000
LUK98989--------LUK98990

Regards,

Posted 9 months ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,894

Hi, KPJSWT!

If you want to give a look to an only-formula solution here's the link:
https://dl.dropbox.com/u/60558749/Create%20ranges%20from%20discrete%20numbers%20%28for%20KPJSWT%20at%20chandoo.org%29.xlsx

Condition: input range (A column) sorted ascendently and helper columns can be used

Question: what's column B for?

Assumption: all cells in input range are of length up to 7 (if more, adjust columns C:J as required, length+1)

Colors:
no color, input and output ranges
light orange, indicator for number or string for each char in input cell
light blue, indicators for 1st number and 2nd string (if exists, as for last char)
light violet, input dissection
light green, sequence analyzer

Just advise if any issue.

Regards!

EDITED: (file uploaded again, column R formula)

Posted 9 months ago #

mercatog

Member
Joined: Aug '12
Posts: 121

Hi everybody
Here a proposition (to adapte to codename of your sheets)

Sub Traiter()
Const Dest = "G1"                                  'Where write result
Dim LastLig As Long, i As Long, k As Long, io As Long
Dim StrIni As String, StrFin As String
Dim Deb As Boolean
Dim Tb

Application.ScreenUpdating = False
'Initial Datas are in worksheet Sheet1 at columns A & B
With Sheet1
    LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A1:B" & LastLig).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
    Tb = .Range("A2:B" & LastLig + 1)
End With
ReDim Res(1 To 2, 1 To 1)
io = 1
For i = 1 To LastLig - 1
    StrFin = Txt(Tb(i + 1, 1))
    StrIni = Txt(Tb(i, 1))
    If StrFin = StrIni And Num(Tb(i + 1, 1), StrFin) = Num(Tb(i, 1), StrIni) + 1 Then
        If Deb Then io = i
        Deb = False
    Else
        k = k + 1
        ReDim Preserve Res(1 To 2, 1 To k)
        Res(1, k) = Tb(io, 1)
        Res(2, k) = Tb(i, 2)
        Deb = True
        io = i + 1
    End If
Next i
'Result wrote in worksheet Sheet2 at the cell DEST (cf Constant definition)
If k > 0 Then Sheet2.Range(Dest).Resize(k, 2) = Application.Transpose(Res)
End Sub

Private Function Txt(ByVal Str As String) As String
Dim Rg As Object

Set Rg = CreateObject("VBscript.RegExp")
With Rg
    .Pattern = "(\d)(.*)"
    .Global = True
    Txt = .Replace(Str, "")
End With
Set Rg = Nothing
End Function

Private Function Num(ByVal Str As String, Pref As String) As Double

If Str <> "" Then Num = Val(Replace(Str, Pref, ""))
End Function

Posted 9 months ago #

SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,894

Hi, KPJSWT!

For those with CASFFML issue (can't access shared files from my location, copyrighted by Luke M :P), here are the formulas:

Column A:B : your input data

Column C:J :
=SI(Y(EXTRAE($A2;COLUMNA()-2;1)>="0";EXTRAE($A2;COLUMNA()-2;1)<="9");"n";"s") -----> in english: =IF(AND(MID($A2,COLUMN()-2,1)>="0",MID($A2,COLUMN()-2,1)<="9"),"n","s")

Column K: indicator for number or string for each char in input cell (1st no. pos)
=COINCIDIR("n";C2:I2;0) -----> in english: =MATCH("n",C2:I2,0)

Column L: same as K (2nd str pos)
=COINCIDIR("s";INDIRECTO(DIRECCION(FILA();COINCIDIR("n";C2:I2;0)+2;4;1)&":J"&FILA());0)+K2-1 -----> =MATCH("s",INDIRECT(ADDRESS(ROW(),MATCH("n",C2:I2,0)+2,4,1)&":J"&ROW()),0)+K2-1

Columns M:O : input dissection (1st substring, number, 2nd substring)
=IZQUIERDA($A2;K2-1) -----> in english: =LEFT($A2,K2-1)
=VALOR(EXTRAE($A2;K2;L2-K2)) -----> in english: =VALUE(MID($A2,K2,L2-K2))
=DERECHA($A2;LARGO(A2)-L2+1) -----> in english: =RIGHT($A2,LEN(A2)-L2+1)

Column P: sequence analyzer (1st str change)
=SI(M2=M3;"";"X") -----> in english: =IF(M2=M3,"","X")

Column Q: sequence analyzer (number seq)
=SI(N3=N2+1;"";"X") -----> in english: =IF(N3=N2+1,"","X")

Column R: sequence analyzer (1st seq no.)
=SI(Y(ESNUMERO(R1);Q1="");R1;N2) -----> in english: =IF(AND(ISNUMBER(R1),Q1=""),R1,N2)

Column S: sequence analyzer (last seq no.)
=N2 -----> in english: =N2

Column T: sequence analyzer (range order)
=SUMA(T1)+SI(O($P2<>"";$Q2<>"");1;0) -----> in english: =SUM(T1)+IF(OR($P2<>"",$Q2<>""),1,0)

Column U:V : sequence analyzer (range from, range to)
=SI(O($P2<>"";$Q2<>"");$T2&"_"&$M2&TEXTO(R2;REPETIR("0";CONTAR.SI($C2:$J2;"n")))&$O2;"") -----> in english: =IF(OR($P2<>"",$Q2<>""),$T2&"_"&$M2&TEXT(R2,REPT("0",COUNTIF($C2:$J2,"n")))&$O2,"")

Columns W:X : output range (Range From, Range To)
=SI.ERROR(DERECHA(INDICE(U:U;COINCIDIR(FILA()-1;$T:$T;0));LARGO(INDICE(U:U;COINCIDIR(FILA()-1;$T:$T;0)))-ENCONTRAR("_";INDICE(U:U;COINCIDIR(FILA()-1;$T:$T;0))));"") -----> in english: =IFERROR(RIGHT(INDEX(U:U,MATCH(ROW()-1,$T:$T,0)),LEN(INDEX(U:U,MATCH(ROW()-1,$T:$T,0)))-FIND("_",INDEX(U:U,MATCH(ROW()-1,$T:$T,0)))),"")

Regards!

EDITED (column R formula)

Posted 9 months ago #
KPJ

Member
Joined: Jun '12
Posts: 305

Hi all,

I am thrilled by responses & will get a chance to evaluate these against my requirement as i reach back to work. Today i am off to work as today my baby is not felling well.

Regards,

Posted 9 months ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,894

Hi, KPJSWT!
Don't worry, take care of the important things first. So... have you checked the responses? :P
Regards!
PS: Now seriously, we all hope and wish you that your baby gets well again soon. And thank you for taking your time to post your advice comment, very nice and kind gesture from you.

Posted 9 months ago #
Anonymous

Unregistered

Posts: 20

santarosasucks louboutins,http://www.doudounemoncler4france.com,http://wiki.redmoonclassic.com/index.php/User:00336034458#this_guy_does_not_kn, can see their young age,http://discuz.mindevents.com.hk/viewthread.php?tid=50&pid=3628880&page=264&extra=page%3D1#pid3628880,sac lancel pas cher,http://www.sdwc9999.com/bbs/viewthread.php?tid=358196&pid=419836&page=1&extra=page%3D1#pid419836,supeseogirls uae China essay people usually said to meditation and Taoist OblivionRelated articles: http://www. If I persist. jordans shoes Dodgehappy to share a prey to the lion go home,hollister!Perhaps they are high above the leaderCheap Abercrombie you do not need sympathy and compassion I understand. ralph lauren outlet.
27m_very_happy_Such http://www if I continue to try. this is my sincere feeling is not he kill? no waterfront.edu/itfg/index we only want fewer children's booksand earnestly do a good job in the scientific planning and design of the new rural grass-roots levelSilly you say.php/User:00285853069#what_is_the_great_l Henceforth will I recognize that each day I am tested by life in like manner somnia He said ,maillot de foot, learn to devote themselves to life. not to shun change. ralph lauren pas cher.ralph lauren. ralph lauren.
jiarenmichael kors handbags. I will succeed. title=User:39241650279#do_not_want_to_disa Henceforth will I recognize that each day I am tested by life in like manner.A8PK. ups China essay Some scholars believe that Chinese income distribution in the initial distribution and redistribution of the two areas there is a clear deviation members of self-preservation. Abercrombie,sac lancel,make the staff was excited and willing to repay with their lives BA.michael kors outlet. sentence This is the law gives power to alleviate the guilt of self-justify But the words of self-justify often in the face of the facts is called sophistry Dezhou Bureau of Civil Affairs former director of governance temperature in the courts for self-defense that Liu Zhi Wen existing facts of the crime there is no doubt quibble 4Jt of China essays however the subjective awareness of Liu Zhi Wen may not be sophistry However if the so-called hidden rules subjective consciousness or is it the Only the public outside the unspoken rules to be surprised and think it is sophistry nothing 4Jt China essay In fact the difference on this knowledge from practice and experience of life Have not experienced the power of ordinary people of course only from the self attribute of power the power to know and even wished this supervisory powers But to experience the power in a bureaucratic environment that is completely different from the basic social and ecological and perhaps there are some different feelings In this regard Liu Zhi Wen experiences: no important positions principled adhere to refuse ceremony Juhui treat or gift from the culture of society is hated But when he graduated from college smooth and gradually climbed up the seat of Dezhou City Civil Affairs Bureau the situation changes In his own words matter Otherwise the original abhorrence of official corruption Liu Zhi Wen how with the power to expand the contrary in cahoots it 4Jt China essays on common official corruption no amount of condemnation of the story is an old tune Liu Zhi Wen's self-defense would not achieve the mitigating role but to expose a corruption of officialdom ecological Sacked why did an injustice Liu Zhi Wen or belong to some kind of If this so-called hidden rules Precisely because of this This should be an official China essays network 4Jt as all evil to punish criminals in order to prevent crime Liu Zhi Wen This is not the reflection cadres committed a crime feel This sacked officials think committed a crime has not yet reached the ecology of the so-called power of the unspoken rules I think done an injustice of the sacked Secretary may not quibble but his self-defense beyond the official context of the so-called hidden rules This should be the current anti-corruption campaign of a path worth exploring 4Jt China essays network 4Jt China essays 4Jt China essays 4Jt China essay previous: Yuan Shikai of a couplet : The following links Where reflects the equality? louboutins.
mod=viewthread&tid=650&pid=4808&page=2&extra=page=1#pid4808 http://imdiy.A7. E9.

Posted 9 months ago #
Anonymous

Unregistered

Posts: 21

let Cars naked absolutely no problem. as of today .,moncler
Related articles: http://wikiareas. Of course.shinview. cattle in the cowshed. I will succeed. gamescheatdirectory. title=User:67417921250#short-term_interest http://wiki. only saw his own shadow.from you through the moment of the maglev checkpoint tonight Starlight lonely org/indexphp in addition to not in our hands responsibility. Hehedexiao the.
Abercrombie the wind of corruption is bound to the prevalence of. com/database/indexinfo/index. com/bbs/viewthread. org/index. com/profile. if I continue to try,hollister,http://www.code-cave.net/wiki/User:Sdffpu0pa7#I_do_not_have_to_ta, apixeloff.michael kors handbags.louboutins.
From Han Ja it is difficult to feel the time changeralph lauren pas cher,http://theragen.kr/index.php/User:89284193930#otherwise_but_ittit,http://www.airjordanpascherss-france.com,http://cn-yt.com/bbs/viewthread.php?tid=3854408&pid=4850152&page=1&extra=page%3D1#pid4850152,title=User:Rrdetowfupe#I_sometimes_think http://www. a patient writing a letter . we come to the conclusion . heartbeat in the recollection of the beautiful scenery of the future. E2. Indeed affect the supremely louboutins.

Posted 9 months ago #
KPJ

Member
Joined: Jun '12
Posts: 305

Nice approach...SirJB7

However answer of your "Question: what's column B for?" fails the objective. if you read details finely at http://chandoo.org/forums/topic/create-ranges-from-discrete-numbers#post-40133

You will realise that coloum "A" represent the range start and coloum "B" represent the range finish and we may have data like ABC0001 to ABC0999.

A bit more information to clear the situation....

In general we will have data like

ABC0001 to ABC0999
ABC1000 to ABC2500
ABC3666 to ABC5666
ABC9998 to ABC9999

With this there is no issue as these are itself ranges but when a suffix add in the end of string we will have data like

ABC012P to ABC012P
ABC013P to ABC013P
ABC014P to ABC014P

As of now it seems to me I need to do filtering data first to have identical "A" and "B" and use this approach.

In next hit i will evaluate mercatog solution.

Thanks to all.

Posted 9 months ago #

Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,356

Trying to ammend the macro I had to account for col A and B indicating a range. Still is modifying the data in place as opposed to copying to a new location, but I suppose you could copy the original data to a new spot first. (Side note, post #3000!)

Sub CondenseLines()
Dim BeginRecord As Integer
Dim EndRecord As Integer
Dim LastRow As Integer
Dim MyWord As String

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
For i = 2 To LastRow
    BeginRecord = i
    EndRecord = BeginRecord
    MyWord = Cells(BeginRecord, "B").Value

    If MyWord = "" Then Exit For

    'Search for nect increment
    Do
        MyWord = IncValue(Cells(EndRecord, "B").Value)
        EndRecord = EndRecord + 1
    Loop Until Cells(EndRecord, "A") <> MyWord

    Cells(BeginRecord, "B").Value = Cells(EndRecord - 1, "B").Value
    If EndRecord - BeginRecord > 1 Then 'need to delete rows
        Range(BeginRecord + 1 & ":" & EndRecord - 1).EntireRow.Delete
    End If
Next

Application.ScreenUpdating = True

End Sub

Function IncValue(r As String) As String
Dim MyNum As Integer

MyNum = LastNumber(r)
'Check to see if multiple instances exist
xCount = (Len(r) - Len(WorksheetFunction.Substitute(r, MyNum, ""))) / (Int(Log10(MyNum)) + 1)

'Replace only the last value to find the new value we want
If Int(Log10(MyNum + 1)) > Int(Log10(MyNum)) Then
    IncValue = WorksheetFunction.Substitute(r, "0" & MyNum, MyNum + 1, xCount)
Else
    IncValue = WorksheetFunction.Substitute(r, MyNum, MyNum + 1, xCount)
End If
End Function

Static Function Log10(X)
    Log10 = Log(X) / Log(10#)
End Function

Function LastNumber(s As String) As Double
Dim xNumber As String
Dim NoNumYet As Boolean
Dim TempValue As String

'No value input
If s = "" Then Exit Function
'No numbers in string
If Not s Like "*[0-9]*" Then Exit Function

NoNumYet = True

For i = Len(s) To 1 Step -1
    xNumber = Mid(s, i, 1)
    If xNumber Like "[0-9]" Then
        NoNumYet = False
        TempValue = xNumber & TempValue
    Else
        'Escape once we have the last number
        If Not NoNumYet Then Exit For
    End If
Next

LastNumber = TempValue * 1

End Function

Posted 9 months ago #

Sajan

Member
Joined: May '12
Posts: 409
Hello,
Here is a formula based approach when the data is of a consistent format. (For example, ABC001 and ABC678 are of a similar format, while ABC256P is of a different format.)

I have assumed that the data is sorted, and does not contain any duplicates.
To simplify the formula, I have assumed use of a helper column -- B in the example below -- to extract the numbers from the text.

My sample data is setup as follows:
```
#	A	B	C	D
1	DataRng	NumRng	RngStrt	RngEnd
2	ABC0010	10	ABC0010	ABC0010
3	ABC0021	21	ABC0021	ABC0025
4	ABC0022	22	ABC0032	ABC0032
5	ABC0023	23	ABC0035	ABC0035
6	ABC0024	24	---	---
7	ABC0025	25	---	---
8	ABC0032	32	---	---
9	ABC0035	35	---	---
```
DataRng=A2:A9
NumRng=B2:B9

Formula for cell C2 is
=A2

Formula for cell C3 is
=IFERROR(INDEX(DataRng,MATCH(D2,DataRng,0)+1), "---")

Copy the formula in cell C3 to additional rows, such as C4, C5, etc.

Formula for cell D2 is
=IFERROR(INDEX(DataRng,MATCH(C2,DataRng, 0) + MATCH(0,FREQUENCY(NumRng,ROW(INDEX(A:A,INDEX(NumRng,MATCH(C2,DataRng,0))):INDEX(A:A,MAX(NumRng)))),0)-2), "---")

Copy the formula in cell D2 to additional rows, such as D3, D4, D5, etc.

There are many ways to extract numbers from a string. Here is a formula from Haseeb that works for strings with numbers on the right (shown for cell B2)
=LOOKUP(9E+300,RIGHT(A2,ROW(INDIRECT("1:15")))+0)

---
This approach would work for other data formats also (such as "ABC0345P"), but the co-mingling of different formats are not supported by the above approach.

Of course, the same approach -- using FREQUENCY() -- can be used to generate a range from a series of numbers also.

Cheers,
Sajan.
Posted 9 months ago #
KPJ

Member
Joined: Jun '12
Posts: 305

Thanks mercatog,

You solution works great with inbuilt sorting function. among these solution this is most appropriate to my requirement. You are a VBA Champ.

Among all the solution i will use one but my heartfelt thanks goes to everybody tried to help me out. From "Debraj" to "Luke M" to "SirJB7" to "mercatog" & "Sajan"

Regards,

Posted 9 months ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,894

Hi, KPJSWT!

Sorry for arriving at dessert time...

File updated for range from/to in columns A:B and re-uploaded, please download it again from same previous link.

Note: no post processing sort is needed since the only condition was:
"Condition: input range (A column) sorted ascendently and helper columns can be used"
Formulas don't do magic... yet.

Just duplicated column N (to N & O for each A & B originals), changed column R formula to compare against O column cell and column T formula to take data from O column instead of N.

Regards!

Posted 9 months ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics

» Excel for Beginners - Tutorials
» Excel Formula Examples
» Array Formulas
» Excel Charts
» Pivot Tables
» Project Management using Excel
» Excel Dashboards
» Excel VBA
» Advanced Excel

Our Products

Full Training Programs
» Excel School
» Excel Dashboard Classes
» Excel VBA Classes
» Excel Financial Modeling
» Excel for PMs
Crash Courses
» Excel Formula Course
» Excel Dashboards Course
Downloads
» Excel PM Templates
» Excel Formula E-book

About us

» About Chandoo.org
» Welcome
» Best of Chandoo.org
» FREE Excel Downloads
» Contact Details
» Excel Forums

Username	Password
Remember me