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!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Zip Code Pricing Macro

Status
Not open for further replies.
bobnplano

bobnplano

Technical User
Mar 25, 2003
52
US
I am only a novice user & I would like to create a Macro that will automatically enter a price based on a zip code. I am using Excel 2002 on Windows XP Pro. I am a contractor with several subcontractors who work for me in different areas of the state of Texas. When I receive a statement from my company it only shows me the zip codes and the rate I am paid & I have to re-do the Excel spreadsheet & separate the subs by contractor numbers & then set the pricing by zip codes for the rate I pay those subs. I would like to take the statement I receive (in Excel format) & resort by Sub # & reset their rates.

EXAMPLE:

My Sub
Rate Sub # Zip Rate
$9.00 1486-10 76801 8.00
$9.00 1486-10 76802 8.00
$9.00 1486-10 76821 8.00
$9.00 1486-10 76823 8.00
$9.00 1486-10 76825 8.00
$9.00 1486-10 76837 8.00
$15.00 1486-10 76837 13.00
$9.00 1486-10 76864 8.00
$9.00 1486-10 76877 15.00
$9.00 1486-10 76903 8.00
$9.00 1486-10 76904 8.00
$9.00 1486-10 76905 8.00
$9.00 1486-10 76943 15.00
$9.00 1486-10 78643 15.00
$9.00 1486-10 79511 8.00
$9.00 1486-10 79512 8.00
$9.00 1486-10 79525 8.00
$9.00 1486-10 79536 7.00
$9.00 1486-10 79541 7.00
$9.00 1486-10 79549 8.00
$9.00 1486-10 79553 8.00
$9.00 1486-10 79556 8.00
$9.00 1486-10 79562 7.00
$9.00 1486-10 79601 7.00
$9.00 1486-10 79602 7.00
$9.00 1486-10 79603 7.00
$9.00 1486-10 79605 7.00
$9.00 1486-10 79606 7.00

Some zip codes are paid at different rates based on the zip code & the type of task to perform. For example, my rate for one zip code may vary based on the task. (see 76837 above) So it is feasable that one zip code may have different rates based on the task. Is there a way to have a macro apply the "If X is blank, then y = blank principle?" i.e. If column B (my rate) is "X" and column D (zip) is 79606 then column E = Sub's rate (based on type of task & zip)?

I am having to manually look at each task, zip & rate & set the sub's rate & it can be very tedious. I have tried to do this on my own but I just cannot get the hang of macros or formulas.

Thanks,

Bob
 
Sort by date Sort by votes


Hi, fellow Texan,

You don't need a macro.

You need a Lookup Table of Zip Codes and Rates. You need to add another column for Task.

Then Name the ranges based on ONE ROW HEADINGS, using Insert > Name > Create - Creat names in TOP row

I used these headings...
[tt]
MyRate SubNbr Zip SubRate Task
[/tt]
Here is the formula for the SubRate
[tt]
=sumproduct((Zip=A1)*(SubNbr=B1)*(Task=C1)*(SubRate))
[/tt]


Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
You might also want to look into using "VLOOKUP()" to build your formula.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Click to expand...

