Leila-Marie
Board Regular
- Joined
- Aug 23, 2013
- Messages
- 50
Hi there...
I am currently trying to work out how I can get Excel to help me check some lists...
I have a long list of addresses (say 4000) and I want it checked against a shorter list of 230 cities that are of interest to me (well, to my boss...)
So, so far all I've managed to do is do a really long combination of "OR" functions, which I've stringed together (see below) but I can only get to about 85 cities, before Excel tells me my function is too long...
So, this (dodgy) function allows me to check whether the city of the address (the city is in cell C1) corresponds to the first 85 cities in column F)
While this does give me the desired results of TRUE or FALSE, it's very unelegant for one thing, and - what is far worse - limits me to checking lists no longer than approx. 85 entries.
There MUST be a better way to do this, surely?
I'm not all that bad at using Excel, but I've searched for 4 hours now and cannot find ANY way (that I can understand) that lets me solve my task...
Many thanks for any pointers or suggestions...
(This may have been covered in previous posts, but the posts I found that sounded SORT OF similar, I couldn't understand - and I didn't know how to refine my search any better - so sorry if this question has been posted before)
Thank you!
Leila-Marie
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]-->=OR(OR(C1=$F$1;C1=$F$2;C1=$F$3;C1=$F$4;C1=$F$5;C1=$F$6;C1=$F$7;C1=$F$8;C1=$F$9;C1=$F$10;C1=$F$11;C1=$F$12;C1=$F$13;C1=$F$14;C1=$F$15;C1=$F$16;C1=$F$17;C1=$F$18;C1=$F$19;C1=$F$20);OR(C1=$F$21;C1=$F$22;C1=$F$23;C1=$F$24;C1=$F$25;C1=$F$26;C1=$F$27;C1=$F$28;C1=$F$29;C1=$F$30;C1=$F$31;C1=$F$32;C1=$F$33;C1=$F$34;C1=$F$35;C1=$F$36;C1=$F$37;C1=$F$38;C1=$F$39;C1=$F$40);OR(C1=$F$41;C1=$F$42;C1=$F$43;C1=$F$44;C1=$F$45;C1=$F$46;C1=$F$47;C1=$F$48;C1=$F$49;C1=$F$50;C1=$F$51;C1=$F$52;C1=$F$53;C1=$F$54;C1=$F$55;C1=$F$56;C1=$F$57;C1=$F$58;C1=$F$59;C1=$F$60);OR(C1=$F$61;C1=$F$62;C1=$F$63;C1=$F$64;C1=$F$65;C1=$F$66;C1=$F$67;C1=$F$68;C1=$F$69;C1=$F$70;C1=$F$71;C1=$F$72;C1=$F$73;C1=$F$74;C1=$F$75;C1=$F$76;C1=$F$77;C1=$F$78;C1=$F$79;C1=$F$80;C1=$F$81;C1=$F$82;C1=$F$83;C1=$F$84;C1=$F$85))<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Normale Tabelle"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
I am currently trying to work out how I can get Excel to help me check some lists...
I have a long list of addresses (say 4000) and I want it checked against a shorter list of 230 cities that are of interest to me (well, to my boss...)
So, so far all I've managed to do is do a really long combination of "OR" functions, which I've stringed together (see below) but I can only get to about 85 cities, before Excel tells me my function is too long...
So, this (dodgy) function allows me to check whether the city of the address (the city is in cell C1) corresponds to the first 85 cities in column F)
While this does give me the desired results of TRUE or FALSE, it's very unelegant for one thing, and - what is far worse - limits me to checking lists no longer than approx. 85 entries.
There MUST be a better way to do this, surely?
I'm not all that bad at using Excel, but I've searched for 4 hours now and cannot find ANY way (that I can understand) that lets me solve my task...
Many thanks for any pointers or suggestions...
(This may have been covered in previous posts, but the posts I found that sounded SORT OF similar, I couldn't understand - and I didn't know how to refine my search any better - so sorry if this question has been posted before)
Thank you!
Leila-Marie
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]-->=OR(OR(C1=$F$1;C1=$F$2;C1=$F$3;C1=$F$4;C1=$F$5;C1=$F$6;C1=$F$7;C1=$F$8;C1=$F$9;C1=$F$10;C1=$F$11;C1=$F$12;C1=$F$13;C1=$F$14;C1=$F$15;C1=$F$16;C1=$F$17;C1=$F$18;C1=$F$19;C1=$F$20);OR(C1=$F$21;C1=$F$22;C1=$F$23;C1=$F$24;C1=$F$25;C1=$F$26;C1=$F$27;C1=$F$28;C1=$F$29;C1=$F$30;C1=$F$31;C1=$F$32;C1=$F$33;C1=$F$34;C1=$F$35;C1=$F$36;C1=$F$37;C1=$F$38;C1=$F$39;C1=$F$40);OR(C1=$F$41;C1=$F$42;C1=$F$43;C1=$F$44;C1=$F$45;C1=$F$46;C1=$F$47;C1=$F$48;C1=$F$49;C1=$F$50;C1=$F$51;C1=$F$52;C1=$F$53;C1=$F$54;C1=$F$55;C1=$F$56;C1=$F$57;C1=$F$58;C1=$F$59;C1=$F$60);OR(C1=$F$61;C1=$F$62;C1=$F$63;C1=$F$64;C1=$F$65;C1=$F$66;C1=$F$67;C1=$F$68;C1=$F$69;C1=$F$70;C1=$F$71;C1=$F$72;C1=$F$73;C1=$F$74;C1=$F$75;C1=$F$76;C1=$F$77;C1=$F$78;C1=$F$79;C1=$F$80;C1=$F$81;C1=$F$82;C1=$F$83;C1=$F$84;C1=$F$85))<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Normale Tabelle"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
