Help with formula please!

emma_b_321

New Member
Joined
Feb 26, 2016
Messages
13
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
This should be relatively easy depending on the data.

Supply some example data so we can see the layout.
 
Upvote 0
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
ABCDE
1NameScoreP/FPass List
2Name 1PassName 1
3Name 2FailName 5
4Name 3FailName 7
5Name 4FailName 8
6Name 5PassName 9
7Name 6Fail
8Name 7Pass
9Name 8Pass
10Name 9Pass
11Name 10Fail
12
Passed
 
Last edited:
Upvote 0
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:

Name Date Assessment TypeOutcome
Bill Smith 15/08/2018 APass
Jay Evans15/08/2018 AFail
Liz Hyde16/08/2018 BPass

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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'.
 
Upvote 0
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
ABCDEFGHI
1NameDateAssessment TypeOutcomeNameDateAssessment TypeOutcome
2Bill Smith15/08/2018APassBill Smith15/08/2018APass
3Jay Evans15/08/2018AFailLiz Hyde16/08/2018BPass
4Liz Hyde16/08/2018BPass
5
Passed (2)
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top