Compare rows and return unique data

Question

I have two sheets one With Complete studentregisters .  Sheet 1 lists all student data, no column has unique data such as an ientifier.. 

Classname	Student Name	Picture allowed
History Class	Donald Duck	Y
Science Class	Minnie Mouse	N
Science Class	Donald Duck	N
Math Class	Dolly Duck	Y
Math Class	Minnie Mouse	Y

So, in Sheet 2 I have listed al Classses Unique, one column only

History Class
Science Class
Math Class

What I want to do is to find all participants in the science class and list them in Sheet3 for Science Class and Return any column I want to Return.. Name, birthdate and so on. Next Sheet (Sheet 4) should be for the History Class

Classname	Student Name	Picture Allowed
Science Class	Minnie Mouse	N
Science Class	Donald Duck	N

, How do I do that?

,

Answer 1

Refer below images:

Image of Sheet1:

Image of Sheet2:

Image of Sheet3:

Enter below formula (non-array) in cell A2 of Sheet2 & copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$100,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!$A$2:$A$100&""),0,0),0))&"","")

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell B2 of Sheet3 & copy down & to the next column C:

=IFERROR(INDEX(Sheet1!B$2:B$100,SMALL(IF(Sheet1!$A$2:$A$100=$A2,ROW(Sheet1!B$2:B$100)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!B$2:B2)))&"","")

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Index_Small_4.xlsx

Regards,

Amit Tandon

Answer 2

Hi,

You may refer to my solution at this link.

Hope this helps.

Answer 3

Excel 2010/2013/2016 Power Query (aka Get & Transform)

No formulas, no VBA macro.

Group by Class, Name, add personal info.

http://www.mediafire.com/download/dr127h5i10o89i1/09_24_16.xlsx

http://www.mediafire.com/download/a1749ba33x8wzm5/09_24_16.pdf

Answer 4

Assume that, Sheet1!A1:C6 is your data table. In Sheet2!A1:C4 is your result table where you want the result.

Use the below ARRAY formula in B2 cell of Sheet2 and press CTRL+SHIFT+ENTER and copy down:

=IFERROR(INDEX(Sheet1!$B$2:$B$6,SMALL(IF(Sheet1!$A$2:$A$6=Sheet2!$A2,ROW(Sheet1!$B$2:$B$6)-MIN(ROW(Sheet1!$B$2:$B$6))+1,""),ROW($A1))),"-")

Use the below ARRAY formula in C2 cell of Sheet2 and press CTRL+SHIFT+ENTER and copy down:

=IFERROR(INDEX(Sheet1!$C$2:$C$6,SMALL(IF(Sheet1!$A$2:$A$6=Sheet2!$A2,ROW(Sheet1!$C$2:$C$6)-MIN(ROW(Sheet1!$C$2:$C$6))+1,""),ROW($A1))),"-")

The result is like below: