• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Values Problem

Kenshin

Member
It is nice to see you again ninjas, need help from you again, hereby my excel file, thanks in advanced
 

Attachments

  • Problem.xlsx
    26.2 KB · Views: 8
Hi:

You can use the following formula
=INDEX($A$3:$A$957,AGGREGATE(15,6,(ROW($B$2:$B$956)-MIN(ROW($B$2:$B$956))+1)/($E$3=$C$2:$C$956),ROW(A1)))

Note: Repeat the same for shift and code number columns by changing the reference.

Also remove the duplicates from the main data set using the remove duplicate excel function before applying the formulas.

Thanks
 
Hi:
The calculation in your file you had set to manual either make it automatic or press function key F9 for the formula to work.

Thanks
 
Try this array formula with remove duplicates on Shift Column.

In E4, array formula copy across and down :

=IFERROR(INDEX(A$1:A$956,SMALL(IF(($C$1:$C$956=$E$3)*MATCH($A$1:$A$956&$B$1:$B$956&$C$1:$C$956,$A$1:$A$956&$B$1:$B$956&$C$1:$C$956,)=ROW($A$1:$A$956),ROW($A$1:$A$956)),ROWS($1:1))),"")

p.s. array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together

Regards
Bosco
 

Attachments

  • VlookupProblem (1).xlsx
    31.8 KB · Views: 12
Back
Top