Formula to identify and display all numbers from 2 separate cells

chellechelle

New Member
Joined
Oct 25, 2019
Messages
5
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 25, 2019
Messages
5
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 25, 2019
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Code:
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


Code:
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

New Member
Joined
Oct 25, 2019
Messages
5

ADVERTISEMENT

:eek: 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! :biggrin:

 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am glad that it works for you, I was happy to help. Thanks for your feedback!!
 

chellechelle

New Member
Joined
Oct 25, 2019
Messages
5
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,172
Messages
5,600,128
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top