Help with formulas

[Lmaonade]

I have a spreadsheet that I need to pull certain information from. This spreadsheet has thousands of rows.

I want to make a seperate sheet for each candidate. So for example, I have created a sheet for Farah Noor.

I want to be able to pull off each Contract reference number from my main spreadsheet and list them all here, I also would like it if there were no duplicates.

Can someone tell me the formula I would need to use to pull this data?

Thanks.

 

[Special-K99]

Try

In Farah Noor!B4
=IFERROR(INDEX('Eclipse Report'!$C$2:$C$100000,SMALL(IF(('Eclipse Report'!$A$2:$A$100000=B$1)*('Eclipse Report'!$B$2:$B$100000=B$2),ROW($A$2:$A$100000)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter

and copy down for n rows where n is the maximum number of contracts you would expect to Farah to have

NOTE: You've spelt Forename inccorrect in Farah Noor!A1

 

[Lmaonade]

Hi Special-K99,

Thank you for the help.

I've pasted this formula into B4 it was coming up with her name rather than the contract number, I changed the "INDEX('Eclipse Report'!$C$2:$C$100000" part to D2 - D100000

that seemed to work. I then tried to do the same on a new sheet for a new candidate using the same formula. The cells are now coming back blank.

Do you know what I'm doing wrong?

 

[Special-K99]

Oops, yep column D is correct.

First check the new candidate does actually appear in the Eclipse Report.
Ensure the formula has been entered as an array formula

Otherwise, could you post the file?
You cant actually post files on this forum but if you post it to an online storage site you can put a link to it here.

Any moderators examining this thread will be ready to blow their tops (at me) as they prefer people post an image in preference to a file elsewhere, however in order to solve this we need to know what the data actually IS not what it looks like in an image so I feel I'm perfectly ok in asking you to post a link to a file in this case.

You could always PM me the link I guess?

 

[Lmaonade]

Hi Special-K99,

Actually that was my error. For some reason the report I had with the Candidate names in it, some of the names were saved with a space after them, meaning if I was making a sheet with one of those candidates I'd have to search them with a space after. (I've fixed that now in the software I pull the report from)

Now I have another quick question. I've pulled all the data from that sheet that I need, now I want to look up in a new sheet if the timesheet number matches one in that sheet, if it does I want to pull across more information. I'll include some screenshots to show what I mean more clearly (As I can't seem to explain it clearly)

In cell G5, I want to put a formula telling me that if cell c5 matches any of the ts numbers in this other sheet:

then I want it to copy over, for example... the cand reference (Colomn C) into that cell.

Sorry if it seems complicated, it actually may seem simple to you.

Thanks in advance.

 

[Special-K99]

Extend the INDEX to cover those columns and amend the last digit in the formula to extract the column you want.

=IFERROR(INDEX('Eclipse Report'!$C$2:$C$100000,SMALL(IF(('Eclipse Report'!$A$2:$A$100000=B$1)*('Eclipse Report'!$B$2:$B$100000=B$2),ROW($A$2:$A$100000)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter

So $C$2:$E$100000
and column will now be 3

Array formulas are slow and if you have a lot of them the worksheet may slow down but apart usuing from VBA sometimes it's the only way.

 

[Lmaonade]

Hi Special-K99, was that an answer to my last question regarding the GM Report sheet?

I am not sure if I understood

 

[Gerald Higgins]

Hi, I know I'm coming to this late, but . . .

In the OP you say you "want to make a separate sheet for each candidate".

By all means do that, but if your data has thousands of rows, as you say, then you're potentially going to end up with hundreds / thousands of sheets.

If you want to have the ABILITY to view a single person's data all at once, on a separate sheet, consider using one single sheet that pulls in the relevant data for whichever single candidate you're interested in.
If you need to show data for all candidates in some special format, then again, consider one single sheet that perhaps duplicates the main data sheet, but does it in your special format.

Any solution that involves creating hundreds of sheets is not a good solution in my opinion.

 

[Special-K99]

Sorry I didnt read that properly.

Looks like you need a INDEX('GM Report'!range,MATCH(C5,'GM Report'!E$2:E$100000,0),0)

Put the range of the column you want to extract, e.g C$2:C$100000 for Card ref