Index small - not finding all data in table

[simmo6477]

Hi,

I have a worksheet (Faculty Data Received) into which I call data from two other workbooks into its rows; included in the data are names of staff at Col A and Dates at Col B. I need to extract the data into a separate sheet for the ten latest (by date) entries for each member of staff - I am using this for the 'earliest' date and then have 9 other equations which capture 2nd smallest, 3rd smallest etc on rows below it (I have shown the first 2):

{=INDEX('Faculty Data Received'!$B$1:$B$1500,SMALL(IF('Faculty Data Received'!$A$1:$A$1500=A1,ROW('Faculty Data Received'!$A$1:$A$1500),""),1),1)}

{=INDEX('Faculty Data Received'!$B$1:$B$1500,SMALL(IF('Faculty Data Received'!$A$1:$A$1500=A1,ROW('Faculty Data Received'!$A$1:$A$1500),""),2),1)}

My problem is that where the dates are drawn from one of the two other workbooks it appears lower down on the Faculty data Received worksheet and the INDEX SMALL function is not picking up these entries.

I hope I have made this clear and that somebody may be able to help.

Many thanks in advance.

[Tony Valko]

The 10 latest dates...

Is that the 10 most recent dates or the 10 oldest dates?

[simmo6477]

Hi Tony,

I need the 10 most recent dates please.

Cheers

[Tony Valko]

Try this...

Let's assume you want the results starting in cell B1.

This array formula** entered in B1 and copied down to B10:

=LARGE(IF('Faculty Data Received'!A$1:A$1500=A$1,'Faculty Data Received'!B$1:B$1500),ROWS(B$1:B1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

[simmo6477]

Thanks very much Tony...

this has worked for my first member of staff BRILLIANTLY although I get a NUM! error where there is no date (I'm sure I'll be able to work out how to rectify that), but when I move on to the information for the next or subsequent members of staff it doesn't quite like it - I should have explained that I'm drawing the 10 most recent entries for all staff members...following is what I now get (BI and EN are different members of staff):

BI 19/05/2012
BI 19/01/2012
BI #NUM!
BI #NUM!
BI #NUM!
BI #NUM!
BI #NUM!
BI #NUM!
BI #NUM!
BI #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!
EN #NUM!

are you able to advise how I might amend the formula for the second and subsequent members of staff please?

[Tony Valko]

Can you post a SMALL sample file so I can see what you're trying to do?

A SMALL file is less than 50kb in size! I won't open files that are bigger.

[simmo6477]

Hi Again Tony,

I have attached a much-reduced workbook and very much appreciate your assistance.

[Tony Valko]

What version of Excel are you using? The sample file is in *.xls format and your profile data says you're using Excel 2010.

The reason I ask is that we need to trap errors and the best way to do that depends on what version of Excel you're using.

[simmo6477]

Hi Tony,

Had only just noticed that...file was created in Excel 2010 at work...brought it home and am using Excel for Mac 2011...have now saved the 'master' file as .xlsx format. Hope hat makes sense.

Cheers

[Tony Valko]

OK, try this array formula** entered in B1 and copied down:

=IFERROR(LARGE(IF('Faculty Data Received'!A$2:A$24=A1,'Faculty Data Received'!B$2:B$24),COUNTIF(A$1:A1,A1)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[simmo6477]

BINGO!! Thanks, mate...first class as you even included ISERROR to deal with my 'no dates'!!

Could I really push it now and ask if it would be possible to include adjacent data (e.g. cols to the right) of the date from the 'Faculty Data Received' worksheet alongside the 'top 10 dates' which you have just solved for me? If not, I understand and will muddle through, I'm sure.

Cheers

simmo6477