Custom function to extract data from table

[RaMa87]

Hi all,

I have a table with a date and an identifier field.

The identifier field can have unique values as well as multiple entries of a unique identifier (ID).

Simplified:

Date -> ID1

Date -> ID2*

Date -> ID2'

Date -> ID3*

Date -> ID3#

Date -> ID3'

What I am interested in is the time frame between the first (e.g. ID2* or ID3*) and last (e.g. ID2' or ID3') mention of an identifier, i.e. unique values (like ID1) and values in between the first and last identifier (like ID3#) need to be disregarded/deleted.

Is there any way I can do this?

I know that I can simply subtract the dates within excel but I am struggling with removing the unique lines and the lines in between the first and last mention of an identifier.

An example file is attached. The table I have has a lot more rows (27.000) and Identifiers can be mentioned up to 8 times in the table.

Thanks so much in advance and best wishes

[#2 · Accepted solution]

@RaMa87 I used three queries. First one is simply a direct copy of the table, sorted ascending on date (qryStartDates), Second is identical but sorted Descending on date (qryEndDates). Third is a full join of 1 and 2 on the ID column, filtered out the ones where dates from 1 and 2 are the same, then subtracted both dates.

[#3]

Brilliant! Thank you so much!

I additionally removed duplicates and transformed the output to only contain positive values (absolute number function) --> exactly what I was looking for.

Along those lines I came across a similar but rather more difficult issue (also maybe difficult to explain)... Do you have by chance a similar brilliant solution?

I have a similar table with a date, an identifier and a number (example attached).

At the moment the original data is sorted by date (i.e. pretty much scrambled up).In theory the data needs to be sorted by date AND by ID - then I am interested in the 'Number' tab.

The number can (but does not necessarily) start with 0 and increases over time (date) for each ID until it drops again to 0.

I am looking for a solution which only returns the number before a 0 for an identifier.

(Hopefully the example explains it a bit better)

I searched for a solution quite some time and did not come up with a smart one:-(

[#4]

@RaMa87 Sure. I used this trick twice to get data from the next record: https://www.linkedin.com/pulse/get-transform-rescue-wyn-hopkins/

[#5]

@Jan Karel Pieterse Thanks! that helps a lot :)