0

This might be an unusual request since I couldn't find anything like this online but here it goes.

I have a table of data like this:

Image of Table Basically, I have a list of people, list of questions they asked and the date that they asked. I want to be able to identify the first time they asked a question based on the date because a single person has asked multiple questions on different dates.

I was thinking of creating a new column with some formula to identify duplicates but not sure how to do it by the criteria of first occurrence by date.

What I'm trying to do is to create a graph/chart to show the number of NEW PEOPLE asking a question over time. Right now I have the total questions being asked over time but I want to find out how many new people are asking questions on a weekly basis.

Would love any help! Thanks :)

1
  • Perhaps can you show a sample output table that you have in mind?
    – patkim
    Nov 29, 2017 at 8:44

2 Answers 2

0

Copy the data table. Sort the data by the date column ascending. Select the column with the names, then click the Data ribbon "Remove Duplicates". Select the Name column and click OK.

enter image description here

enter image description here

The result shows each unique name with the first date that they appeared in the list.

2
  • Thanks so much, this is useful! But this works only if Im doing it one time. I have a model that I put data into and would like for it be shown through a Pivot chart which is why I was wondering if there is something more dyanmic!
    – Bahtfeng
    Nov 29, 2017 at 9:23
  • You could use Power Pivot to perform similar steps and automate the process.
    – teylyn
    Nov 29, 2017 at 20:47
0

See if this works for you. In this example sample data is in cells B3:D100. The output table is columns F3:G3 and below.

Leave at least one cell for reference above this table. Usually you will have a header row and that should suffice. Do not start output table data directly in Row 1, the formula will not work correctly in that case.

In F3 put the following formula and from within the Formula Bar press CTRL + SHIFT + ENTER to create an Array Formula. The formula shall now be enclosed in curly braces to indicate that it's an Array Formula.

=IF(OR(ISERROR(INDEX($B$3:$B$100, MATCH(0,COUNTIF($F$2:F2, $B$3:$B$100), 0))),INDEX($B$3:$B$100, MATCH(0,COUNTIF($F$2:F2, $B$3:$B$100), 0))=0),"",INDEX($B$3:$B$100, MATCH(0,COUNTIF($F$2:F2, $B$3:$B$100), 0)))

Drag it down along the length of the intended rows. This will first extract unique values from column B.

In G3 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an array formula.

=IF(F3="","",MIN(IF($B$3:$B$100=F3,$D$3:$D$100,2958352)))

and drag it down along the intended length. Format column G as Date.

This creates a basic output table showing unique person name from the list and the earliest date when he/she asked the first question. You can extend the formula down and wherever no data is returned / applicable it will just show blank. It will get dynamically filled as you fill your master table. This still does not provide 'Weekly New People' perspective though. That would required a different logic.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .