• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create ranges from discrete numbers

kuldeepjainesl

kuldeepjainesl

Member
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
 
KPJSWT


What is the rule/s between the input and output ?

There doesn't appear to be any obvious rules
 
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,
 
Hi KuldeePJainSWeTa,


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

[pre]
Code: 
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
[/pre]
Regards,

Deb
 
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,
 
Couple of questions:

1. Is col B always the same value as col A?

2. Is it okay if we sort the data?
 
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,
 
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?

[pre]
Code: 
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
[/pre]
 
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,
 
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)
 
Hi everybody

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

[pre]
Code: 
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
[/pre]
 
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)
 
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,
 
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.
 
santarosasucks louboutins,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.
 
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,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.
 
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.
 
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!)

[pre]
Code: 
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
[/pre]
 
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:

[pre]
Code: 
#	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	---	---
[/pre]
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.
 
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,
 
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!
 
You must log in or register to reply here.
Back
Top