Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Expand Range in a cell
- Thread starter Hosacans
- Start date
- Thread starter
- #3
i guess i should just explain my whole situation...
i have file from UPS that lists 3 digits zip codes and their corresponding zone numbers. their records comes in ranges ex: a1 = 004-010, b1 = 5... meaning that zips with 004 to 010 as their first 3 digits will be in zone 5.
now i have a bunch of data with actual zip codes, i manage to extract the first 3 digits of every record. but i am stuck on trying to match my actual records to the table by UPS.
i.e. if i have a record that has 006 as the first 3 digit zip, how do i return 5 as the zone by comparing 006 to 004-010?
hope this is clear
THanks
hosackies
i have file from UPS that lists 3 digits zip codes and their corresponding zone numbers. their records comes in ranges ex: a1 = 004-010, b1 = 5... meaning that zips with 004 to 010 as their first 3 digits will be in zone 5.
now i have a bunch of data with actual zip codes, i manage to extract the first 3 digits of every record. but i am stuck on trying to match my actual records to the table by UPS.
i.e. if i have a record that has 006 as the first 3 digit zip, how do i return 5 as the zone by comparing 006 to 004-010?
hope this is clear
THanks
hosackies
Copy this formula in column B as far as you wish to go.
=IF(LEFT($A$1,3)*1+ROW(B7)-1>RIGHT($A$1,3)*1,"",LEFT($A$1,3)*1+ROW(B7)-1)
Hightlight the relevant cells in column B then go to
Format, Cells, Number tab, select General, select Custom, in the Type box enter 000 (three zeros) and click OK
=IF(LEFT($A$1,3)*1+ROW(B7)-1>RIGHT($A$1,3)*1,"",LEFT($A$1,3)*1+ROW(B7)-1)
Hightlight the relevant cells in column B then go to
Format, Cells, Number tab, select General, select Custom, in the Type box enter 000 (three zeros) and click OK
Sorry. Copied the wrong cell. Put this formula in cell B1 and copy as far you wish.
=IF(LEFT($A$1,3)*1+ROW(B1)-1>RIGHT($A$1,3)*1,"",LEFT($A$1,3)*1+ROW(B1)-1)
=IF(LEFT($A$1,3)*1+ROW(B1)-1>RIGHT($A$1,3)*1,"",LEFT($A$1,3)*1+ROW(B1)-1)
Hosacans,
If your UPS ranges are in column A (with corresponding zone number in column B) and your list of Zip Codes is in column E, you can return the zone number with this array formula:
=MIN(IF(A$1:A$30="",999, IF((--RIGHT(E1,3)>=--LEFT(A$1:A$30,3))*(--RIGHT(E1,3)<=--RIGHT(A$1:A$30,3)), B$1:B$30,999)))
Array formula, so hold Control + Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula. If not, click in the formula bar and try again.
The formula returns 999 if the zip code in column E does not fall into one of the possible ranges in column A. If the zip code falls into more than one range, it returns the one with the smaller zone number.
The reference to row 30 is arbitrary. It may extend beyond your data from UPS if you wish.
Brad
If your UPS ranges are in column A (with corresponding zone number in column B) and your list of Zip Codes is in column E, you can return the zone number with this array formula:
=MIN(IF(A$1:A$30="",999, IF((--RIGHT(E1,3)>=--LEFT(A$1:A$30,3))*(--RIGHT(E1,3)<=--RIGHT(A$1:A$30,3)), B$1:B$30,999)))
Array formula, so hold Control + Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula. If not, click in the formula bar and try again.
The formula returns 999 if the zip code in column E does not fall into one of the possible ranges in column A. If the zip code falls into more than one range, it returns the one with the smaller zone number.
The reference to row 30 is arbitrary. It may extend beyond your data from UPS if you wish.
Brad
- Thread starter
- #8
Thanks byundt, that does exactly what i need...
but i do have a question, what does the "--" in front of the left and right functions do?
i still have to sit with this formula and dissect it to figure out the logic behind it...
thanks again for digging me out of this mistery.
but i do have a question, what does the "--" in front of the left and right functions do?
i still have to sit with this formula and dissect it to figure out the logic behind it...
thanks again for digging me out of this mistery.
Hosacans,
The double negative -- is a means of coercing (converting) from a string to a number. I could alternatively have added 0, multiplied by 1 or used the VALUE function:
=MIN(IF(A$1:A$30="",999, IF((1*RIGHT(E1,3)>=1*LEFT(A$1:A$30,3))*(1*RIGHT(E1,3)<=1*RIGHT(A$1:A$30,3)), B$1:B$30,999)))
=MIN(IF(A$1:A$30="",999, IF((VALUE(RIGHT(E1,3))>=VALUE(LEFT(A$1:A$30,3)))*(VALUE(RIGHT(E1,3))<=VALUE(RIGHT(A$1:A$30,3))), B$1:B$30,999)))
The MIN function is in the formula because the IF function returns an array of zone numbers and 999. If there is only one matching zone, the MIN will ignore all the 999 values and select the zone number. If there is more than one zone number, the MIN will pick the lowest one.
Obviously, the 999 is an arbitrary number. You could pick anything you wanted, as long as it was bigger than any possible zone number. When developing the formula, I also considered using the empty string "" instead of 999. The only trouble is that the formula then returns an error value if there are no matches.
Brad
The double negative -- is a means of coercing (converting) from a string to a number. I could alternatively have added 0, multiplied by 1 or used the VALUE function:
=MIN(IF(A$1:A$30="",999, IF((1*RIGHT(E1,3)>=1*LEFT(A$1:A$30,3))*(1*RIGHT(E1,3)<=1*RIGHT(A$1:A$30,3)), B$1:B$30,999)))
=MIN(IF(A$1:A$30="",999, IF((VALUE(RIGHT(E1,3))>=VALUE(LEFT(A$1:A$30,3)))*(VALUE(RIGHT(E1,3))<=VALUE(RIGHT(A$1:A$30,3))), B$1:B$30,999)))
The MIN function is in the formula because the IF function returns an array of zone numbers and 999. If there is only one matching zone, the MIN will ignore all the 999 values and select the zone number. If there is more than one zone number, the MIN will pick the lowest one.
Obviously, the 999 is an arbitrary number. You could pick anything you wanted, as long as it was bigger than any possible zone number. When developing the formula, I also considered using the empty string "" instead of 999. The only trouble is that the formula then returns an error value if there are no matches.
Brad
- Thread starter
- #10
Brad, thanks for the explanation, the double negative is indeed a cool trick...
there's another question i have, and it refers to the "*" sign you have in the second if statement. to my understanding the condition dictates, if zip is greater or equal to the left of ranges: "*" (times): the zip is lesser or equal to the right of the ranges, then return the corresponding zone number in the array... what does that asterisk do/mean? is it really a multiplication or is it like an "and" property?
there's another question i have, and it refers to the "*" sign you have in the second if statement. to my understanding the condition dictates, if zip is greater or equal to the left of ranges: "*" (times): the zip is lesser or equal to the right of the ranges, then return the corresponding zone number in the array... what does that asterisk do/mean? is it really a multiplication or is it like an "and" property?
Hosacans,
The * is acting like an AND condition. Each of the Boolean expressions is enclosed by parentheses. Multiplying them returns 1 (TRUE) only if all of the expressions in the product return TRUE.
Adding them would be more like an OR condition. If any of the Boolean expressions are TRUE, then the result will be TRUE (the sum has a value of 1 or more).
The reason the formula uses all this tricky business is because AND and OR don't work the way you expect in an array formula. Instead, they look over the entire range (all rows) to determine whether to return TRUE or FALSE--instead of the more useful method of evaluating TRUE or FALSE a row at a time.
Brad
The * is acting like an AND condition. Each of the Boolean expressions is enclosed by parentheses. Multiplying them returns 1 (TRUE) only if all of the expressions in the product return TRUE.
Adding them would be more like an OR condition. If any of the Boolean expressions are TRUE, then the result will be TRUE (the sum has a value of 1 or more).
The reason the formula uses all this tricky business is because AND and OR don't work the way you expect in an array formula. Instead, they look over the entire range (all rows) to determine whether to return TRUE or FALSE--instead of the more useful method of evaluating TRUE or FALSE a row at a time.
Brad
- Status
Similar threads
- Locked
- Question
- Locked
- Question