Nov 03 2020 09:50 AM
In the table below I require to find formula to fill out column C and D, since I have thousand of records to filter through
COLUMN A has duplicate ID which I need to identify the distinct ID in COLUMN D as shown
Number of days for each distinct ID from start to finish which is in Step Date (column B)
example: 10000523052 is from 4/30/2020 - 7/27/2020 = 88 days; For every Unique ID, Days(Last Entry date - First Entry Date)
I want this identified for each ID in column D as shown.
What should be the formula for Column D and Column C to show Unique ID and days between first entry and last entry for the specific ID at the top entry?
A | B | C | D |
ID | Date | Distinct ID | Days |
10000523052 | 7/27/2020 | 10000523052 | 88 |
10000523052 | 7/27/2020 | ||
10000523052 | 7/27/2020 | ||
10000523052 | 4/30/2020 | ||
10000523052 | 4/30/2020 | ||
10000523088 | 7/28/2020 | 10000523088 | 89 |
10000523088 | 7/28/2020 | ||
10000523088 | 7/15/2020 | ||
10000523088 | 4/30/2020 | ||
10000523088 | 4/30/2020 |
Thanks for your help in advance.
Nov 03 2020 10:53 AM
Here's a method. I'll be curious if other more elegant solutions are proposed.
This does presume that you've sorted all of the records by the first column (the ID)... Assuming that's the case, then this formula
=IF(A3<>A2,A3,"")
will populate column C with the new ID each time it changes; otherwise leave it blank.
And then, for each row in column C that has a new ID, this formula in column D subtracts the minimum date from the maximum date for the ID in the adjacent column.
=IF(C3="","",MAXIFS($B$3:$B$11,$A$3:$A$11,C3)-MINIFS($B$3:$B$11,$A$3:$A$11,C3))
See the attached
Nov 03 2020 11:01 AM
Here's a more elegant solution, in that it will list all the Unique IDs all bunched together...you could even put these formulas on their own page, apart from the database itself.
The use of UNIQUE does require the most recent edition of Excel. If you have that edition, you will find this preferable to the prior solution.
Put this wherever you want it. I illustrate it in Column C, but it could be placed elsewhere.
=UNIQUE(A3:A11)
UNIQUE will "spill" the results down as far as necessary to produce a complete list, so there should be no other content below it.
This formula remains as it was before, and should be adjacent to the foregoing.
=IF(C3="","",MAXIFS($B$3:$B$11,$A$3:$A$11,C3)-MINIFS($B$3:$B$11,$A$3:$A$11,C3))
Nov 03 2020 11:39 AM
@mathetes Thanks Mathetes. the suggested formula did not work; the result of the formula was 0.
Also looking for a formula for D3 to compute the number of days between the first date for the ID and last date for the same ID.
Nov 03 2020 11:51 AM
@bxsilva1740 If you are on a modern version of excel, perhaps the attached sheet answers your question.
Nov 03 2020 12:07 PM
In your file ID are numbers, and Distinc ID are texts first character of which is non-breaking space (unicode 160), most probably they are copy/pasted from Web.
For this concrete sample that could be fixed as
=MAXIFS($B$3:$B$11,$A$3:$A$11,SUBSTITUTE(C3,UNICHAR(160),"")*1)-
MINIFS($B$3:$B$11,$A$3:$A$11,SUBSTITUTE(C3,UNICHAR(160),"")*1)
Nov 03 2020 01:26 PM
I attached spreadsheets in both cases that DID work. I just posted the formula to make the posting more educational.
The second example, using UNIQUE, will not work, as I said, unless you have the newest versions of Excel.
Nov 03 2020 02:46 PM
Nov 03 2020 03:29 PM
As variant
C3:
=IFERROR(
INDEX($A$3:$A$11,
AGGREGATE(15,6,1/(COUNTIF($A$3:A3,A3)=1)*(ROW()-ROW($A$2)),1)
),
"")
D3:
=IFERROR(
AGGREGATE(14,6,1/(COUNTIFS(C3,$A$3:$A$11))*$B$3:$B$11,1)-
AGGREGATE(15,6,1/(COUNTIFS(C3,$A$3:$A$11))*$B$3:$B$11,1),
"")
and drag down
Nov 04 2020 05:06 AM
Nov 04 2020 05:07 AM