Listing all values within a range of values

[arundharmarajan]

Hi,

I have a column(assume A) with various numerical values from 0000 to 9000 (not in any incremental sequence).

There is another column which has many ranges, like 1794-6317 in it (the lower and upper values will be valid and will be in the list).

Is there any way to display the numbers within that range by looking at column A?

I am fine if the output comes in multiple cells or in a single cell separated by comma or any other delimiter.

I have given a sample image to explain better.

 

[footoo]

Sub FT()
Dim rng1 As Range, rng2 As Range, cel As Range, cel2 As Range, s$, l%, r%
Set rng1 = Range([A1], Cells(Rows.Count, "A").End(xlUp))
Set rng2 = Range([c1], Cells(Rows.Count, "C").End(xlUp))
rng2.Offset(0, 2).NumberFormat = "@"
For Each cel2 In rng2
    s = ""
    l = Left(cel2, 4) - 1
    r = Right(cel2, 4) + 1
    For Each cel In rng1
        If cel > l And cel < r Then
            If s = "" Then
                s = cel
            Else
                s = s & "," & cel
            End If
        End If
    Next
    cel2(1, 3) = s
Next
End Sub

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can try...

[table="width: 500"]
[tr]
	[td]Function WithinSpan(CellsToCheck As Range, DashedSpan As String) As String
  Dim SpanLimits As Variant
  SpanLimits = Split(DashedSpan, "-")
  WithinSpan = Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF((0+@>=" & SpanLimits(0) & ")*(0+@<=" & SpanLimits(1) & "),@,"""")", "@", CellsToCheck.Address(0, 0)))))), " ", ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use WithinSpan just like it was a built-in Excel function. For example, for the example you posted, put this formula in cell E1 and copy it down to the end of the number spans you have in Column C...

=WithinSpan(A1:A11,C1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[arundharmarajan]

Sub FT()
Dim rng1 As Range, rng2 As Range, cel As Range, cel2 As Range, s$, l%, r%
Set rng1 = Range([A1], Cells(Rows.Count, "A").End(xlUp))
Set rng2 = Range([c1], Cells(Rows.Count, "C").End(xlUp))
rng2.Offset(0, 2).NumberFormat = "@"
For Each cel2 In rng2
    s = ""
    l = Left(cel2, 4) - 1
    r = Right(cel2, 4) + 1
    For Each cel In rng1
        If cel > l And cel < r Then
            If s = "" Then
                s = cel
            Else
                s = s & "," & cel
            End If
        End If
    Next
    cel2(1, 3) = s
Next
End Sub

Here is a UDF (user defined function) that you can try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function WithinSpan(CellsToCheck As Range, DashedSpan As String) As String
  Dim SpanLimits As Variant
  SpanLimits = Split(DashedSpan, "-")
  WithinSpan = Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF((0+@>=" & SpanLimits(0) & ")*(0+@<=" & SpanLimits(1) & "),@,"""")", "@", CellsToCheck.Address(0, 0)))))), " ", ", ")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the responses. If I have to increase the complexity of the issue, if the input has multiple ranges and also single values and I want them encompassed within a tag, will it be possible?

Something of this sort..

P.S. Sorry, unable to find a way to attach a sample file to make it easier.

 

[Peter_SSs]

Welcome to the MrExcel board!

.. if the input has multiple ranges and also single values and I want them encompassed within a tag, will it be possible?

I'm assuming that "1000" in cell F2 instead of "0000" was a typo.

See if this is what you want

Sub ListValues()
  Dim d As Object
  Dim a As Variant, bits As Variant
  Dim i As Long, j As Long, k As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For Each bits In a
    d(bits) = 1
  Next bits
  a = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = vbNullString
    bits = Split(a(i, 1), ",")
    For j = 0 To UBound(bits)
      If InStr(1, bits(j), "-") = 0 Then
        If d.exists(bits(j)) Then s = s & "</a><a>" & bits(j)
      Else
        For k = Split(bits(j), "-")(0) To Split(bits(j), "-")(1)
          If d.exists(Format(k, "0000")) Then s = s & "</a><a>" & Format(k, "0000")
        Next k
      End If
    Next j
    a(i, 1) = Mid(s, 5) & "</a>"
  Next i
  With Range("F2").Resize(UBound(a))
    .NumberFormat = "@"
    .Value = a
    .Columns.AutoFit
  End With
End Sub

My results:
(My signature block below has a link with options for posts screen shots/sample data like this.)

Book1
	A	B	C	D	E	F
1
2	0000		0000,1794-6317			00001794318659736317
3	0087		1076-7635,8369			1076179431865973631776358369
4	1076		1794,5973-7912,9000			179459736317763579129000
5	1794		0000-1076,6317-7912			000000871076631776357912
6	3186		1076			1076
7	5973
8	6317
9	7635
10	7912
11	8369
12	9000
Check Values

 

[arundharmarajan]

Welcome to the MrExcel board!

I'm assuming that "1000" in cell F2 instead of "0000" was a typo.

See if this is what you want

Oops, yeah. Missing out on F2 was a mistake. Thank you so much for the response. Works beautifully!!!!

 

[Peter_SSs]

Thank you so much for the response. Works beautifully!!!!

You are welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only, like I've edited your post to.

 

[Rick Rothstein]

Here is how I would rewrite the UDF that I posted in Message #3 to meet your new requirements...

[table="width: 500"]
[tr]
	[td]Function WithinSpan(CellsToCheck As Range, DashedSpan As String) As String
  Dim V As Variant, SpanLimits As Variant
  For Each V In Split(DashedSpan, ",")
    SpanLimits = Split(V & "-" & V, "-")
    WithinSpan = WithinSpan & "<a>" & Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF((0+@>=" & SpanLimits(0) & ")*(0+@<=" & SpanLimits(1) & "),@,"""")", "@", CellsToCheck.Address(0, 0)))))), " ", "</a><a>") & "</a>"
  Next
End Function[/td]
[/tr]
[/table]