Please help with metric sheet formulas!
I need help with a couple of formulas for my metric sheet.
1- I have a master sheet and am trying to figure out a formula for my metric sheet to list all duplicate names on that sheet.
2- I have built a couple of reports from one master sheet. I need a formula to get a count of how many names are on each report.
3- I have an expiration date column that calculates many expiration dates on that row and returns the earliest date. If that expires, I want to see the name on that row, the representative responsible to follow up (from a dropdown) and which task from that filter expired.
Thank you!!
Answers
-
- The easiest for Duplicate names is to create a helper column "Duplicates".
- =COUNTIF(Name:Name, =Name@row) would count the duplicates
- =IF(COUNTIF(Name:Name, =Name@row)>1, "YES", "NO") would put a Yes or No if that name is a duplicate
- You can just create a filter on that column and it will only show the duplicates
- =COUNT(Name:Name) will give you a Count of the names, including duplicates
- For multiple Sheets, you would just have to change to a named range =COUNT({SheetOneNames})
- =COUNT(DISTINCT(Name:Name)) would remove duplicates
- =COUNT(DISTINCT({SheetOneNames})) would work from another sheet
- For the Last, it is going to involve some INDEX and MATCH functions..
- Basically, you want to find the row number of the earliest date you are returning.
- Then you can run some In INDEX functions using the Row Number. to return things like the representative associated with that expiration date.
- Would probably have to see some more data to provide you a formula
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
- The easiest for Duplicate names is to create a helper column "Duplicates".
-
Thank you so much for your help!!
1- Duplicates worked - thank you! I added a countif to my metric sheet to return the number of duplicates on my master sheet.
2- To clarify, I want to add a formula to my metric sheet to see how many names are currently on each report. I cant seem to reference a report in a formula. Only sheets come up as an option.
3- see example below:
When a date in the "upcoming expirations" column is in the past I want to see the "name", "HR Rep" and which task matches that date. (In other words which task has already expired) So in this case I'd want to see:
Name: Jennifer, HR Rep: Cindy, Expired: Availability expiration (maybe we can include the date it expired as well)
there are many more tasks but I just added two for reference.
Thank you so much!
4- One more thing if you have time, How do I set up a VLOOKUP on my master sheet where it lists all the reports that the row is on. In other words I want to know on which report the current row is currently on.
Thank you so so much for your help!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!