Finding the first instance in a column and returning value

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
677
Office Version
  1. 365
Platform
  1. MacOS
Hi. In my file (link below) and worksheet 'Completed Unique E(V)', is there a way to make it auto-populate column B (and subsequently the other columns), rather than the manually entered entries I have at the moment? I have tried various ifs, matches and vlookups etc. but can't seem to get it to work. I just want it to pick up the first instance (i.e. all those that have 1 in column AO in the ‘All Completed Runs’ worksheet) of each event from column C in the ‘All Completed Runs’ worksheet. So I then have a list of my unique events, as per the manual entries now. I assume I then just offset or something to populate the other cells on each row?

parkrun - My parkrun Record - WORKING.xlsx

Thanks!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
What version of Excel are you using?
If you edit your account details, you can show this in your mini-profile, which saves members having to ask. :)
 
Upvote 0
What version of Excel are you using?
If you edit your account details, you can show this in your mini-profile, which saves members having to ask. :)
I have Office 365 on Mac. Thanks, how dod I add to my profile?
 
Upvote 0
If you click on your avatar (top right of the board) you will see Account details
1585656382133.png


If you select that, you will see the various office versions & platforms, just check those that apply
 

Attachments

  • 1585656361088.png
    1585656361088.png
    40.2 KB · Views: 1
Upvote 0
How about
=FILTER(FILTER('All Completed Runs'!C3:AH2002,'All Completed Runs'!AO3:AO2002=1),(COLUMN('All Completed Runs'!C3:AH3)<=5)+(COLUMN('All Completed Runs'!C3:AH3)=29)+(COLUMN('All Completed Runs'!C3:AH3)=30)+(COLUMN('All Completed Runs'!C3:AH3)=34))
 
Upvote 0
Another option to keep the hyperlinks

Cell Formulas
RangeFormula
A3A3=IF(B3<>"",1,"")
B3:B10B3=IFERROR(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(B$3:B3)),1),"")
C3:C10C3=IFERROR(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(C$3:C3)),2),"")
D3:D10D3=IFERROR(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(D$3:D3)),3),"")
E3:E10E3=IFERROR(HYPERLINK(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(E$3:E3)),27)),"")
F3:F10F3=IFERROR(HYPERLINK(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(F$3:F3)),28)),"")
G3:G10G3=IFERROR(INDEX('All Completed Runs'!$C$3:$AI$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$C$3:$C$2002)-ROW('All Completed Runs'!$C$3)+1)/('All Completed Runs'!$AO$3:$AO$2002=1),ROWS(G$3:G3)),32),"")
A4:A10A4=IF(B4<>"",A3+1,"")
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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