The Excel Thread

YouTube · PostRe: The Excel Thread by **Stugene** » Fri Mar 11, 2022 4:38 pm

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Another question off the back of this...

I've put the same formula in two cells. The one I was testing to get it working which works, and the other where I actually want it to appear which doesn't. (You can click on the image to enlarge it)

Is there something obvious I'm missing about why this would happen?

No, Excel just does that sometimes. It's a pain in the arse.

PostRe: The Excel Thread by **Cuttooth** » Fri Mar 11, 2022 4:41 pm

That usually happens to me if the template I'm working on has that column's format set to Text.

PostRe: The Excel Thread by **Joer** » Fri Mar 11, 2022 4:44 pm

That's solved it!

Thank you everyone.

Now to spend another 45 minutes "working on it" until my shift finishes to magically have it done at 17:30.

PostRe: The Excel Thread by **Mommy Christmas** » Fri Mar 11, 2022 5:38 pm

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Another question off the back of this...

I've put the same formula in two cells. The one I was testing to get it working which works, and the other where I actually want it to appear which doesn't. (You can click on the image to enlarge it)

Is there something obvious I'm missing about why this would happen?

https://support.microsoft.com/en-us/top ... 259f2f931c

PostRe: The Excel Thread by **Sprouty** » Fri Mar 11, 2022 7:55 pm

Joer wrote:I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

There are always a ton of different ways of achieving something within Excel or programing. Stugene's method appears to work well, and whilst it's not the approach I would take, but that doesn't make it any more or less valid. I'm glad you found a solution that worked!

PostRe: The Excel Thread by **Moggy** » Thu May 26, 2022 10:49 am

I am strawberry floating useless with Excel. :lol:

I have a huge list of people and need to find the latest salary for each one. But none of them have the same dates. Example:

MembNo: 345 Mr A Jones 01/01/2020 £23,432.53
MembNo: 345 Mr A Jones 01/01/2021 £24,356.42
MembNo: 345 Mr A Jones 05/08/2022 £25,432.32
MembNo: 289 Mr B Smith 01/02/2021 £78,532.43
MembNo: 289 Mr B Smith 04/09/2021 £79,533.57

Is there a simple way to get Excel to highlight the most recent date for each unique member number?

PostRe: The Excel Thread by **Cuttooth** » Thu May 26, 2022 10:51 am

Can you just custom sort the table by name then date, newest to oldest?

EDIT - You can then remove duplicate names and the first instance in the table will be kept.

Probably better to go by member ID actually.

PostRe: The Excel Thread by **Rex Kramer** » Thu May 26, 2022 10:53 am

Moggy wrote:I am strawberry floating useless with Excel.

I have a huge list of people and need to find the latest salary for each one. But none of them have the same dates. Example:

MembNo: 345 Mr A Jones 01/01/2020 £23,432.53
MembNo: 345 Mr A Jones 01/01/2021 £24,356.42
MembNo: 345 Mr A Jones 05/08/2022 £25,432.32
MembNo: 289 Mr B Smith 01/02/2021 £78,532.43
MembNo: 289 Mr B Smith 04/09/2021 £79,533.57

Is there a simple way to get Excel to highlight the most recent date for each unique member number?

If those are a single field then you could you the text to columns function with a fixed width to separate them out and then sort by number and date as separate columns.

PostRe: The Excel Thread by **Moggy** » Thu May 26, 2022 10:55 am

Cuttooth wrote:Can you just custom sort the table by name then date, newest to oldest?

EDIT - You can then remove duplicate names and the first instance in the table will be kept.

Probably better to go by member ID actually.

Of course. I knew there was a simple way :lol:

Cheers

PostRe: The Excel Thread by **Cuttooth** » Thu May 26, 2022 10:58 am

If you want to keep the data intact you can also used the tried and true VLOOKUP after sorting it as that only ever returns the first match it comes across.

There'll be other ways too of course.

PostRe: The Excel Thread by **Moggy** » Thu May 26, 2022 11:04 am

Cuttooth wrote:If you want to keep the data intact you can also used the tried and true VLOOKUP after sorting it as that only ever returns the first match it comes across.

There'll be other ways too of course.

Your way worked perfectly so no need to muck around with VLOOKUP. Thanks!

PostRe: The Excel Thread by **Balladeer** » Thu May 26, 2022 11:07 am

If date = maxifs dates by member ID, salary, ""...

...everybody else's ways are simpler, but I like using formulae. :oops:

PostRe: The Excel Thread by **Joer** » Mon Feb 13, 2023 5:28 pm

Another bump on this thread.

We have a customer at work who gives us an 8 digit number for a product when we need a 10 digit number and they do this twice a week on around 100 items.

We have collated a list of these 10 digit product codes that they use, and want something where we can put their 8 digit numbers in, it will compare it to the list of the 10 digit numbers we have collated, and if there is a match on those first 8 digits, it will then return the full 10 digit code in a separate list so we can easily copy them. I'm thinking some sort of formula in column D that says if column A matches the first 8 digits of column B, then display column B result in a new list.

I've got a sample of the data below to show you what I mean.

Anyone know an easy way to do this?

PostRe: The Excel Thread by **Balladeer** » Mon Feb 13, 2023 5:34 pm

If A = Left(B, 8)
? What's the base format for B? Do most of them have leading zeroes?

PostRe: The Excel Thread by **Joer** » Mon Feb 13, 2023 5:37 pm

Rarely leading zeroes. It may just be those 2 in the list of 200 odd to be honest.

PostRe: The Excel Thread by **rinks** » Mon Feb 13, 2023 5:46 pm

Joer wrote:Anyone know an easy way to do this?

Yes, but the customer probably won't like it.

PostRe: The Excel Thread by **Moggy** » Mon Feb 13, 2023 5:48 pm

twitter.com/alpacaaurelius/status/1624832049738547200

Seems appropriate for this thread.

PostRe: The Excel Thread by **Tomous** » Mon Feb 13, 2023 6:01 pm

If my ancestors are so smart, then why are they dead?

Checkmate.

PostRe: The Excel Thread by **Balladeer** » Mon Feb 13, 2023 11:03 pm

Joer wrote:Rarely leading zeroes. It may just be those 2 in the list of 200 odd to be honest.

Not that it's hard to add an exception anyway, but I've just realised I'm not doing what you want regardless. Have C2 read =left(B2,8), then have D2 read:
=iferror(index(B:B, match(A2, C:C, 0), 1), "")
Then drag both down. It won't group them neatly up near the top, you'll have to do a manual filter for that or get jiggy with Visual Basic, but it'll give you a list of the matched codes from column B and empty spaces where no matches exist.

PostRe: The Excel Thread by **Joer** » Tue Feb 14, 2023 9:22 am

Balladeer wrote:
Joer wrote:Rarely leading zeroes. It may just be those 2 in the list of 200 odd to be honest.

Not that it's hard to add an exception anyway, but I've just realised I'm not doing what you want regardless. Have C2 read =left(B2,8), then have D2 read:
=iferror(index(B:B, match(A2, C:C, 0), 1), "")
Then drag both down. It won't group them neatly up near the top, you'll have to do a manual filter for that or get jiggy with Visual Basic, but it'll give you a list of the matched codes from column B and empty spaces where no matches exist.

Thanks Balladeer,

I've given that a go but the D column has stayed blank.

Any ideas where i've gone wrong?

The Excel Thread

Who is online

Affiliates