Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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? -- -SA "Jerry" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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 "StumpedAgain" wrote: 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? -- -SA "Jerry" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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" wrote: 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? -- -SA "Jerry" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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 befo =IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.)))) Let me know! -- -SA "Jerry" wrote: 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" wrote: 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? -- -SA "Jerry" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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" wrote: 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 befo =IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.)))) Let me know! -- -SA "Jerry" wrote: 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" wrote: 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? -- -SA "Jerry" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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 -- -SA "Jerry" wrote: 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" wrote: 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 befo =IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.)))) Let me know! -- -SA "Jerry" wrote: 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" wrote: 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? -- -SA "Jerry" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
I am unfamiliar with VBA, and do not own the program. Is this something than
can be done in Excel? "StumpedAgain" wrote: 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 -- -SA "Jerry" wrote: 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" wrote: 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 befo =IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.)))) Let me know! -- -SA "Jerry" wrote: 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" wrote: 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? -- -SA "Jerry" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data into ranges
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!!! -- -SA "Jerry" wrote: I am unfamiliar with VBA, and do not own the program. Is this something than can be done in Excel? "StumpedAgain" wrote: 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 -- -SA "Jerry" wrote: 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" wrote: 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 befo =IF(A1<1001,"1-1000",IF(A1<1251,"1001-1250",IF(A1<2501,"1251-2500",IF(etc.)))) Let me know! -- -SA "Jerry" wrote: 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" wrote: 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? -- -SA "Jerry" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Consolidate Data | Excel Discussion (Misc queries) | |||
consolidate data | Excel Worksheet Functions | |||
Data Consolidate | Excel Discussion (Misc queries) | |||
help to consolidate data | Excel Worksheet Functions | |||
consolidate data | Excel Discussion (Misc queries) |