Duplicate entries

[MEZHH]

Hi. I have an Excel file, which there is data about 2000+ people. It's the date they entry and exit from office building. Some people use their card multiple times and there are their names in excel in multiple places. I need to find their actual entry (it's the first time the name shown) and exit date (it's the last time), not during the day.

example.xlsx
	C
5	John Smith 27.01.2025 9:00
6	Shanice Raymond 27.01.2025 9:05
7	Naima Holmes 27.01.2025 9:35
8	Inaaya Richard 27.01.2025 9:12
9	Shanice Raymond 27.01.2025 10:15
10	Luna Snyder 27.01.2025 9:06
11	Isobel Myers 27.01.2025 9:18
12	Shanice Raymond 27.01.2025 11:20
Sheet1

 

[mumps]

not during the day

What do you mean by this? Explain in detail using an example from your data. In which row do your names start? Where do you want to put your desired results?

 

[jkpieterse]

Assuming Office 365...
In cell C1, enter this formula:

=LET(data,A1:A8,UNIQUE(TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),""))))

In cell D1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

In cell E1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data,,0,-1),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

Adjust A1:A8 accordingly

 

[MEZHH]

What do you mean by this? Explain in detail using an example from your data. In which row do your names start? Where do you want to put your desired results?

All employees has a card, which is using when they enter to the building and exit from building and the card reading system converts this data to an excel file. If an employee enters to the building 3 times and exits 3 times, it means his name will be on excel file 6 times. I need only first time he enters the building and the last time he exits from building.

 

[MEZHH]

Assuming Office 365...
In cell C1, enter this formula:

=LET(data,A1:A8,UNIQUE(TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),""))))

In cell D1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

In cell E1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data,,0,-1),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

Adjust A1:A8 accordingly

I'll try it. Thanks

 

[MEZHH]

Assuming Office 365...
In cell C1, enter this formula:

=LET(data,A1:A8,UNIQUE(TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),""))))

In cell D1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

In cell E1:

=LET(data,A1:A8,names,TRIM(SUBSTITUTE(data,TEXTAFTER(TEXTAFTER(data," ")," "),"")),firstOnes,XLOOKUP(C1#,names,data,,0,-1),TEXTAFTER(TEXTAFTER(firstOnes," ")," "))*1

Adjust A1:A8 accordingly

I couldn't try it. I think Office 365 functions are different than old office versions

 

[Fluff]

Xl 2024 (which your profile is showing you use) has all those functions.

 

[MEZHH]

I'll try it. Thanks

it works. Thank you very much

 

[Peter_SSs]

it works. Thank you very much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.