How to return multiple values for a single criteria

[Mangisqa]

Hello ,
I have been doing a lot of searching and haven't been able to find the right anwer to help me:

I have a workbook with two worksheets. Worksheet 1 is a paste in report from our system of record that lists the dates that people have taken classes. I want to be able to have a list on worksheet 2 all the dates an employee attended classes. The common criteria is an employee number. I have tried the help section on office and the formula doesn't seem to meet my needs. I also want to be able to drag the formula across the row if needed not down a column.

Worksheet 1
Employee number. Class date
1 1/30/2011
2 2/24/2011
3 1/24/2011
2 3/2/2011
1 4/1/2011
3 2/15/2011
2 3/1/2011


Worksheet 2
Employee number. Dates attended
1.
2
3


There is more information but these are the columns I a
Worried about for this problem

Thanks in advance!!!!!

 

[kidwispa]

You could use a pivot table - highlight all your data, go to Insert and then click on PivotTable. When this creates in a new sheet drag both fields into the Row Labels section - this shows like this:

 

[Mangisqa]

Thanks! But the pivot table messes up true formatting for the rest of the information I need. Is there a formula so it is all in one line?

 

[Aladin Akyurek]

Hello ,
I have been doing a lot of searching and haven't been able to find the right anwer to help me:

I have a workbook with two worksheets. Worksheet 1 is a paste in report from our system of record that lists the dates that people have taken classes. I want to be able to have a list on worksheet 2 all the dates an employee attended classes. The common criteria is an employee number. I have tried the help section on office and the formula doesn't seem to meet my needs. I also want to be able to drag the formula across the row if needed not down a column.

Worksheet 1
Employee number. Class date
1 1/30/2011
2 2/24/2011
3 1/24/2011
2 3/2/2011
1 4/1/2011
3 2/15/2011
2 3/1/2011


Worksheet 2
Employee number. Dates attended
1.
2
3


There is more information but these are the columns I a
Worried about for this problem

Thanks in advance!!!!!

Let A1:B8 on Sheet1 house the sample you posted, the headers included.

Sheet2

A2: 1
A3: 2
A4: 3

B2, just enter and copy down:

=COUNTIF(Sheet1!$A$2:$A$9,A2)

C2, control+shift+enter, not just enter, copy across, and then down:

=IF(COLUMNS($C2:C2)<=$B2,
    INDEX(Sheet1!$B$2:$B$9,SMALL(IF(Sheet1!$A$2:$A$9=$A2,
      ROW(Sheet1!$B$2:$B$9)-ROW(Sheet1!$B$2)+1),
        COLUMNS($C2:C2))),"")

 

[Mangisqa]

Perfect!

 

[Aladin Akyurek]

Perfect!

You are welcome. Thanks for providing feedback.