Hi gang,
I am stuck on a worksheet that just won't cooperate. I have a huge workbook with tabs for employee entry each week. I want a tab each for monthly/quarterly reporting. On each weekly worksheet, the manager enters each employees name along with some other data. I want the monthly report to dynamically generate with all of the employees that have worked that 5-week period (no need to worry about 4 vs 5 week months for my purposes).
In my first attempt, I set up a data worksheet where I pull in the list of each week's employees, and I want to de-duplicate and alphabetize that list. I can't quite work out the arrays, and hope maybe someone out there could either fix up my formulas (formulae??) or present me with a new one. I'm avoiding VB because 1) I don't know if well enough to support it and 2) this thing will be out of my hands and caretaking very soon and I want it fairly easy to maintain.
I can't figure out how the new-ish Communities lets you upload a page, so here's the quick and dirty paste of what my test data looks like right now, columns A (range named "names"), B, and C:
| Jan names | filter | alpha |
| Beth | Beth | Annette |
| Jim | Jim | Annette |
| Roger | Roger | Beth |
| Joe | Joe | Bill |
| Jeremy | Jeremy | Jeremy |
| Bill | Bill | Jim |
| Annette | Annette | Joe |
| 0 | 0 | Roger |
| 0 | #N/A | |
| Beth | #N/A | |
| Roger | #N/A | |
| Jeremy | #N/A | |
| Bill | #N/A | |
| 0 | #N/A | |
| 0 | #N/A | |
| 0 | #N/A | |
| Jim | #N/A | |
| Bill | #N/A | |
| Annette | #N/A | |
| joe | #N/A | |
| 0 | #N/A | |
| 0 | #N/A |
Column B has the array (with ctrl-shift-enter) in is of:
=(INDEX(names,MATCH(0,COUNTIF(B$1:$B1,names)+IF(COUNTIF(names,names)>=1,0,1),0)))
and Column C has:
=IF(ISNA(INDEX($B$2:$B$23, MATCH(SMALL(COUNTIF($B$2:$B$23, "<"&$B$2:$B$23), ROW(1:1)), COUNTIF($B$2:$B$23, "<"&$B$2:$B$23), 0))),"",(INDEX($B$2:$B$23, MATCH(SMALL(COUNTIF($B$2:$B$23, "<"&$B$2:$B$23), ROW(1:1)), COUNTIF($B$2:$B$23, "<"&$B$2:$B$23), 0)))).
You can see that Column C is giving me duplicate names. And when I copy these formulas in to my larger worksheet, they give me somehwat stranger data still.
Any thoughts? Thanks so much!!
-beth
