How do I consolidate data into ranges

[Jerry]

I need to consolidate data from 2 columns. Column A has over 40,000 unique
values, Column B has only 5. I need to consolidate Column A into ranges. I.e.
1 thru 1000 = 1. 1001 thry 1250 = 2. 1251 thru 2500 = 1. etc.
Thanks

 

[StumpedAgain]

Maybe I'm not understanding correctly, but if you follow this if statement it
can give you what you stated below.

=IF(A1<1001,1,IF(A1<1251,2,IF(A1<2501,3,IF(etc.))))

This what you're looking for?

 

[Jerry]

Sorry, allow me to clarify. The first column is zip code ranges, and the
second column is transit times (# of days). I need to consolidate all the zip
codes into ranges as opposed to a huge list of zip codes.
Thanks

 

[Jerry]

The figures 1 thru 1000 were an example, I need a formula to create the
actual range based on the value in Column B.

 

[StumpedAgain]

Perhaps a quick "this is what I have" and "this is what I want" would help.
I.e.

What I have:
Zipcodes Transit Times
94534 4
65481 5
etc.

What I want:
??

I guess I'm confused on what you're looking to get out of it. Do you want a
count of all the ranges between certain values? How are you looking to
consolidate these zip codes?

I'm not sure this is what you're looking for but perhaps a slight
modification to what I posted before:

=IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.))))

Let me know!

 

[Jerry]

This is what I have:

Zipcodes Transit time
00501 5
00502 5
00503 5
11001 1
11002 1

I need to replace this with:

Zipcode Ranges Transit Time
00501 - 00503 5
11001 - 11002 1

Thank you for all your assistance

 

[StumpedAgain]

OOhh... well then... I would use the following in VBA. I used it on a quick
test string and it worked for me. Let me know how it goes!


Option Explicit
Sub consolidation()

Dim transit, zipcode1, zipcode2 As Range

Set transit = Range("B2")

Do Until transit.Value = ""
Set zipcode1 = transit.Offset(0, -1)
Do Until transit <> transit.Offset(1, 0)
Set zipcode2 = transit.Offset(1, -1)
zipcode1.Value = zipcode1 & zipcode2.Value
transit.Offset(1, 0).EntireRow.Delete
Loop
Set transit = transit.Offset(1, 0)
Loop

Set transit = Range("A2")
Do Until transit.Value = ""
transit.Value = Left(transit.Value, 5) & " - " & Right(transit.Value, 5)
Set transit = transit.Offset(1, 0)
Loop

End Sub

 

[Jerry]

I am unfamiliar with VBA, and do not own the program. Is this something than
can be done in Excel?

 

[StumpedAgain]

Hey Jerry, no problem.

To start a new macro:
Open excel and press Alt + F11
Start a new module by going to Insert->Module
Paste the below programming into the module.

To run the macro:
Go to the sheet on which you want to run the macro
Go to Tools->Macro->Macros...
Select the consolidation macro and click run

As this is your first macro, make sure to save a copy before you run a macro
because there is no "undo" after running a macro!

Also note that the macro starts in "B2". If your transit times start
anywhere else, you have to change the code.

Let me know if you have any questions!!!