Filter values with different 8 digits

[Newbie73]

Hello everyone, I'm trying to find a way to filter from thousands of rows on Sheet1 only the rows where the values from Column C last 8 digits (2 letters and 6 numbers) match sheet2 B1. There will still be several duplicates, so ideally filter further from the same values the one with an older date from Column M.

Example: Sheet2 B2 = "BR746241"

Filter row 22 from B to N as "BR745706401402" last 8 digits don't match Sheet2 B2 and it's the oldest date value (Column M) from the all the rows that have "BR745706401402" on Column C.

I've created an example sheet with the expected result in Sheet2.

Test2.xlsx

Thanks again!

 

[Armstrong_N14]

Hope these helps:

LEFT, LEFTB functions - Microsoft Support

LEFT returns the first character or characters in a text string, based on the number of characters you specify. LEFTB returns the first character or characters in a text string, based on the number of bytes you specify.

support.microsoft.com

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

 

[Vogateer]

I can't click on the one drive links, so would you mind pasting some info from those sheets on here? Preferably where I can copy and paste the values instead of typing in numbers from an image.

 

[Newbie73]

Sure! First table is sheet1 and second is sheet2. Let me know if you need anything else!

BR746241501402

08/12/2020​

3 Years, 10 m

BR746241501402

18/08/2020​

3 Years, 6 m

BR746241501402

17/03/2020​

3 Years, 1 m

BR745706401402

17/12/2019​

#VALUE!​

BR746241501402

17/12/2019​

2 Years, 10 m

BR745706401402

09/09/2019​

#VALUE!​

BR746241501402

09/09/2019​

2 Years, 7 m

BR745706401402

21/05/2019​

#VALUE!​

BR746241501402

21/05/2019​

2 Years, 3 m

BR745706401402

25/02/2019​

#VALUE!​

BR745706401402

29/10/2018​

#VALUE!​

BR745706401402

18/07/2018​

#VALUE!​

BR745706401402

18/04/2018​

#VALUE!​

BR745706401402

09/01/2018​

#VALUE!​

BR745706401402

26/09/2017​

#VALUE!​

BR745706401402

04/04/2017​

#VALUE!​

BR745706401402

06/12/2016​

#VALUE!​

BR745706401402

09/08/2016​

#VALUE!​

BR745706401402

25/04/2016​

#VALUE!​

BR745706401402

02/02/2016​

#VALUE!​

BR746241

BR745706401402

02/02/2016​

#VALUE!​

 

[Vogateer]

Unfortunately I'm confused about what kind of matching you're wanting. You mention wanting to match the last 8 digits with 2 letters and 6 numbers, but none of the values in column C have 2 letters and 6 numbers for their last 8 characters. Would you mind giving an explicit example of the value you're matching, and precisely how it's matching?

Or did you want to match the last 8 digits of 06401402 from the second table?

 

[Newbie73]

Thank you for your reply and sorry maybe didn't explained correctly! I want if the 2 letters and the next 6 digits are different than Sheet2 B1, then filter those values but only the one with oldest date.

For example, below we have 3 IDs on the left. The first one has the exact first 2 letters followed by the exact 6 digits like Sheet2 B1, but the next two IDs don't, within the ID that doesn't match, so "BR745706401402" the one with oldest date is "BR745706401402" 02/02/2016 so this is the row to be filtered in the formula if possible. Same for all other IDs that don't have the exact same start as Sheet2 B1, in this case "BR746241"

Did this make more sense?

Sheet2 B1 - BR746241

Sheet1

BR746241501402

17/03/2020​

3 Years, 1 m

BR745706401402

17/12/2019​

#VALUE!​

BR745706401402

02/02/2016​

#VALUE!​

 

[Newbie73]

Anyone? I've tried the LEFT with Filter function but for some reason still can't make it work

 

[Peter_SSs]

As far as I can see in your example all the values in Sheet2 column C that do not match Sheet2 B1 are identical (all BR745706401402). Is that always the case? If not, please provide a more varied small set of sample data and the expected results and explain again in relation to that new sample data.

 

[Newbie73]

Hello Peter and thank you for your reply! That's not always the case, numbers are always very similar but frequently different.

I've added a few different examples to the example spreadsheet, and Bold the more relevant information in both sheets hope it helps but do let me know if you need anything else!

Sheet2, values from Sheet1 diffrent than Sheet2 B1 "BR746241" and the entry of the same ID with the oldest date:

BR745706401402									02/02/2016​
BR745333401402									05/04/2022​
BR745123401402									06/04/2021​

 

[Peter_SSs]

Thanks for the extra data/info.

This may need a bit more tweaking depending on the rest of the data but try it for a start. Check/adjust the ranges in my formula to match your data.

=LET(a,Sheet1!C3:O27,c,TAKE(a,,1),d,Sheet1!M3:M27,FILTER(a,(LEFT(c,LEN(B1))<>B1)*(d=MINIFS(d,c,c)),""))