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

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

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

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