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


--
HBF
------------------------------------------------------------------------
HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
View this thread: http://www.excelforum.com/showthread...hreadid=552336

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" wrote in message
...

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

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

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

Double lookup without using vlookup?
 

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



Biff Wrote:
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]


--
HBF
------------------------------------------------------------------------
HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
View this thread: http://www.excelforum.com/showthread...hreadid=552336

Double lookup without using vlookup?
 

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


--
RudeRam


------------------------------------------------------------------------
RudeRam's Profile: http://www.excelforum.com/member.php...fo&userid=8818
View this thread: http://www.excelforum.com/showthread...hreadid=552336