+ Reply to Thread
Results 1 to 3 of 3

Match and index functions: corrlating data from 2 worksheets

  1. #1
    schultzd@ohsu.edu
    Guest

    Match and index functions: corrlating data from 2 worksheets

    I'm not sure the best way to do this. I have 2 workbooks (I can easily
    combine them in one worksheet-that is not the problem). In the first I
    have a column of identifiers and corresponding data in the adjacent
    column. In a second workbook I have a list of identifiers, which
    includes all the identifiers present in the first workbook, and a
    column of data associated with them. After I combine them into a
    single worksheet I want to look up the data value associated with the
    same identifier and move it next to the first set of data. For example:

    1A 1M Convert to 1 A M
    3B 2N 3 B O
    5C 3O 5 C Q
    4P
    5Q

    The real files are much larger and the identifiers are not listed in
    order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
    suggests using Index and Match functions as such:

    =INDEX(F2:F21,MATCH(B1,D2:D21,0))

    But this would only return the corelate for the identifier in B1. Can
    one somehow drag down this formula in a column B so that all B1:B2000
    is matched or can one replace B1 with an array? or is there some other
    way to do this?
    Thanks,
    Dennis


  2. #2
    Biff
    Guest

    Re: Match and index functions: corrlating data from 2 worksheets

    Hi!

    Assume:

    A............B............C..........D..........E
    1 1A.........1M...........1...........A..........M
    2 3B.........2N............3...........B..........O
    3 5C.........3O............5...........C..........Q
    4 4P
    5 5Q

    Formula C1: =LEFT(A1,1)

    Formula D1: =RIGHT(A1,1)

    Formula E1 (entered with the key combo of CTRL,SHIFT,ENTER):

    =INDEX(RIGHT(B$1:B$5,1),MATCH(C1,LEFT(B$1:B$5,1),0))

    Select the range C1, D1, E1 and drag copy down as needed.

    Something tells me it won't be that easy! <g>

    Biff

    <schultzd@ohsu.edu> wrote in message
    news:1116637917.742328.158240@g43g2000cwa.googlegroups.com...
    > I'm not sure the best way to do this. I have 2 workbooks (I can easily
    > combine them in one worksheet-that is not the problem). In the first I
    > have a column of identifiers and corresponding data in the adjacent
    > column. In a second workbook I have a list of identifiers, which
    > includes all the identifiers present in the first workbook, and a
    > column of data associated with them. After I combine them into a
    > single worksheet I want to look up the data value associated with the
    > same identifier and move it next to the first set of data. For example:
    >
    > 1A 1M Convert to 1 A M
    > 3B 2N 3 B O
    > 5C 3O 5 C Q
    > 4P
    > 5Q
    >
    > The real files are much larger and the identifiers are not listed in
    > order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
    > suggests using Index and Match functions as such:
    >
    > =INDEX(F2:F21,MATCH(B1,D2:D21,0))
    >
    > But this would only return the corelate for the identifier in B1. Can
    > one somehow drag down this formula in a column B so that all B1:B2000
    > is matched or can one replace B1 with an array? or is there some other
    > way to do this?
    > Thanks,
    > Dennis
    >




  3. #3
    Max
    Guest

    Re: Match and index functions: corrlating data from 2 worksheets

    Another way to play with ..
    (Link to a sample file is provided below)

    In Sheet2, assume the source data is in A2 down, viz:

    1A
    1M
    3B
    2N
    5C
    3O
    4P
    5Q
    4X
    2W
    etc

    List the 5 identifiers across in say G1:K1, i.e.: 1,2,3,4,5

    Put in B2: =IF($A2="","",IF(LEFT($A2,1)+0=G$1,ROW(),""))

    Copy B2 across 5 cols to F2 (as we have 5 identifiers in G1:K1), fill down
    to say, F30 to cover the expected data range. Note that B1:F1 should be left
    empty, that's why we've had to "offset" the 5 identifiers list to be say,
    just adjacent to cols B to F.

    In a new Sheet3
    ----------------
    List the identifiers across in B1:F1, i.e.: 1,2,3,4,5

    Put in B2:

    =IF(ISERROR(SMALL(Sheet2!B:B,ROWS($A$1:A1))),"",MID(INDEX(Sheet2!$A:$A,MATCH
    (SMALL(Sheet2!B:B,ROWS($A$1:A1)),Sheet2!B:B,0)),SEARCH(B$1,INDEX(Sheet2!$A:$
    A,MATCH(SMALL(Sheet2!B:B,ROWS($A$1:A1)),Sheet2!B:B,0)))+1,99))

    Copy B2 across to F2, fill down to say, F30
    (cover the same range as in Sheet2)

    Sheet3 will return the listing below.
    It's what you want, except that it's transposed.
    (as it is, this list in Sheet3 may be also be acceptable?)

    1 2 3 4 5
    A N B P C
    M W O X Q

    In Sheet1
    -----------
    We'll "re-transpose" what's in Sheet3 to get the final desired listing

    Put in A1:

    =IF(OFFSET(Sheet3!$B$1,COLUMNS($A$1:B1)-1,ROWS($A$1:B1)-1)=0,"",OFFSET(Sheet
    3!$B$1,COLUMNS($A$1:B1)-1,ROWS($A$1:B1)-1))

    Copy A1 across to AD1
    (cover the same range as in Sheet3, except "column-wise")
    Fill down to AD5

    Sheet1 will return the required listing:

    1 A M
    2 N W
    3 B O
    4 P X
    5 C Q

    Here's a sample file with the implemented construct:
    http://flypicture.com/p.cfm?id=50775

    (Right-click on the link: "Download File"
    at the top in the page, just above the ads)

    File: schuldzd_wksht_2.xls

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1 22' N 103 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    <schultzd@ohsu.edu> wrote in message
    news:1116637917.742328.158240@g43g2000cwa.googlegroups.com...
    > I'm not sure the best way to do this. I have 2 workbooks (I can easily
    > combine them in one worksheet-that is not the problem). In the first I
    > have a column of identifiers and corresponding data in the adjacent
    > column. In a second workbook I have a list of identifiers, which
    > includes all the identifiers present in the first workbook, and a
    > column of data associated with them. After I combine them into a
    > single worksheet I want to look up the data value associated with the
    > same identifier and move it next to the first set of data. For example:
    >
    > 1A 1M Convert to 1 A M
    > 3B 2N 3 B O
    > 5C 3O 5 C Q
    > 4P
    > 5Q
    >
    > The real files are much larger and the identifiers are not listed in
    > order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
    > suggests using Index and Match functions as such:
    >
    > =INDEX(F2:F21,MATCH(B1,D2:D21,0))
    >
    > But this would only return the corelate for the identifier in B1. Can
    > one somehow drag down this formula in a column B so that all B1:B2000
    > is matched or can one replace B1 with an array? or is there some other
    > way to do this?
    > Thanks,
    > Dennis
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1