Cross Referencing Lists (REVISED)

[Ralph]

Pardon me, I accidently sent the previous post before
finishing it. Here is the problem:

I have 2 tables, table 1 which has 2 colums of entries
let's call them columns A and B. Table 2 has only 1 column
of entries. Some (but not all) of Table 2's entries are
also be found in column B of table 1. When there is a
match between an entry found in table 2 and an entry in
column B of table 1, I want Excel to give me the entry in
COLUMN A that corresponds to the matching item found in
column B. I want a list compiled as such. If this is
confusing perhaps I can email the 2 lists to someone and it
will be easier to explain (if that is appropriate to say on
this list).

I very much appreciate any help!

 

[Frank Kabel]

Hi
in B1 on your second sheet enter:
=IF(ISNA(MATCH(A1,'sheet1'!$B$1:$B$100,0)),"",INDEX('sheet1'!$A$1:$A$10
0,MATCH(A1,'sheet1'!$B$1:$B$100,0)))

 

[Ralph]

Frank,

Thank you very much for helping again. I think this
formula will work but it would need to be modified to fit
the way I have arranged my data. It looks like in your
formula you were assuming I have different worksheets
setup? I'm sorry if I gave you that impression. THis is
very hard to do without actually showing you the files but
I will try to explain how the information is arranged.

For speaking purposes I described the problem in my prevous
post like this: "I have 2 tables, table 1 which has 2
colums of entries let's call them columns A and B. Table 2
has only 1 column of entries. Some (but not all) of Table
2's entries are also be found in column B of table 1. When
there is a match between an entry found in table 2 and an
entry in column B of table 1, I want Excel to give me the
entry in COLUMN A that corresponds to the matching item
found in column B. I want a list compiled as such."

I didn't think anyone was going to write me the exact
formula, sorry. The data is actually arranged all on 1
worksheet, columns A and B are the same as described in
Table 1 above. And column C has the data that I described
above as being in Table 2. So they are all on the same
worksheet. What I need is for Excel to locate every case
that an entry found in column B matches an entry found
anywhere in column C (meaning it doesnt have to be next to
it, it can be ANYWHERE in column C). Then I need to Excel
to compile a list of the entry found in column A that is in
the same row as the entry in column B that matched ANY
entry in column C. Am I explaining this clearly? I hope
so. Sorry, I tried to adapt the formula you created but
couldn't figure it out. I think one more try and we've got it!

Thanks!

 

[Frank Kabel]

Hi
so use the following formula in D1:
=IF(ISNA(MATCH(C1,$B$1:$B$100,0)),"",INDEX($A$1:$A$10
0,MATCH(C1,$B$1:$B$100,0)))
and copy this down

 

[Ralph]

Frank,

I responded to this post once already but for some reason
it didn't seem to go through. THank you for writing the
formula. I've been trying to adapt it to the way my data
is arranged but can't figure out how. My data is arranged
such that columns A and B are as described previously (as
table 1) and column C has the data that I was referring to
as being in table 2. So, all the data is on 1 worksheet.
How would I adapt your formula?

Thanks!

 

[Frank Kabel]

Hi
I already provided the adapted formula

 

[Ralph]

Ok Frank, we are getting very hot now, almost got it.
Thank you for revising the formula, I changed it to:

=IF(ISNA(MATCH(C1,$B$1:$B$6000,0)),"",INDEX($A$1:$A$6000>0,MATCH(C1,$B$1:$B$6000,0)))

so that it will search the entire list (only changed the
rows to 6000). What this formula is doing is saying "TRUE"
when the item in column C next to the formula is also found
in anywhere in column B. I need the reverse. I need it to
recognize when an item in column B next to the formula is
found anywhere in column C and then instead of saying
"TRUE" I need it to say what is in column A next to it.
THis is so difficult to explain. Let me try restating the
problem in this way:

I have a list with 3 columns: A, B and C. I need the list
of entries in column A to be reduced as follows. Whenever
there is an entry in column A that has an entry in column B
on the same row (i.e. next to it) that matches an entry
found anywhere in column C THEN I need Excel to keep that
entry in column A. WHen it is not the case I don't want
the entry in column A anymore, this will have the effect of
reducing the list of entries in column A to ONLY those
entries in which the corresponding entry in column B (i.e.
next to it) is found anywhere in the list of entries in
column C. Does this make sense?

 

[Frank Kabel]

Hi
try:
=IF(ISNA(MATCH(B1,$C$1:$C$6000,0)),"",INDEX($A$1:$A$6000,MATCH(B1,$C$1:
$C$6000,0)))

--
Regards
Frank Kabel
Frankfurt, Germany

Ok Frank, we are getting very hot now, almost got it.
Thank you for revising the formula, I changed it to:

=IF(ISNA(MATCH(C1,$B$1:$B$6000,0)),"",INDEX($A$1:$A$6000>0,MATCH(C1,$B$
1:$B$6000,0)))

