Filter data based on columns, display unique row data

I have a long list of data like this.

PHP Code: 

ColumnB    ColumnC        ColumnD     ColumnE 
Name        Work Order     Date        Job Commission 
Alex        23816-1        5/6/13      $45.24 
John        21083-1        5/6/13      $22.20 
John        23643-1        5/6/13      $76.08 
Alex        23929-1        5/7/13      $45.00 
John        23965-1        5/7/13      $41.55 
Debr        23983-1        5/7/13      $30.00 
John        23984-1        5/8/13      $49.20 
Alex        24011-1        5/8/13      $30.00 
Debr        24030-1        5/8/13      $30.00 
Mike        21397-1        5/9/13      $107.52 
Sean        24098-1        5/9/13      $102.00 


I need a way to search an entire column and display the rows with the matching cell (in this case "Alex"). As you can see column C, D and E all common unique information.

PHP Code: 

ColumnB    ColumnC        ColumnD     ColumnE 
Alex        23816-1        5/6/13      $45.24 
Alex        23929-1        5/7/13      $45.00 
Alex        24011-1        5/8/13      $30.00 


What's the best way to achieve this?

Hello,

The easy way would be creating a helper column using COUNTIF that distinguish a row being unique or not, and then you can filter on that column. Of course you can always add a condition on it to make the COUNTIF to only count when the value in Column B matches what you are looking for (In this case, "Alex").

The rough method should be using Array formula to return all unique rows, though if your file has a large number of data, this method is not really encouraged.

My proposed idea involves the use of COUNTIF to count the number of rows that will have the exact same data in all four column B, C, D and E at the same time. If the result is 1, the row should be unique, otherwise it is duplicated.

Can you provide a small sample file with dummy data? It will be much easier for others to help you also.

This is turning out different than I thought.

You can try using a filter, and by choosing the name on column A, no mater which rows has that name, it will show itself in a neat table, like the attached file.

However, if you want a more advanced methods, you can try the Array method - just change the name in G1 and see the list update itself. Note that you will have to drag the formula down as far as you see a lot of "blank".

Also note that for the sake of testing, I have messed your table up.

Try this...

enter smith, John in E2
Then in f2, copied down, use this regular formula...
=IFERROR(INDEX(OFFSET(INDEX($B$2:$B$71, MATCH($E$2,$A$2:$A$71, 0)),,, COUNTIF($A$2:$A$71, $E$2), ), ROW(A1)), "")
for the subsequent columns, copy that across, and change the $B$2:$B$71 to c, d etc

I suggest you try the solution from FDibbins, that is a really good one.

About the Array method, because they are Array formula, after you paste them, you will have to click on the formula bar, then hold Ctrl-Shift and hit Enter (Confirm with CSE). Afterward, if the formula is being wrapped inside a { } like in the sample, you did it right.

All you have to do is copy the formula on A2, paste it on your real workbook, make it Array (wrapped inside { } ), drag it to D2, then Select all A2 to D2, drag it down as far as you see blank.

ROW(A1) is 1 of te ways to make a formula increment certain arguments.

ROW(A1) = 1
ROW(A2) = 2
etc

Lets try a different approach.

On sheet1 V2, use this, copied down...
=A2&COUNTIF($A$2:A2,A2)

Then on sheet3, similar to Daily, put the name you want in B1, and in C1:E1, the same headings as Daily. In C2, copied down, use this...
=IFERROR(INDEX(Sheet1!F:F,MATCH(Sheet3!$B$1&ROW($A1),Sheet1!$V:$V,0),1),"")
Change F:F to match the other columns you want to extract

If you make the headings on each sheet identical, you can then use this instead, and copy it across...
=IFERROR(INDEX(Sheet1!$A:$U,MATCH(Sheet3!$B$1&ROW($A1),Sheet1!$V:$V,0),MATCH(Sheet3!C$1,Sheet1!$A$1:$U$1,0)),"")