PC Review


Reply
Thread Tools Rate Thread

Newbie and use of lookup question?

 
 
manasi
Guest
Posts: n/a
 
      20th Sep 2005

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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?TWFyaWUgRA==?=
Guest
Posts: n/a
 
      20th Sep 2005
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
>
>

 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      20th Sep 2005
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
 
Reply With Quote
 
manasi
Guest
Posts: n/a
 
      20th Sep 2005

> 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

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Sep 2005
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
 
Reply With Quote
 
manasi
Guest
Posts: n/a
 
      21st Sep 2005

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

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Sep 2005
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup =?Utf-8?B?SkJ1c2g=?= Microsoft Excel Worksheet Functions 0 7th Dec 2006 12:24 AM
Use a lookup field, or lookup table? =?Utf-8?B?UmljdGVy?= Microsoft Access 2 19th Jan 2006 06:27 AM
Lookup Lookup Lookup =?Utf-8?B?TVI=?= Microsoft Excel Worksheet Functions 2 10th Mar 2005 02:59 AM
lookup tables and avoiding lookup wizard =?Utf-8?B?T2xkU3RvbmVCdWRkaGE=?= Microsoft Access Getting Started 1 15th Feb 2005 10:21 PM
How do I lookup and return different values when the lookup value. =?Utf-8?B?a2c=?= Microsoft Excel Misc 1 20th Jan 2005 01:53 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:31 AM.