Newbie and use of lookup question?

[manasi]

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?

 

[Guest]

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?).

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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.