Match 2 sheet and build table

[npereira]

Hi all,

I have a document I need to do.

I have 2 xls doc sheets. Both docs have 2 colums, 1st is the US state
the second is the City. I need to build a table that will tell me ho
many rows (State and City) matches between the 2 sheets and i
possible:

Build a table showing how many matches and how many dont,
list the ones that dont,
build a chart

How can I do this? I dont even know where to start....

The 1 sheet is the master, has 200 or so rows that the other sheet mus
match against.

Thanks for any help you can provide me

 

[npereira]

Can Anyone help with this? At least tell me how this should be done

 

[npereira]

ok, let me put this in a proper example for better under standing:

Sheet1:
Col A = STATE,
Col B = CITY

Sheet 2:
Col A = STATE,
Col B = CITY
Col C = Does Cell A1:B1 match any in Sheet 1 Cell A1:B2162

Example:
Sheet 1 Sheet 2
State City State City Match?
CA Frezno AL Mobile NO
AR Ferndale CA Frezno YES
AZ Phoenix AZ Tucson NO

Does this help more? The colum C in sheet 2 should be the one havin
the formula for each row.

Hope this helps as I am despratly trying to make this work. Bee
looking around the net and found this:

=if(sheet1!A1:B1="","",if(type(vlookup(Sheet1!A1:B1,A$1:B$2162,1,false))
=A1:B1,"wrong","correct"))

But it does not work, or maybe I have it wrong or something... I don
know. And I dont know where or what that second line does or where t
put it...

send an email if you want the excel sheet as I cannot attache it here

 

[Dave Peterson]

In C2 (headers in row 1???):

=isnumber(match(1,(a2=sheet1!$a$2:$a$2162)*(b2=sheet1!$b$2:$b$2162),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

If you don't have headers, change those top rows to 1.

 

[npereira]

somthing is wrong as even if there is a match it says FALSE...

I have no headers... the first row is where the data starts

thank

 

[Dave Peterson]

Did you use ctrl-shift-enter?

Are you sure the spelling is exactly the same? No leading/trailing/embedded
spaces???

 

[npereira]

Got it working with this formula...

=IF(ISERROR(IF(VLOOKUP(A4,Sheet1!$A$2:$B$2162,2,FALSE)=B4,"YES","")),"",IF(VLOOKUP(A4,Sheet1!$A$2:$B$2162,2,FALSE)=B4,"YES",""))

Thanks !

 

[npereira]

Although, 1 issue is I had to put the City in column A and State in
Column B or else it would'nt work

 

[npereira]

Now the only issue is to build a Pie chart that is dynamic with a column
setup as a List (column N has Population desity per City) but when I set
my list for that column, my pie chart screws up and isnt dynamic.

 

[Dave Peterson]

I don't work enough with charts to be helpful.

Now the only issue is to build a Pie chart that is dynamic with a column
setup as a List (column N has Population desity per City) but when I set
my list for that column, my pie chart screws up and isnt dynamic.