Double lookup without using vlookup?

H

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
vlookup with a match, but it only brings in the first value.
Thank
 
B

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
 
M

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
 
H

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
Click to expand...
[/QUOTE]
 
Top