| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I build a excel sheet that counts item in an inventory | Excel Worksheet Functions | |||
| How do i build a search table in excel | Excel Worksheet Functions | |||
| Build a single order sheet from a multiple page material list. | Excel Discussion (Misc queries) | |||
| Is there a way to build a timeline PPT spread sheet from excel | Excel Worksheet Functions | |||
| Inventory sheet to track, order & reduce quantity from master. | New Users to Excel | |||
Linear Mode
