Lounge of Tomorrow - View Single Post - Excel Help

Alex · 09-15-2008, 10:23 AM

I'll email back the spreadsheet with some tweaks I made. Fortunately for me the issue is really really simple (at least the problem with it breaking at row 182).

Remember where I said to make sure your array left plenty of room for the size of the Address worksheet? So in my sample formula I used C1000 and D1000? The problem is, your Address worksheet is 14,428 rows long, so the attempt to match is failing because it is only searching in the first 1,000 rows and Row 182 just happens to be where you are alphabetically far enough along to be outside that 1,000 row range.

So changing C1000 and D1000 to C15000 and D15000 (or even larger) will get rid of that problem.

As you say, you have some data quality problems. The source of the 0 values in many rows is, as I'm sure you figured out, that not every name on the Address worksheet has a city or ZIP code associated with it. If you want to handle those a bit more gracefully you can replace the above formulas with these:

Code:

For City:
=IF(VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE))

For ZIP:
=IF(VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE))

This will have the impact of showing "NO CITY" or "NO ZIP" instead of "0". Apply a little conditional formatting to the column (look under Format --> Conditional Formatting and you can set up a rule, for example, that will cause the text to display as red and bold when it says NO CITY) and they'll really pop if you need to in some way resolve them.

The source of the vast majority of your #N/A is that there simply isn't a match for the name on the Address worksheet. Some are because there really is no match, most are because "Aggasid, Michelle & Frank" on Order is "Aggasid, Michelle &". Nothing can be done to help with these except manually changing one to match the other. A small portion of them seem to be issues in text formatting such as "Wethers, Mark" on one and "wethers, mark" on the other. That could be accounted for in the formulas but they seem to be such a small portion it probably isn't worth the effort.

If you want to handle the #N/A errors more gracefully you can take the above revisions another step and use these:

Code:

For City:
=IF(ISERROR(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)))

For ZIP:
=IF(ISERROR(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)))

This will result in any #N/A displaying as NO MATCH and any match with no city or ZIP displaying as NO CITY or NO ZIP as appropriate.

One issue, though, is that VLOOKUP in a large array is slow so you may see some lag in updating when/if you change values.

I'll email you the spreadsheet with all of these changes so you can see them.

09-15-2008, 10:23 AM	#15
Alex . Join Date: Feb 2005 Posts: 13,354	I'll email back the spreadsheet with some tweaks I made. Fortunately for me the issue is really really simple (at least the problem with it breaking at row 182). Remember where I said to make sure your array left plenty of room for the size of the Address worksheet? So in my sample formula I used C1000 and D1000? The problem is, your Address worksheet is 14,428 rows long, so the attempt to match is failing because it is only searching in the first 1,000 rows and Row 182 just happens to be where you are alphabetically far enough along to be outside that 1,000 row range. So changing C1000 and D1000 to C15000 and D15000 (or even larger) will get rid of that problem. As you say, you have some data quality problems. The source of the 0 values in many rows is, as I'm sure you figured out, that not every name on the Address worksheet has a city or ZIP code associated with it. If you want to handle those a bit more gracefully you can replace the above formulas with these: Code: For City: =IF(VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE)) For ZIP: =IF(VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE)) This will have the impact of showing "NO CITY" or "NO ZIP" instead of "0". Apply a little conditional formatting to the column (look under Format --> Conditional Formatting and you can set up a rule, for example, that will cause the text to display as red and bold when it says NO CITY) and they'll really pop if you need to in some way resolve them. The source of the vast majority of your #N/A is that there simply isn't a match for the name on the Address worksheet. Some are because there really is no match, most are because "Aggasid, Michelle & Frank" on Order is "Aggasid, Michelle &". Nothing can be done to help with these except manually changing one to match the other. A small portion of them seem to be issues in text formatting such as "Wethers, Mark" on one and "wethers, mark" on the other. That could be accounted for in the formulas but they seem to be such a small portion it probably isn't worth the effort. If you want to handle the #N/A errors more gracefully you can take the above revisions another step and use these: Code: For City: =IF(ISERROR(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE))) For ZIP: =IF(ISERROR(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE))) This will result in any #N/A displaying as NO MATCH and any match with no city or ZIP displaying as NO CITY or NO ZIP as appropriate. One issue, though, is that VLOOKUP in a large array is slow so you may see some lag in updating when/if you change values. I'll email you the spreadsheet with all of these changes so you can see them.
	Submit to Quotes