Re: Cross Referencing Lists (REVISED)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 10/06/04 

Date: Wed, 6 Oct 2004 18:52:06 +0200

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
"Ralph" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:361101c4abc3$a84bd150$a501280a@phx.gbl...
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?
>-----Original Message-----
>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!
>
>
>
>
>>-----Original Message-----
>>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
>>
>>--
>>Regards
>>Frank Kabel
>>Frankfurt, Germany
>>
>>"Ralph" <anonymous@discussions.microsoft.com> schrieb im
>Newsbeitrag
>>news:352e01c4abb9$c27259f0$a501280a@phx.gbl...
>>> 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!
>>>
>>>
>>>
>>>
>>>
>>>
>>> >-----Original Message-----
>>> >Hi
>>> >in B1 on your second sheet enter:
>>>
>>>=IF(ISNA(MATCH(A1,'sheet1'!$B$1:$B$100,0)),"",INDEX('sheet1'!$A$1:$A
$1
>>0
>>> >0,MATCH(A1,'sheet1'!$B$1:$B$100,0)))
>>> >
>>> >--
>>> >Regards
>>> >Frank Kabel
>>> >Frankfurt, Germany
>>> >
>>> >"Ralph" <anonymous@discussions.microsoft.com> schrieb im
>>> Newsbeitrag
>>> >news:1a0101c4abb1$6b1adc20$7d02280a@phx.gbl...
>>> >> 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!
>>> >
>>> >.
>>> >
>>
>>.
>>
>.
>

Relevant Pages

  • Re: Autoruns
    ... It's impossible for AutoRuns to keep a database of all software available and what you may have installed on your system and then uninstalled (with the uninstalled software not deleting it's own entries from the registry properly or completely), or indeed what 3rd party software is essential at boot-up and what isn't. ... It's just a matter of going through the categories in AutoRuns and also looking in Task Manager's Processes list and/or using the similar lists, but with full path info., from the system info. freeware program 'Everest' which you can find and download with a google search and will help you identify which entries belong to which software packages by virtue of the full path. ... Finally don't delete an entry from AutoRuns, just uncheck it so that it is restorable later. ...
    (microsoft.public.windowsxp.general)
  • Re: nested structure with "internal references"
    ... dict entries can be lists or other dicts. ... The lists and dicts can also contain int, float, string, ... ... But i'd also like to have something like a "reference" to another ... I'd like to refer to another entry and not copy that entry, ...
    (comp.lang.python)
  • Re: Cross Referencing Lists (REVISED)
    ... >>Frank Kabel ... >>> I need Excel to reduce column A as follows: ... >>>>of entries in column A to be reduced as follows. ... >>>>there is an entry in column A that has an entry in column B ...
    (microsoft.public.excel.worksheet.functions)
  • Re: a benefit to using a linked list in insertion sort?
    ... >> Use linked lists to make insertions easy, ... >> isn't a binary search at all but a hash table. ... You "probe" the entry at this index. ... rather than having to insert and move subsequent entries as ...
    (comp.programming)
  • [RFC] [PATCH] Improve list.h documentation for _rcu() primitives
    ... primitives, as suggested off-list. ... * Insert a new entry before the specified head. ... * manipulating whole lists rather than single entries, ...
    (Linux-Kernel)