+ Reply to Thread
Results 1 to 6 of 6

Double lookup without using vlookup?

  1. #1
    Forum Contributor
    Join Date
    05-01-2006
    Location
    Erie, Pa USA
    MS-Off Ver
    Vista - Excel 2003
    Posts
    132

    Double lookup without using vlookup?

    Col A (starting with A2) contains data that randomly repeats itself:
    A
    B
    D
    A
    B
    C etc

    Row 1 contains the Friday of the week starting on B1
    6/2
    6/9
    6/16 etc

    Where the rows and columns intersect is data
    for instance: B2 contains "Test" because A is in Test on 6/2
    C5 contains "Paint" because A is in paint on 6/9

    I need to take this data and make it so it reads on one row
    so for instance on row 1 & 2:

    blank 6/2 6/9
    A Test Paint

    Is there a way to extract the information on one row? I tried using a vlookup with a match, but it only brings in the first value.
    Thanks

  2. #2
    Biff
    Guest

    Re: Double lookup without using vlookup?

    Hi!

    Is it possible to have more than 1 entry per week per code in column A?

    Like this:

    .................6/2................6/9.................6/16
    A.............xxx..............................................
    D................................................................
    A.............xxx..............................................
    A..................................xxx.........................
    A..................................xxx.........................

    How many rows of data are there? How many columns?

    Biff

    "HBF" <HBF.29geb2_1150391705.4535@excelforum-nospam.com> wrote in message
    news:HBF.29geb2_1150391705.4535@excelforum-nospam.com...
    >
    > Col A (starting with A2) contains data that randomly repeats itself:
    > A
    > B
    > D
    > A
    > B
    > C etc
    >
    > Row 1 contains the Friday of the week starting on B1
    > 6/2
    > 6/9
    > 6/16 etc
    >
    > Where the rows and columns intersect is data
    > for instance: B2 contains "Test" because A is in Test on 6/2
    > C5 contains "Paint" because A is in paint on 6/9
    >
    > I need to take this data and make it so it reads on one row
    > so for instance on row 1 & 2:
    >
    > blank 6/2 6/9
    > A Test Paint
    >
    > Is there a way to extract the information on one row? I tried using a
    > vlookup with a match, but it only brings in the first value.
    > Thanks
    >
    >
    > --
    > HBF
    > ------------------------------------------------------------------------
    > HBF's Profile:
    > http://www.excelforum.com/member.php...o&userid=34019
    > View this thread: http://www.excelforum.com/showthread...hreadid=552336
    >




  3. #3
    Max
    Guest

    Re: Double lookup without using vlookup?

    One play to tinker with ..

    Assume source data is in sheet: X, data within A2:D20 (say)
    (B1:D1 contains col headers: 6/2, 6/9, 6/16 ...)

    It's also assumed that there's no duplicate text appearing
    for the same item in col A under any single col in cols B to D

    In a new sheet,
    Paste the same col headers in C1:E1, then

    In A2:
    =IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)>1,"",ROW()))
    (Leave A1 empty)

    In B2:
    =IF(COUNT(A:A)<ROW(A1),"",INDEX(X!A:A,MATCH(SMALL(Y!A:A,ROW(A1)),Y!A:A,0)))

    In C2, array-entered with CSE**:
    =IF(ISNA(MATCH(1,(X!$A$2:$A$20=$B2)*(X!B$2:B$20<>""),0)),"",INDEX(X!B$2:B$20,MATCH(1,(X!$A$2:$A$20=$B2)*(X!B$2:B$20<>""),0)))

    **press CTRL+SHIFT+ENTER to confirm the formula
    (instead of just pressing ENTER)

    Copy C2 across to E2
    Select A2:E2, fill down to E20

    Col B will extract the unique items from X's col A, all bunched neatly at
    the top
    C2:E20 will be populated as desired
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "HBF" wrote:
    > Col A (starting with A2) contains data that randomly repeats itself:
    > A
    > B
    > D
    > A
    > B
    > C etc
    >
    > Row 1 contains the Friday of the week starting on B1
    > 6/2
    > 6/9
    > 6/16 etc
    >
    > Where the rows and columns intersect is data
    > for instance: B2 contains "Test" because A is in Test on 6/2
    > C5 contains "Paint" because A is in paint on 6/9
    >
    > I need to take this data and make it so it reads on one row
    > so for instance on row 1 & 2:
    >
    > blank 6/2 6/9
    > A Test Paint
    >
    > Is there a way to extract the information on one row? I tried using a
    > vlookup with a match, but it only brings in the first value.
    > Thanks
    >
    >
    > --
    > HBF
    > ------------------------------------------------------------------------
    > HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
    > View this thread: http://www.excelforum.com/showthread...hreadid=552336
    >
    >


  4. #4
    Max
    Guest

    Re: Double lookup without using vlookup?

    A sample construct is available at:
    http://www.savefile.com/files/4535203
    Extract_Unique_n_Double_Lookup.xls

    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  5. #5
    Forum Contributor
    Join Date
    05-01-2006
    Location
    Erie, Pa USA
    MS-Off Ver
    Vista - Excel 2003
    Posts
    132
    Hi,

    Yes, there could be more than 1 entry per week. There are 185 rows now, but that will grow with the number of orders. The number of columns would be limited to 106 (one for each week for 2 years).


    What I'm trying to get to is this:

    ............6/2.....6/9.....6/16.....6/23
    A..........S1......S2......Test.....Paint
    B....................S1.......S2......Test....
    C..........S2......Test....Paint


    And I can get the data like this:

    .........6/2.......6/9.....6/16.....6/23
    A........S1
    C........S2
    B...................S1
    A...................S2
    C....................Test
    B.......................................Test
    C............................Paint
    A.......................................Paint


    S1, S2 are assembly stations, as are Test and Paint



    Quote Originally Posted by Biff
    Hi!

    Is it possible to have more than 1 entry per week per code in column A?

    Like this:

    .................6/2................6/9.................6/16
    A.............xxx..............................................
    D................................................................
    A.............xxx..............................................
    A..................................xxx.........................
    A..................................xxx.........................

    How many rows of data are there? How many columns?

    Biff


    >[/color]

  6. #6
    Registered User RudeRam's Avatar
    Join Date
    04-27-2004
    Posts
    8
    How can the soulution that Max gave out be used when you have 6 source sheets?
    May God have mercy upon my enemies, because I won't.
    - General George Patton Jr

+ 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