CODEWITHSUNDEEP
excelunique
ASK
ASK

Reputation: 19

Finding Unique data in a list

I have a dataset:

dataset

I need a new table in which I can get the sum of the hours a person worked. Now my problem is each time a new person's data arrives, I have to add his name to the other table, is there a way to do that automatically. Thank you

Upvotes: 1

Views: 32

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You could use a pivot tables. In that case, you should manually update it every time you have new data.

If you want automatic updating, you can:

  1. Get a column with unique Person values (taken from here). For instance, enter in C2 :
    =INDEX($A$2:$A$50,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$50),0,0),0)).
    and copy downwards. In the link provided there is another posible formula, which is an array formula. I personally like better non-array formulas. The range $A$2:$A$50 could be large enough to cover future data additions. You could wrap the formula with IFERROR:
    =IFERROR(INDEX($A$2:$A$50,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$50),0,0),0))," ").
    This will still leave a zero at the end of the list, but it does not bother. Or you could easily modify your formula to get rid of it.

  2. Get the sum of values for each unique ítem in the list. Enter in D2 :
    =IFERROR(SUMPRODUCT(($A$2:$A$50=C2)*$B$2:$B$50)," ") and copy downwards.

This solution avoids having to refresh.

PS: I think I have already posted this solution to a similar question a long time ago.

Upvotes: 1

Related Questions