Formula to handle data

**SmallJack** · 01-14-2010, 08:23 AM

Hi there,

Would need your expertise to advice on how to create a formula to handle the following data in order to produce the desired output below.

I have some data in Sheet 1 and the corresponding data in Sheet2. The output is in Output Sheet.

If the data in Sheet1, "Student1" match the data in Sheet2, "Student1", then it will copy all the entries for Student1 in other worksheet with the header "Student1" (Output Sheet).

The same thing goes to other Student in the list. For this sample, I only provide a short list of data in Sheet2, it can be long, more than 5 students.

Please refer to the attached file for sample data.

Sheet1
------------
Student1 | Student2 | Student3 | Student4 | Student5

Sheet2
------------
Student2 | Student5 | Student3 | Student4 | Student1
aaa | aaa | fff | aaa | aaa
ddd | fff | ccc | ccc | bbb
eee | ccc | ccc | ddd | ccc
ccc | ddd | ddd | eee | ddd
eee | eee | eee | bbb | eee
bbb | bbb | aaa | fff | fff

Output:
-------------
Student1 | Student2 | Student3 | Student4 | Student5
aaa | aaa | fff | aaa | aaa
bbb | ddd | ccc | ccc | fff
ccc | eee | ccc | ddd | ccc
ddd | ccc | ddd | eee | ddd
eee | eee | eee | bbb | eee
fff | bbb | aaa | fff | bbb

Would appreciate if you could help to produce a formula to handle this data.

Thanks a lot in advance.

- Peter

**blane245** · 01-14-2010, 09:15 AM

Here's a solution involving a formula in each cell of the output worksheet. You will have to extend the formula to each column and row that contains data in Sheet2. Book1.xls

**Palmetto** · 01-14-2010, 09:54 AM

Another option that doesn't use volatile functions.

Formula to pull the student names:
=INDEX(Sheet2!$A$1:$E$1,1,MATCH(Sheet1!A$1,Sheet2!$A$1:$E$1,0))

Formula to pull the student data:
=INDEX(Sheet2!$A$2:$E$7,ROW()-1,MATCH(Output!G$1,Sheet2!$A$1:$E$1,0))

**SmallJack** · 01-15-2010, 09:24 PM

Hi All,

This is great.

Thanks a lot for your response.

I will check and test the formula.

- Peter

**sharp_1** · 01-16-2010, 01:43 PM

I have a worksheet drop down field with account names that are pulled from Sheet1 which I call "accounts". What I want to do is have a specific text result in Sheet2 if the text in Sheet2 matches Sheet1>Column.

Here is what I have been messing with:

=IF(C5=Table1[[#All],[Holding]],"Holding","Pay")

Now C5 in my above formula returns desired result when C5 matches with A5 in Table from my Accounts worksheet. I need entry from C5 that matches any data from Table1 in Sheet1 to return my specific result.

Hope that makes sense!!

**Palmetto** · 01-16-2010, 03:38 PM

sharp_1,

Your post does not comply with Forum Rule #2.

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Do not hijack the thread of another user. Post your question in a thread you start.