Hi all,
I'm not sure my title explains this question well but I am struggling to label my data in a specific way.
I have a spreadsheet with where individuals (identified by a UR) have more than one scan in their pregnancy. Each pregnancy is identified by its due date.
I am wanting to find a formula to count the number of times a person has a pregnancy in my dataset regardless of the number of scans. This can occur 5+ times for some people.
In the example below, the UR 25016151 has two pregnancies - the pregnancy ID for the pregnancy with the due date 21/8/19 should be labelled 1 in each row with the correlating scan as it is the first pregnancy in this set, and then the second due date of 2/12/20 should be labelled 2. All other URs only have 1 pregnancy in this set and hence their pregnancy ID should be 1.
Thanks very much in advance, please let me know if any further clarification is needed!
I'm not sure my title explains this question well but I am struggling to label my data in a specific way.
I have a spreadsheet with where individuals (identified by a UR) have more than one scan in their pregnancy. Each pregnancy is identified by its due date.
I am wanting to find a formula to count the number of times a person has a pregnancy in my dataset regardless of the number of scans. This can occur 5+ times for some people.
In the example below, the UR 25016151 has two pregnancies - the pregnancy ID for the pregnancy with the due date 21/8/19 should be labelled 1 in each row with the correlating scan as it is the first pregnancy in this set, and then the second due date of 2/12/20 should be labelled 2. All other URs only have 1 pregnancy in this set and hence their pregnancy ID should be 1.
Thanks very much in advance, please let me know if any further clarification is needed!
Sample Data.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Exam date | DOB | UR | Due Date | Pregnancy ID | ||
2 | 7/25/19 | 2/18/95 | 25016151 | 8/21/19 | |||
3 | 6/27/19 | 2/18/95 | 25016151 | 8/21/19 | |||
4 | 4/12/19 | 2/18/95 | 25016151 | 8/21/19 | |||
5 | 11/4/20 | 12/2/78 | 2861802 | 12/1/20 | |||
6 | 10/6/20 | 12/2/78 | 2861802 | 12/1/20 | |||
7 | 9/8/20 | 12/2/78 | 2861802 | 12/1/20 | |||
8 | 10/28/20 | 2/18/95 | 25016151 | 12/2/20 | |||
9 | 9/10/20 | 2/18/95 | 25016151 | 12/2/20 | |||
10 | 7/22/20 | 2/18/95 | 25016151 | 12/2/20 | |||
11 | 11/5/20 | 11/8/88 | 26281844 | 12/2/20 | |||
12 | 10/21/20 | 11/8/88 | 26281844 | 12/2/20 | |||
13 | 10/8/20 | 11/8/88 | 26281844 | 12/2/20 | |||
14 | 9/10/20 | 11/8/88 | 26281844 | 12/2/20 | |||
15 | 11/6/20 | 10/19/94 | 26282656 | 12/2/20 | |||
16 | 10/23/20 | 10/19/94 | 26282656 | 12/2/20 | |||
17 | 11/2/20 | 4/17/88 | 26286627 | 12/2/20 | |||
18 | 10/6/20 | 4/17/88 | 26286627 | 12/2/20 | |||
19 | 9/16/20 | 4/17/88 | 26286627 | 12/2/20 | |||
20 | 7/15/20 | 4/17/88 | 26286627 | 12/2/20 | |||
Sheet1 |