Filter data and return unique rows from one of the columns

[mejohn]

Hello everyone!

I have the following formula that extracts columns 1,2,3,6,7 from sheet1 into sheet2.

=FILTER(INDEX(Sheet1!A:G,SEQUENCE(ROWS(Sheet1!A:G)),{1,2,3,6,7}),ISNUMBER(SEARCH(Sheet2!B2,Sheet1!F:F)))

How can the formula be modified so that only unique records in sheet1:column2 are ported over to sheet2?

Thank you in advance for taking the time to look into this!

 

[Fluff]

Maybe

=LET(Data,Sheet1!A2:G5000,f,FILTER(INDEX(Data,SEQUENCE(ROWS(Data)),{1,2,3,6,7}),ISNUMBER(SEARCH(B2,Sheet1!F2:F5000))),r,ROWS(f),FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(f,,2)=TRANSPOSE(INDEX(f,,2))),SEQUENCE(r,,,0))=1))

 

[mejohn]

Maybe

=LET(Data,Sheet1!A2:G5000,f,FILTER(INDEX(Data,SEQUENCE(ROWS(Data)),{1,2,3,6,7}),ISNUMBER(SEARCH(B2,Sheet1!F2:F5000))),r,ROWS(f),FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(f,,2)=TRANSPOSE(INDEX(f,,2))),SEQUENCE(r,,,0))=1))

This works great!

Fluff, you're an Excel Hokage Ninja!

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[mejohn]

You're welcome & thanks for the feedback.

Hi Fluff,
I'm sorry to reach out to you directly, but I was wondering if you have time to look at the following issue I'm having.

Detect peak maximum on a spectrum from the raw data

Hello everyone! I’m trying to detect a peak maximum from the raw data of a spectrum, but only if the peak rises and falls by a minimum amount. The formula I currently have will detect this, but only on sharp peaks which rise and fall by the minimum amount between adjacent row values. So, if...

www.mrexcel.com

Thank you for your time. You've been a great help!