Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default hlookup & vlookup together

i have a master list having about 400 families and their member numbers
as follows

10/00 james
10/01 john
10/02 andrew
10/03 peter
10/04 mike

21/00 jane
21/01 moses
21/02 lucy

where */00 is the main member
i have another 2nd list having a version of the list as follows

10/00 james
10/00 john
10/00 andrew
10/00 peter
10/00 mike

21/00 jane
21/00 moses
21/00 lucy
21/00 (someone who shouldnt be here)

however some families in the second list have more members than in the
master list whereas others have less members

ive been using vlookup in a helper column in the 2nd list only but it
isnt very helpfull here since it lists for me only the main members as
seen in the masterlist ( but has been helpfull in seeing the missing
families)

is there a simple way perhaps using both vlookup & hlookup or any other
functions (without using macros if possible) to help me easily see the
problem families and the problem members.



thanks

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default hlookup & vlookup together

I'd do a little work to create a field in column C.
=LEFT(A1,SEARCH("/",A1)-1)&"-"&B1
and drag down

You'll end up with
10-james
10-john
10-andrew
10-peter
10-mike

Then I'd do that same thing for the second set of data (on Sheet2).

After I did that, I'd compare those two fields.

In D1:
=isnumber(match(c1,sheet2!c:c,0))
and drag down

If you see True, it matches. False means a problem.

You could do the similar formula in the other sheet, too. It might make
checking a bit easier.

schampiri wrote:

i have a master list having about 400 families and their member numbers
as follows

10/00 james
10/01 john
10/02 andrew
10/03 peter
10/04 mike

21/00 jane
21/01 moses
21/02 lucy

where */00 is the main member
i have another 2nd list having a version of the list as follows

10/00 james
10/00 john
10/00 andrew
10/00 peter
10/00 mike

21/00 jane
21/00 moses
21/00 lucy
21/00 (someone who shouldnt be here)

however some families in the second list have more members than in the
master list whereas others have less members

ive been using vlookup in a helper column in the 2nd list only but it
isnt very helpfull here since it lists for me only the main members as
seen in the masterlist ( but has been helpfull in seeing the missing
families)

is there a simple way perhaps using both vlookup & hlookup or any other
functions (without using macros if possible) to help me easily see the
problem families and the problem members.

thanks


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default hlookup & vlookup together

You can create a Pivot Table that looks like this:

Fam Memb Total
10 andrew 2
james 2
john 2
mike 1
peter 2
21 charles 1
jane 2
lucy 2
moses 2

A one in the Total column indicates a problem child.
The PT assumes your data, with headers, is arranged like this:

Numb Memb Fam
10/00 james 10
10/01 john 10
10/02 andrew 10
10/03 peter 10
10/04 mike 10
21/00 jane 21
21/01 moses 21
21/02 lucy 21
10/00 james 10
10/00 john 10
10/00 andrew 10
10/00 peter 10
21/00 jane 21
21/00 moses 21
21/00 lucy 21
21/00 charles 21

The Fam column is created with this formula:
=LEFT(Numb,2)
PT Layout: Row = Fam, Memb
Column = (leave empty)
Data = Count of Memb
PT Options: Uncheck/Hide Grand Totals/Subtotals

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default hlookup & vlookup together

i was hoping there is a simple way of combining vlookup and hlookup
together to see the problem families.

i jus need to identify them then i can visuallyu check for the finer
details of the differences


thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup and hlookup hotelmasters Excel Worksheet Functions 4 August 15th 06 08:41 PM
can hlookup and vlookup be used in combination ? Arul T Excel Discussion (Misc queries) 2 April 20th 06 10:59 AM
VLookup & HLookup [email protected] Excel Discussion (Misc queries) 2 October 3rd 05 03:36 PM
Vlookup and Hlookup Phlogiston2312 Excel Worksheet Functions 1 April 21st 05 04:59 PM
Can vlookup or hlookup look to other worksheets within a workbook. flgc54 Excel Worksheet Functions 1 March 12th 05 01:31 AM


All times are GMT +1. The time now is 12:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"