Share via

find first occurrence of data

Anonymous
2021-03-24T18:48:17+00:00

I found beaucoup articles on finding the first occurrence of data equal to or greater than a value.Not quite what I want.

I have a table of several hundred names with the dates of their transactions. What I want to do is find when a person made their first transaction. Can it be done?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2021-03-24T23:34:28+00:00

Hi,

Drag the first 2 columns to the row area of a Pivot Table.  Drag the third columns to the value area section.  Right click and change the summarisation function to Minimum.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2021-03-24T19:12:40+00:00

In G3 as an array formula confirmed with Ctrl+Shift+Enter:

=IFERROR(INDEX($A$3:$A$22,MATCH(0,COUNTIF(G$2:G2,$A$3:$A$22),0)),"")

In H3 as an array formula confirmed with Ctrl+Shift+Enter:

=IFERROR(INDEX($B$3:$B$22,MATCH(1,($A$3:$A$22=G3)*($C$3:$C$22=I3),0)),"")

In I3 (this is an ordinary formula):

=IF(G3="","",MINIFS($C$3:$C$22,$A$3:$A$22,G3))

Format I3 as a date.

Select G3:I3 and fill or copy down as far as you want.

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-03-25T18:03:31+00:00

    Thank you much.

    I guess it's finally time for me to learn how to use pivot tables, eh?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-03-25T18:02:53+00:00

    Thank you much.

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more