Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() I hope I posted this question in the right forum this time. I am newbie here and is having hard time in doing this. I have an excel workbook which has two sheets Grp1 and Grp2 within these sheets i have unit and id columns, what I am trying to do is find out the combination of unit and id column which is on Grp1 but not on Grp2 tab. Can I use the Lookup function for this.Could you all please help? -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 |
#2
![]() |
|||
|
|||
![]()
I'm not quite sure what you want...so here it goes my guess. I would import
both lists into MS Access as two separate tables. Use a Left Join and run a query based on the information that you want to extract. The Lookup feature will simply say if cell A1 = Red and B1 = Apple, then you enter a lookup formula in another cell, we'll say D1, that says if C1 = Red, then D1 will automatically pop-up with Apple. (confused?). "manasi" wrote: I hope I posted this question in the right forum this time. I am newbie here and is having hard time in doing this. I have an excel workbook which has two sheets Grp1 and Grp2 within these sheets i have unit and id columns, what I am trying to do is find out the combination of unit and id column which is on Grp1 but not on Grp2 tab. Can I use the Lookup function for this.Could you all please help? -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 |
#3
![]() |
|||
|
|||
![]()
I used column A to hold the units in both sheets.
I used column B to hold the ID in both sheets. Then I insert a new column in Grp1. I used this formula: =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1: $B$20=B1),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.) Make the range big enough to match your data, but don't use the whole column. And drag down the column. If you see True, then that pair is on Grp2. False means that pair isn't on Grp2. manasi wrote: I hope I posted this question in the right forum this time. I am newbie here and is having hard time in doing this. I have an excel workbook which has two sheets Grp1 and Grp2 within these sheets i have unit and id columns, what I am trying to do is find out the combination of unit and id column which is on Grp1 but not on Grp2 tab. Can I use the Lookup function for this.Could you all please help? -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 -- Dave Peterson |
#4
![]() |
|||
|
|||
![]() I used this formula: =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1: $B$20=B1),0)) Thanks Dave I was able to copy the formula but the results were not the ones I wanted I will try giving an example. in Grp1 tab I have a combination of unit id 107 236 100 336 ...... when I go to Grp2 tab I have unit id 107 236 108 456 ..... The combination of grp1 100/336 is not on Grp2 therefore I need to show that. Hope this will clarify -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 |
#5
![]() |
|||
|
|||
![]()
Did you get a result of True for the 107/236 row?
Did you get a result of False for the 100/336 row? If you did and just need to see the rows with falses, you could apply Data|Filter|autofilter to that column. Display just the False rows. If you need to copy those visible rows somewhere else, you could just select those visible rows and edit|copy, then edit|paste. === What did those formulas evaluate to? manasi wrote: I used this formula: =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1: $B$20=B1),0)) Thanks Dave I was able to copy the formula but the results were not the ones I wanted I will try giving an example. in Grp1 tab I have a combination of unit id 107 236 100 336 ..... when I go to Grp2 tab I have unit id 107 236 108 456 .... The combination of grp1 100/336 is not on Grp2 therefore I need to show that. Hope this will clarify -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 -- Dave Peterson |
#6
![]() |
|||
|
|||
![]() Some of the combinations which existed in Grp1 and also Grp2 were showing up as False. For ex: in row 261 i had 51/63 going to Grp2 row 265 had 51/63 but the formula evaluated to False on Grp1 which meant there is no combination on Grp2 which is not true. Please suggest me. -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 |
#7
![]() |
|||
|
|||
![]()
Find a couple of empty cells.
Put this in one: =grp1!a261=grp2!a265 and put this in the other: =grp1!b261=grp2!b265 Do you get Trues for both of these? If you don't look for extra spaces, numbers treated as text in the offending cells. manasi wrote: Some of the combinations which existed in Grp1 and also Grp2 were showing up as False. For ex: in row 261 i had 51/63 going to Grp2 row 265 had 51/63 but the formula evaluated to False on Grp1 which meant there is no combination on Grp2 which is not true. Please suggest me. -- manasi ------------------------------------------------------------------------ manasi's Profile: http://www.excelforum.com/member.php...o&userid=27411 View this thread: http://www.excelforum.com/showthread...hreadid=469228 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|