index / match /lookup ? help

A

andrewm

Hi

I am trying to change sheet 1 into sheet 2 form

ie. sheet one is


date 15/7 16/7 ...
andrew E R
john R E
mary w R
Jim R w etc

to

date E W R R
15/7 andrew mary john jim
16/7 john jim andrew mary etc

I know what to do with the E and W's (as there are only one ), but
problem
with R's

andrewm
 
D

Domenic

Assumptions:

Sheet1!A1:C5 contains your source data

Sheet2!A2:A3 contains 15/7 and 16/7

Sheet2!B1:E1 contains E, W, R, and R

Formula:

Sheet2!B2, copied across and down:

=INDEX(Sheet1!$A$2:$A$5,SMALL(IF(INDEX(Sheet1!$B$2:$C$5,0,MATCH($A2,Sheet1!$B$1:$C$1,0))=B$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),COUNTIF($B$1:B$1,B$1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
A

andrewm

Hi,

Sorry

sheet 1 contains all of -
A B C
1 date 15/7 16/7 ...
2 andrew E R
3 john R E
4 mary w R
5 Jim R W etc

sheet 2

to A B C D
E W R R
15/7 andrew mary john jim
16/7 john jim andrew mary etc



andrewm
 
D

Domenic

I don't quite understand. Can you elaborate?
Hi,

Sorry

sheet 1 contains all of -
A B C
1 date 15/7 16/7 ...
2 andrew E R
3 john R E
4 mary w R
5 Jim R W etc

sheet 2

to A B C D
E W R R
15/7 andrew mary john jim
16/7 john jim andrew mary etc



andrew
Click to expand...
 
A

andrewm

Hi,

Sorry

sheet 1 contains all of -
column a column b column c column d
date 15/7 16/7 ...
andrew E R
john R E
mary W R
jim R W


it is a roster for 4 people showing what they are doing in each day
either E,R, or W

I would like to rearrange it to sheet 2 showing

column A column B column C column D
column
E W
R R
l
15/7 andrew mary john
jim
16/7 john jim
andrew mary etc

andrewm
 
D

Domenic

My solution will provide you with the results you're looking for. Have
you tried it?
 
A

andrewm

Hi, Dominec and others

I have tried the array formula

(copied and used control shift enter)

did not work

what am i doing wrong

(could you attach an example)

many thanks

andrewm
 
D

Domenic

Since I don't have a utility to zip files, I won't be able to attach a
sample. But, if you'd like, I can email you one. If you're
interested, send me your email address via Private Message.
 
Top