Help with formula please!

[emma_b_321]

I am trying to create a formula that will pull in some information from one spreadsheet to another.

The current spreadsheet shows lots of names and test results. For the new spreadsheet, I only want to pull in the names (and some additional details on other columns) of the people that have passed the testing, which is indicated by a column that says either 'Pass' or 'Fail'. My attempts so far have pulled in the names but with a load of blank rows in between but I only want to pull in the people that have passed.

Could someone please help me with this?

 

[spiralrain]

Without specifics on where each piece of relevant information is and where it's going, this is going to be pretty vague answer without actual code.

First determine the number of rows in the current spreadsheet. We'll call this LastRow.
Next determine the row number in the new spreadsheet that you want to start putting the information in. We'll call this CurrentRow.
Next Start a loop from the first applicable row of the current spreadsheet, to the LastRow.
Inside the loop, create an IF statement looking at the column with the Pass/Fail. If Fail, do nothing. If Pass, copy information from that row and the columns you want, to the new spreadsheet in CurrentRow and in the columns you want the information.
Then increase the CurrentRow by 1 and continue the loop.

I hope this helps.

 

[Special-K99]

This should be relatively easy depending on the data.

Supply some example data so we can see the layout.

 

[Peter_SSs]

See if you can apply this to your circumstances.
Formula in E2 is copied down as far as you might need.
You should then be able to use VLOOKUP or INDEX/MATCH to retrieve the other wanted data based on the list of those who have passed.

Excel Workbook
	A	B	C	D	E
1	Name	Score	P/F		Pass List
2	Name 1		Pass		Name 1
3	Name 2		Fail		Name 5
4	Name 3		Fail		Name 7
5	Name 4		Fail		Name 8
6	Name 5		Pass		Name 9
7	Name 6		Fail
8	Name 7		Pass
9	Name 8		Pass
10	Name 9		Pass
11	Name 10		Fail
12
Passed

 

[emma_b_321]

This should be relatively easy depending on the data.

Supply some example data so we can see the layout.

So the current spreadsheet looks like this:

[TABLE="width: 329"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD] Date [/TD]
[TD] Assessment Type[/TD]
[TD]Outcome[/TD]
[/TR]
[TR]
[TD]Bill Smith[/TD]
[TD="align: right"] 15/08/2018[/TD]
[TD] A[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Jay Evans[/TD]
[TD="align: right"]15/08/2018[/TD]
[TD] A[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Liz Hyde[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD] B[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]

We don't want to have to type in the name to find the other cells, it would basically pull in the name, date and assessment type only if the outcome was 'Pass'.

 

[Peter_SSs]

See if you can apply this to your circumstances.
Formula in E2 is copied down as far as you might need.
You should then be able to use VLOOKUP or INDEX/MATCH to retrieve the other wanted data based on the list of those who have passed.

So, as I said, modifying my suggestion just a little to match your circumstances & then using INDEX/MATCH

F2 copied down
G2 copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Date	Assessment Type	Outcome		Name	Date	Assessment Type	Outcome
2	Bill Smith	15/08/2018	A	Pass		Bill Smith	15/08/2018	A	Pass
3	Jay Evans	15/08/2018	A	Fail		Liz Hyde	16/08/2018	B	Pass
4	Liz Hyde	16/08/2018	B	Pass
5
Passed (2)