Formula to identify and display all numbers from 2 separate cells

[chellechelle]

I have a string of numbers that I need to have individually listed (one number per cell). Right now that string is being identified by having the smallest number in one cell and the largest number in another cell. Instead of displaying like this I need to have the entire string of numbers to display vertically as 1 number per cell.

Any help you can provide is greatly appreciated!

 

[igold]

Hi chellechelle,

Welcome to the MrExcel forum.

The display vertically as 1 number per cell, I understand. I am not sure from your description what the numbers look like prior to that...

 

[chellechelle]

Hi! Thank you for your response.
Right now, Column C is named "Zip low" and has the lowest 5 digit zip code assigned to an agent. Column D is named "zip high" and has the highest 5 digit zip assigned to that agent.
The program we were uploading this to was able to discern the zips between the two listed numbers, but now we're using a different upload program and have to tell the system all of the specific zips. So I am hoping for a formula that will look something like this.

if c="zip low" and d="zip high" then these are the numbers that are between those two numbers.

If you can provide a formula for that, I know how to convert text to columns, etc!

Your help is greatly appreciated!!

 

[igold]

Are the numbers that you are looking for that fall in between the low and the high zip codes, just adding 1 to the lower number until you reach the higher number or are the coming from a list of defined valid zip codes that are listed somewhere.

Maybe you could show a before and after type of representation so I have a better grasp as to where the zips start, where you want them listed and that sort of thing. Is it mandatory to be a formula or are you open to a macro.

 

[chellechelle]

Regarding the zip code number sequence: Yes! it is any of thenumbers by adding 1 to the lowest number until the highest number is reached.
And regarding output, I’m sure a macro would work just aswell as a formula. I’m curious as to whyyou suggest one rather than the other!
An example of what I’m hoping to get in output would look this:

AgentName​	OfficeNum​	ZipLow​	ZipHigh​	StateLow​	StateHigh​	Zip Range
LASTNAME, FIRSTNAME	AI003	71700	71899	AR	AR	71700, 71701, 71702, 71703, 71704, 71705…… ETC, 71899

<tbody>
</tbody>

 

[igold]

I sometimes find it is easier to do stuff with a macro rather than a formula. I guess if I had better formula skills that would be different. I also think I read somewhere that if a task can be completed with a formula rather that a macro, you should use the formula. I am sure one of the pundits on this site will chime in on the correctness of this.


Are you looking for the "Zip Range" to be all in one cell separated by commas or would you prefer them going down the column.

From your example assuming the "AgentName" is column A and the "Zip Range" is Column G, then the following two codes will, as noted in the code do one or the other. The caveat with putting all the zips in one cell is that 1) there is a limit to how many characters a cell can contain and 2) a lesser number of how many characters a cell will display. The formula bar however will show all the characters.

Sub zipRng()
    
    'All zips in Cell G2
    Dim zip As Long, i As Long
    Dim zipr As String
    
    For i = Range("C2") To Range("D2")
        zipr = zipr & ", " & i
    Next
    zipr = Mid(zipr, 2)
    Range("G2") = zipr
    
End Sub

Sub zipRng2()


    'All zips going down colunm G
    Dim qzip As Long, i As Long
    Dim arrZip
    
    qzip = Range("D2") - Range("C2") + 1
    ReDim arrZip(1 To qzip, 1 To 1)
    For i = 1 To qzip
        arrZip(i, 1) = Range("C2") + i - 1
    Next
    Range("G2").Resize(UBound(arrZip)) = arrZip
    
End Sub

I hope this helps some...

 

[chellechelle]

HELP SOME???!!! THAT WORKED LIKE A GEMMMMMM, YOU’RE A LIFESAVER!!!!!! I didn’t have aclue as to how I was going to complete this project in a timely manner, so you’vedefinitely made my week exponentially better. Thank you tremendously, and havea great day!

 

[igold]

I am glad that it works for you, I was happy to help. Thanks for your feedback!!

 

[chellechelle]

So now I have all these separate zip ranges in their own separatecolumns. And each agent is on their own separate worksheet. Now I’d like to be able to have all theseseparate columns combined into 1 long data string in Column G. Would it at all be possible to create aformula (or macro J)that would allow me to search each column (starting with column H) and copy anydata in that column to the next blank cell in Column G. The formula or macrowould stop once it reached a column in which there was no data. Please let me know if you need any furtherdetails. As always, your help is greatlyappreciated!

 

[igold]

I am not sure I understand (I got lost somewhere, data string??). You want to combine all the zips in the Column G's from separate sheets and put them all in one column. What sheet would this long column of zips be on and what are you searching for and what information do you want to return from your search. Perhaps another little graphic would help me understand what you want to achieve...