Lets say you have the zip codes and sub-rates in C11:D30
(Starting at C11 enter the zip and D11 would have the coresponding rate as you go down to row 30 as the end.

If you entered the zip code in A2 and in cell B2 typed
"=VLOOKUP(A2, C11:D30,2,FALSE)"

The 2 is the second column of the array which covers C11 through D30 (C is the first column)

It would show you the rate where it found the matching zip code.
If the zip in A2 was 76837 it would return 13.00

And you can use VLOOKUP in formulas.
 
Upvote 0 Downvote



Cap,

Two problems for using VLOOKUP...

1. There is more than ONE lookup value and

2. ther are more than ONE return values possible for the lookup values.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote



Cap,

Two problems for using VLOOKUP...

1. There is more than ONE lookup value and

2. there are more than ONE return values possible for the lookup values.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Alright folks, I'll see if I can figure this out. It all looks good. Now to see if I can do it.

Bob
 
Upvote 0 Downvote
Alright guys, I am still having trouble applying this principle. I want to attach my spreadsheet of what I've done but can't figure out for the life of me how I can attach it. I have placed it in my gmail documents & allowed it to be shared so I hope you can see it using the URL link I have posted. If you are not able to access or see my shared document, then please tell me how I can attach it to a reply. I have set this up for one sub contractor & I hope you can assist me in setting up the lookup so that I can duplicate it for my other sub-contractors. You will notice that some Zip codes are in red indicating within the sub's own county & the zip codes that are bold are outside his normal operating area so I have to pay a higher rate eventhough I lose money because it is higher than I get paid for the task (the nature of the beast, as they say). Please help!

Bob
 
 http://spreadsheets.google.com/ccc?key=rNqhiiMsFp8sU5OAD79MxxA&hl=en
Upvote 0 Downvote
Try this formula in E3
Code: 
=VLOOKUP(D3,$H$3:$N$96,MATCH(A3,$H$2:$N$2,0),FALSE)
 
Upvote 0 Downvote
Also, your titles must match so in the lookup table (H2:N2) the title for Property Condition should be changed to Property Condition - No Contact
 
Upvote 0 Downvote



Is your table in columns H:N applicable to ALL subs?

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Skip,

No, columns H:N for the other subs are different zips & rates but exact same header.
 
Upvote 0 Downvote
Where are you at now with this project?

What are the existing problems?

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Skip,

When I apply the formula, exactly as you listed it, I get #N/A in every cell from E3:E67. I put the formula in E3 & copied it all the way to E67. Am I doing something wrong?

Bob
 
Upvote 0 Downvote
Post your formula.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Here is my formula:

=VLOOKUP(D3,$H$3:$N$96,MATCH(A3,$H$2:$N$2,0),FALSE)
 
Upvote 0 Downvote
Skip,

I even changed the formula to:

=VLOOKUP(D3,$H$3:$H$96,MATCH(A3,$I$2:$N$2,0),FALSE)

Apparently this doesn't help either. Same results!
 
Upvote 0 Downvote


[tt]
=VLOOKUP(D3,$H$3:$N$96,MATCH(A3,$I$2:$N$2,0),FALSE)
[/tt]
Your Lookup Range (second argument) needs to reference the entire table data range, starting with the loookup value range.... $H$3:$N$96

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Skip,

I still get #N/A. What am I doing wrong now? I checked & rechecked to make sure I copied your formula exactly as you said & I still get the #N/A response. I am confused now!
 
Upvote 0 Downvote

[tt]
=VLOOKUP(D3,$H$3:$N$96,[red]MATCH(A3,$I$2:$N$2,0)[/red],FALSE)
[/tt]
#N/A means that EITHER
there is no A3 value in row 2 or
there is no D3 value in column H

on your sheet.

Also if you EDIT the formula (F2) you will see all the referenced ranges outlined in individual colors. That's a check.

If you EDIT the formula and select just the MATCH function, as emphasized, and hit the F9 key, it will substitute the MATCH offset value. (BTW, follow that check by hitting the ESC key to return the formula) That's a check.

Make sure that the values in column A EXACTLY matched values in row 2. No leading or trailing spaces.

Make sure that the Zip code data in both column D and column H are formatted TEXT and not GENERAL or any other numeric format.

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Skip,

I am getting more & more confused now -

"#N/A means that EITHER

there is no A3 value in row 2" (A3 is Foreclosure & it does appear in row 2 i.e., L2)

or

"there is no D3 value in column H" (D3 is 76801 & it does appear in column H i.e., H12)

Values in column A do EXACTLY match values in row 2 with no leading or trailing spaces - Does that also mean no spaces in between words? i.e. Delinquent Interview? or should it be Delinquent_Interview?

Column D & H are both formatted as TEXT & not GENERAL



 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
305
strongm
strongm
remeng
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
259
dhookom
dhookom
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
184
mintjulep
M
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
181
strongm
strongm
remeng
  • Locked
  • Question
Multiple Users in a Macro enabled Excel Doc
Replies
0
Views
141
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top