Only show results where condition is met

Chibikitsuneluna

New Member
Joined
Jul 13, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hi, I am new here and unfortunately due to restrictions and data protection I can't post a mini sheet or even screenshot my workbook. I am willing to provide any/all data I can to help get an answer. I have attached images of the Headers but am unable to provide much more unfortunately.


I have a very large Workbook; where my main Worksheet, named Caseload, which uses a Table.
Caseload headers.JPG



I have another Worksheet, named Andrea; where I only want to pull Data relevant to this person.
Andrea Headers.JPG




The result I need in Worksheet Andrea, column A; is to return the CRN (column B) from Worksheet Table Caseload; only if the Appt Therapist (column AJ) in Caseload says "Andrea".


The current formula in the Andrea Worksheet that I am using in A3 is:

=IFERROR(INDEX('CEREDIGION CASELOAD'!$B$2:$B$2000,SMALL(IF(('CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1),ROW('CEREDIGION CASELOAD'!$AJ$2:$AJ$2000)-ROW('CEREDIGION CASELOAD'!$AJ2)+1),ROWS(AJ$2:AJ2))),"")



This returns the first CRN from Caseload, where the Appt Therapist in Caseload is Andrea; however it fails after that, returning seemingly random results from the list; and even returning some of these random results multiple times.

Any help will be much appreciated!!! Thanks!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about
Excel Formula:
=FILTER('CEREDIGION CASELOAD'!$B$2:$B$2000,'CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1,"")
 
Upvote 0
Solution
How about
Excel Formula:
=FILTER('CEREDIGION CASELOAD'!$B$2:$B$2000,'CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1,"")

Thanks, the data shown is correct, HOWEVER it doesn't start until A75, before that it just shows #SPILL!

Any ideas?
 
Upvote 0
You need to put the formula in a single cell (do not filldown) & clear all cells below the formula.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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