Double lookup without using vlookup?

[HBF]

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

[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

"HBF" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Max]

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
>
>

[Max]

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
---

[HBF]

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

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]

[RudeRam]

How can the soulution that Max gave out be used when you have 6 source sheets?