CODEWITHSUNDEEP
excel
KGYM
KGYM

Reputation: 31

Excel duplicate date subtraction

I have a table in Excel, with names in Column A, and dates in Column B. Names are present several times for the most part, each carrying a payment date in Column B. So if someone received one payment, the name is there once, with a date. If someone received eight, the name is there eight times, with eight different dates.

What I'm looking for is a method, to take each name (not the occurrence, but the same string), and present the difference of the maximum and the minimum date for every string that is the same (i.e. the date range of payments for every single person).

I tried basically everything in Excel. Conditional formatting and Pivot Tables did not help (the latter can only add, not subtract when using PT). Manual work would take a lot of time even if specifying min and max values for the entries, since the table has 17033 rows with 2218 unique names.

I would be grateful if you could help. I suspect the solution is not that hard, but I cannot really get my head around it.

Upvotes: 0

Views: 150

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34265

You can use a standard array formula to list the distinct names e.g. in D2:-

=INDEX($A$2:$A$10000,MATCH(1,(COUNTIF($A$1:$D1,$A$2:$A$10000)=0)*($A$2:$A$10000<>""),0))

Then another one to find the maximum for each name and subtract the minimum:-

=MAX(IF($A$2:B$10000=$D2,$B$2:$B$10000))-MIN(IF($A$2:A$10000=$D2,$B$2:$B$10000))

But these are slow with ~10000 rows - a pivot table is much faster.

I would keep things really simple by putting the minimum and maximum date as value fields in the pivot table and manually adding a formula to subtract one from the other - will post a screenshot later.

enter image description here

Upvotes: 1

Related Questions