compare two groups of data
 

I am using excel 2002. I have two groups of data in one spreadsheet that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000 statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the value is
in D and return that answer. Since there are more account numbers in C than B
I can not seem to make VLOOKUP work, but that function really confuses me.

yup..
as far as i know, VLOOKUP is enough for this job.
may i know what really confuses you?

"Jeanne" wrote:

I am using excel 2002. I have two groups of data in one spreadsheet that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000 statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the value is
in D and return that answer. Since there are more account numbers in C than B
I can not seem to make VLOOKUP work, but that function really confuses me.

the function arguments. Sometimes if I try to answer the function values and
then do f2 I can see what it is trying to do. Can you tell me what would go
in each function argument based on the coumn info I gave?

"Alvin" wrote:

yup..
as far as i know, VLOOKUP is enough for this job.
may i know what really confuses you?

"Jeanne" wrote:

I am using excel 2002. I have two groups of data in one spreadsheet that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000 statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the value is
in D and return that answer. Since there are more account numbers in C than B
I can not seem to make VLOOKUP work, but that function really confuses me.

i'll use the column E to do the vlookup
=VLOOKUP(B1,C$1:D$33000,2,0)
and copy the formula for all B.
explanation : find value of B1 in C1:C33000. If found, take the 2nd column
of C1:D33000, so it must be in column D.

If you don't want to get bothered with "#N/A"s, replace the formula
=IF(ISERROR(VLOOKUP(B1,C$1:D$33000,2,0)),"",VLOOKU P(B1,C$1:D$33000,2,0))

"Jeanne" wrote:

the function arguments. Sometimes if I try to answer the function values and
then do f2 I can see what it is trying to do. Can you tell me what would go
in each function argument based on the coumn info I gave?

"Alvin" wrote:

yup..
as far as i know, VLOOKUP is enough for this job.
may i know what really confuses you?

"Jeanne" wrote:

I am using excel 2002. I have two groups of data in one spreadsheet that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000 statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the value is
in D and return that answer. Since there are more account numbers in C than B
I can not seem to make VLOOKUP work, but that function really confuses me.

Jeanne Wrote:
I am using excel 2002. I have two groups of data in one spreadsheet
that I
am trying to comare. Here is my example:
Column A = value of a statement cycle, Column B account number, There
are
4000 statement cycles and 4000 account numbers.
Column C with 33,000 account numbers, column D = value of 33,000
statement
cycles. In a seperate column I need to compare the two account number
columns, if the number is in both B & C then I need to know what the
value is
in D and return that answer. Since there are more account numbers in C
than B
I can not seem to make VLOOKUP work, but that function really confuses
me.

I will do this in 3 steps ... there may be a more elegant and efficient
solution but meanwhile, this might work for you.

I will assume that all the account numbers in Columns B and C are all
unique, i.e, there are no duplications WITHIN each column. Let us say
that the account number that you want to be looked up is entered in
Cell E1.

STEP 1: DETERMINE IF THE ENTRY IN CELL E1 IS IN B1:B4000

You can enter this formula in, say, Cell F1

=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,B1:B4000))))

If the account number in Cell E1 is in B1:B4000 (and since the account
numbers are unique in this column as ASSUMED above), your answer in the
above formula will be 1. If the account number is not in B1:B4000, then
your answer will be a 0 (zero).

STEP 2: DETERMINE IF THE ENTRY IN CELL E1 IS IN C1:C33000

You can enter this formula in, say, Cell F2

=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,C1:C33000))))

Again, if the account number in Cell E1 is in C1:C33000 (and since the
account numbers are unique in this column as ASSUMED above), your
answer in the above formula will be 1. If the account number is not in
C1:C33000, then your answer will be a 0 (zero).


STEP 3 : ENTER THIS FORMULA IN, SAY, CELL E2

=IF(and(F1=1,F2=1),VLOOKUP(E1,C1:D33000,2,0),"N/A")

Hope this will help you.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=474792