Numbers between "Start" and "End" sequence

[freshgoblinmilk]

A is the smallest value of a sequence
B is the largest value of a sequence
What I want if for C to show all numbers that fit in that range

I am hoping that this way I can easily find duplicated entries beteen 2 tabs.

 

[Jimmypop]

Hi there

You can try the following formula perhaps?

Book1
	A	B	C
1	Start	End	Range
2	1	8	1,2,3,4,5,6,7,8
3	9	11	9,10,11
4	12	12	12
5	13	13	13
6	14	21	14,15,16,17,18,19,20,21
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=TEXTJOIN(",",TRUE,IF(ROW(INDIRECT(A2&":"&B2))>0,ROW(INDIRECT(A2&":"&B2)),""))

This I think will only work if you have the numbers as given. Will the numers from Start to End ever change?

 

[Fluff]

The OP doesn't have Textjoin, as that only appeared with 2019.

 

[Jimmypop]

Hi Fluff

I keep forgetting to check that... Apologies to OP...

 

[RoryA]

I am hoping that this way I can easily find duplicated entries between 2 tabs.

If you are actually still on 2016, I suspect you will need code, since you don't have any of the good text functions. Is code an option for you?

 

[RoryA]

One possibility:

Function ListNumbers(startNum As Long, endNum As Long, Optional sep As String = ",") As String
   Dim OutNums() As String
   ReDim OutNums(startNum To endNum)
   Dim ctr As Long
   For ctr = startNum To endNum
      OutNums(ctr) = ctr
   Next ctr
   ListNumbers = Join(OutNums, sep)
   
End Function

Used like:

=ListNumbers(A2,B2)

 

[kvsrinivasamurthy]

Since 2016 is not having Textjoin function, UDF is alternative.
Code for UDF

Function GetNumbers(St As Long, Nd As Long)
Dim temp, T&
If St <> "" And Nd <> "" Then
For T = St To Nd
temp = temp & ", " & T
Next T
End If
If temp <> "" Then GetNumbers = Mid(temp, 3)
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm
In C2 then copied down.
=GetNumbers(A2,B2)

 

[awoohaw]

@freshgoblinmilk , What kind of duplicates are you looking for? There may be other ways to do this other than by comparing concatenations? Can you post some examples of the data each each tab using the xl2bb add in (link below)? Sanitize your data before posting

 

[kvsrinivasamurthy]

Correction to Post#7

Function GetNumbers(St As Range, Nd As Range)
Dim temp, T&
If St <> "" And Nd <> "" Then
For T = St To Nd
temp = temp & ", " & T
Next T
End If
If temp <> "" Then GetNumbers = Mid(temp, 3)
End Function

 

[awoohaw]

Here is a excel worksheet formula solution. It requires helper columns for each instance of a number in the range.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	8	1	2	3	4	5	6	7	8
2	9	11	9	10	11
3	12	12	12
4	13	13	13
5	14	21	14	15	16	17	18	19	20	21
Sheet1

Cell Formulas
Range	Formula
C1:M5	C1	= IF(COLUMN(C1)-3+$A1<=$B1, AGGREGATE(15,4,COLUMN(1:1),COLUMN(C1)-3+$A1),"")