so that it will search the entire list (only changed the
rows to 6000). What this formula is doing is saying "TRUE"
when the item in column C next to the formula is also found
in anywhere in column B. I need the reverse. I need it to
recognize when an item in column B next to the formula is
found anywhere in column C and then instead of saying
"TRUE" I need it to say what is in column A next to it.
THis is so difficult to explain. Let me try restating the
problem in this way:

I have a list with 3 columns: A, B and C. I need the list
of entries in column A to be reduced as follows. Whenever
there is an entry in column A that has an entry in column B
on the same row (i.e. next to it) that matches an entry
found anywhere in column C THEN I need Excel to keep that
entry in column A. WHen it is not the case I don't want
the entry in column A anymore, this will have the effect of
reducing the list of entries in column A to ONLY those
entries in which the corresponding entry in column B (i.e.
next to it) is found anywhere in the list of entries in
column C. Does this make sense?

 

[Ralph]

Frank,

THanks, but it didn't work. Is there any way I can send
you the file or post it somehow? I don't think I am
communicating the issue well enough for you to understand
it. I have tried stating it several different ways now. I
will attempt to say it again using a hypothetical example:

I have a list with 3 columns. In column A are different
types of foods, such as milk, carrots, steak, etc... NExt
to each item in column A is the category it is in, for example:
column A column B
milk dairy
carrots vegetable
steak meat


Column C has some items that are also in column B:
Column C
dairy
meat

I need Excel to reduce column A as follows:
column A
milk
steak

THe list in column A is reduced because there was no
"vegetable" entry in column C. I have a list of 5,000 plus
entries that I need this done on so I hope you will bear
with this process. I feel that you are getting close.

THANKS!

 

[Frank Kabel]

hi
o.k. send me the file:
email: frank[dot]kabel[at]freenet[dot]de

and enter some expected result values in the desired location

 

[Ralph]

Frank,

A better example is this:

A B C
cheese dairy dairy
milk dairy vegetable
butter dairy cars
carrots vegetable trees
celery vegetable computers
cucumber vegetable
steak meat
chicken meat


RESULT:
A
cheese
milk
butter
carrots
celery
cucumber

 

[Ralph]

I sent it now Frank, THANK YOU VERY MUCH!

-----Original Message-----
hi
o.k. send me the file:
email: frank[dot]kabel[at]freenet[dot]de

and enter some expected result values in the desired location

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

THanks, but it didn't work. Is there any way I can send
you the file or post it somehow? I don't think I am
communicating the issue well enough for you to understand
it. I have tried stating it several different ways now. I
will attempt to say it again using a hypothetical example:

I have a list with 3 columns. In column A are different
types of foods, such as milk, carrots, steak, etc... NExt
to each item in column A is the category it is in, for example:
column A column B
milk dairy
carrots vegetable
steak meat


Column C has some items that are also in column B:
Column C
dairy
meat

I need Excel to reduce column A as follows:
column A
milk
steak

THe list in column A is reduced because there was no
"vegetable" entry in column C. I have a list of 5,000 plus
entries that I need this done on so I hope you will bear
with this process. I feel that you are getting close.

THANKS!




:
$

.

 

[Frank Kabel]

Hi
send it back to you. For all other following this thread. Working
solution in cell D1: array formula:
=INDEX($A$1:$A$500,SMALL(IF(COUNTIF($C$1:$C$500,$B$1:$B$500),ROW($B$1:$
B$100)),ROW(1:1)))

and copied down

--
Regards
Frank Kabel
Frankfurt, Germany

I sent it now Frank, THANK YOU VERY MUCH!

-----Original Message-----
hi
o.k. send me the file:
email: frank[dot]kabel[at]freenet[dot]de

and enter some expected result values in the desired location

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

THanks, but it didn't work. Is there any way I can send
you the file or post it somehow? I don't think I am
communicating the issue well enough for you to understand
it. I have tried stating it several different ways now. I
will attempt to say it again using a hypothetical example:

I have a list with 3 columns. In column A are different
types of foods, such as milk, carrots, steak, etc... NExt
to each item in column A is the category it is in, for example:
column A column B
milk dairy
carrots vegetable
steak meat


Column C has some items that are also in column B:
Column C
dairy
meat

I need Excel to reduce column A as follows:
column A
milk
steak

THe list in column A is reduced because there was no
"vegetable" entry in column C. I have a list of 5,000 plus
entries that I need this done on so I hope you will bear
with this process. I feel that you are getting close.

THANKS!




-----Original Message-----
Hi
try:

=IF(ISNA(MATCH(B1,$C$1:$C$6000,0)),"",INDEX($A$1:$A$6000,MATCH(B1,$C$ 1
:

=IF(ISNA(MATCH(C1,$B$1:$B$6000,0)),"",INDEX($A$1:$A$6000>0,MATCH(C1,$ B
$ $
A

.