Hi all, I have some data that has many information including personal id and a start year. The personal id can be repeated throughout the data, but per personal id, there is only a unique start year, such as this: Data Personal IDStart Year00012010000120110002201200022013 I am trying to combine the data so the personal id is not repeated, and I get a range of years per personal id, so end up with something like this: Output Personal IDYears00012010-201100022012-2013 Anyone know how to do this? Is it possible?? Thanks in advance!

Combining Data on Value and Date

Hi all,

I have some data that has many information including personal id and a start year. The personal id can be repeated throughout the data, but per personal id, there is only a unique start year, such as this:

Data

Personal ID	Start Year
0001	2010
0001	2011
0002	2012
0002	2013

I am trying to combine the data so the personal id is not repeated, and I get a range of years per personal id, so end up with something like this:

Output

Personal ID	Years
0001	2010-2011
0002	2012-2013

Anyone know how to do this? Is it possible?? Thanks in advance!

Output

Date Time

Accepted answers

Christine_Assaad

Hi @pablomtz28

Please see below a way to do it.

Min Max.yxmd

Christine_Assaad

Hi @pablomtz28

Is this what you are looking for?

Min Max_2.yxmd

All comments

Lyser

could you use the summarize tool to group by Personal ID, and calculate the MIN and MAX start years?

you could then use the formula tool to create the a field containing the MIN and MAX as a range.

Christine_Assaad

Hi @pablomtz28

Please see below a way to do it.

Min Max.yxmd

pablomtz28

Thank you very much, that was very helpful!

I have another follow up question, I dont know if you might know the answer to this, as I found a better way to approach this problem I had.

I am dealing with the same data, but instead of dealing with ranges as mentioned before, I want to add five new columns that relate to the years 2016-2020 and simply add a boolean per Personal ID to check wether they were a part of the event that year.

Input:

Personal ID	Year
0001	2016
0001	2017
0001	2020
0002	2018
0002	2019
0002	2020

Output:

Personal ID	2016	2017	2018	2019	2020
0001	Yes	Yes	No	No	Yes
0002	No	No	Yes	Yes	Yes

Is it possible maybe?? Thanks in advance!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335