Match 2 sheet and build table
 

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 how
many rows (State and City) matches between the 2 sheets and if
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 must
match against.

Thanks for any help you can provide me.


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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 having
the formula for each row.

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

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

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

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


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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

=isnumber(match(1,(a2=sheet1!$a$2:$a$2162)*(b2=she et1!$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 wrote:

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 having
the formula for each row.

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

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

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

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

--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

--

Dave Peterson

Match 2 sheet and build table
 

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

thanks


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

Did you use ctrl-shift-enter?

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



npereira wrote:

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

thanks

--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

--

Dave Peterson

Match 2 sheet and build table
 

Got it working with this formula...

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

Thanks !


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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.


--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

Match 2 sheet and build table
 

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

npereira wrote:

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.

--
npereira
------------------------------------------------------------------------
npereira's Profile: http://www.excelforum.com/member.php...o&userid=10799
View this thread: http://www.excelforum.com/showthread...hreadid=570506

--

Dave Peterson