Array Formula To Return Unique Values From a Column Using a Value

[lucas813]

I have two sheets. The first sheet is used to provide an overview of the rest of the workbook. In this sheet, I would like to use an employee name, Sue, to look into worksheet two and return all of the projects Sue worked on, eliminating repeats.

Sheet one: In Cell B1 you have the name "Sue". From cell A26 down shows all of the projects Sue worked on(not repeats).

Sheet two:
A(name) B(date) C(job) D(project)
Sue 1/2/15 Drafter Building 1
John 1/2/15 Drafter Building 1
Sue 1/3/15 Drafter Building 2
Sue 1/2/15 Drafter Building 3
John 1/4/15 Drafter Building 3
John 1/5/15 Drafter Building 1
Sue 1/8/15 Drafter Building 3
Sue 1/9/15 Drafter Building 3
Sue 1/9/15 Drafter Building 2


So...Sue worked on Projects Building 1, Building 2, and Building 3.

[lucas813]

I cannot change the formatting of Sheet Two as it is an import of data from another program that needs to keep the same layout for future updates.

[oeldere]

Can you help with my topic if you have time?

http://www.excelforum.com/excel-form...ml#post3988082

You ask just 1 member to help you with your problem.

You get more change for an solution get help of all forummembers.

A good start would be to follow the advice in the link below.

http://www.excelforum.com/the-water-...-question.html

[oeldere]

Duplicated post.

Probably I'm looking for more post on my counter, since this is the 2nd duplicated post today !!!!

[FDibbins]

oeldere, not just 1 member lol...

Hello

Can you help with my topic if you have time?

http://www.excelforum.com/excel-form...ml#post3988082

[FDibbins]

Put this ARRAY formula in A26, copie down ad=nd across as needed...

=IFERROR(INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!$A$2:$A$10=$B$1,ROW(Sheet2!$A$2:$A$10)-1),ROWS(Sheet2!$A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[oeldere]

Ford, always nice to read I am not a Remy (alone at the world) !

[lucas813]

Thanks for the response. I could not get the formula to work. Please help some more. Thank you.


Test1 is without your entry, test2 is with.

[lucas813]

Also, there are currently 39,000 lines on "sheet 2". Is there a way to use Sheet2!A:A for all within the column?

[oeldere]

with an pivot table.

See the attached file.

[lucas813]

Your links take me to the "notify admin" page, but a pivot table. I will try that. Dont know why I was trying to use a formula here, I have loads of pivot tables used already. Thanks for the point in the right direction.

[lucas813]

Well, now I am having an issue of returning all values of row names where the column/row intersect contains value.

Is it possible to provide both the formula for use of the pivot table and the formula originally asked for which does a look up on the data sheet?

Thank you in advance.

[lucas813]

This is returning values, but does not eliminate repeats:

=INDEX('Employee Data'!$A:$D,SMALL(IF('Employee Data'!$A:$A='Printout Sheet'!$B$2,ROW('Employee Data'!$A:$A)),ROW(4:4))+2,4)

[XOR LX]

Hi.

Can you just clarify which of the two files you attached earlier is of interest here? And, importantly, did you make sure to include a few of your expected results in it so that it is clear what we're aiming for?

Regards

[lucas813]

Test2 contains the formula suggested by FDibbins.

The answer should say(from column D on sheet2):
UALR
UAMS
ASU
UA


These were the four projects she worked on, and should be listed.

[XOR LX]

Array formula**:

=IFERROR(INDEX(Sheet2!$D$2:$D$25,SMALL(IF(FREQUENCY(IF(Sheet2!$A$2:$A$25=$B$1,MATCH(Sheet2!$D$2:$D$25,Sheet2!$D$2:$D$25,0)),ROW(Sheet2!$D$2:$D$25)-MIN(ROW(Sheet2!$D$2:$D$25))+1),ROW(Sheet2!$D$2:$D$25)-MIN(ROW(Sheet2!$D$2:$D$25))+1),ROWS($1:1))),"")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).