Excel cross-checking (what do I call it?)

[-Demosthenes-]

I have a collum that is a list of cities, and I need to make sure every city in Utah is in it. I can create a list of all the cities, but then how can I cross check it with the list I already have (to make sure I have them all)?

[Sayonara]

Iirc, you want "vertical lookup".

 

Or you could just order each list by alpha, and paste one next to the other...? (use a simple pivot table if one list contains multiple entries for the same city).

[-Demosthenes-]

Okay, in my test I have two collums of city names and I used the third collum to find out if they had the same city names. In C1 I put "=VLOOKUP(B1, A1:A253,TRUE)" and it displayed the city name when B1 had a city contained between A1 and A253, and when it didn't it displayed "#N/A" (deleted the last letter so it was different. But when when I misspelled "Cedar City" as "Ceder City" it gave the value "Cedar Hills". And When I tried just "Cedar" it gave me "Castle Valley." So I'm not sure I'm doing it right, I have very little experience in excel.

 

I'm not sure what the second part of your post is saying, again, I'm an excel noob.

[Sayonara]

You have two lists right? Do they have (or should they have) multiple entries for the same cities?

 

If not, you can sort the lists alphabetically, paste one next to the other, and visually compare them.

 

If one or both have non-unique entries in them, go to help and learn about pivot tables. They are one of Excel's best features, and massively overlooked by casual users.

 

I have never actually used VLOOKUP because Excel is satan's bumhole, but I am sure someone here will have done.

[-Demosthenes-]

One list is a complete list of all the cities, the reference. The other is a list of cities (listed by zip code) that should contain all the cities. I'm trying to make sure it does.

 

I'll look up pivot tables.

[ParanoiA]

One list is a complete list of all the cities' date=' the reference. The other is a list of cities (listed by zip code) that [i']should[/i] contain all the cities. I'm trying to make sure it does.

 

I'll look up pivot tables.

 

Ok, when you use TRUE, this means that you want the closest answer. If you use FALSE, it will only return the exact answer. Most people will tell you to sort both columns ascending. This is required for TRUE, but not for FALSE.

 

Also, I don't see your return column index value. Try this:

 

VLOOKUP(B1,A:A,1,FALSE)

 

This says, do a vertical lookup on the value in cell B1 in the entire column of A. If a match is found, then return that value, if not, then return #N/A.

 

#N/A is telling you it couldn't find that value, so that would be the same as saying the city name is not in the list, or it's mispelled.

 

I don't know of any way you could design a function to find mispelled city names.

[-Demosthenes-]

I think I got it, I'll alphabetize them and compare them visually, seems like a good idea.

 

Now, if I have a list of numbers is there a way which numbers are repeats (a number occurs more than once)?

[john5746]

Sort numbers in column A, then past following formula in B column, except the first cell, which you would set to 1.

 

=IF(A2=A1,B1+1,1) this will increment if repeated or set to 1 if not.

 

If you are doing alot of this kind of stuff, importing to Access and doing queries would be some useful knowledge. Excel is great for calculations, statistics and quick sorting. Access is better with sorting, counting, etc