Formula Trouble with Index, Match, and Countifs

[NorthbyNorthwest]

Hi, everyone. I have a formula that initially appeared to work, i.e., it returned what I wanted. It returned a list of distinct names from a table to another worksheet.

Formula =INDEX(SumTable1[Employee],MATCH(0,COUNTIFS(C$4:$C5,SumTable1[Employee],D$4:$D5,SumTable1[Position],E$4:$E5,SumTable1[Grade]),0))

The table dataset covers 12 months. Each employee has 12 entries. In most instances, the employee's position and grade will not change in the 12 months. So, in this instance the formula should return the employee's name once. However, if the employee's position or grade changes, I need the formula to return the employee's name a second time (to another row). Initially, the formula worked. But now for some unexplainable reason the formula returns the first name in the table's Employee column repeatedly, as the formula copies down. Could someone assist? I am totally stumped.

 

[Fluff]

How about

=TAKE(UNIQUE(SumTable1[[Employee]:[Grade]]),,1)

 

[NorthbyNorthwest]

Thanks, Fluff. That worked. But now I'm thinking I didn't ask the right question. I thought if I understood how to get the employee's name twice I wouldn't have problem getting the next two columns. Wrong! I've been trying last hour and no luck. So, if you would be so kind, let me ask another question. Sorry. Maybe, it's what I should've asked the first time. As I stated before I want employee's name returned twice if there's a change in position or grade. But I also want the position and grade returned. So...

row 1, column 1: John Smith row 1, column 2: Old position row 1, column 3 Old grade
row 2, column 1: John Smith row 2, column 2: New position row 2, column 3 New grade

How can I achieve this?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[NorthbyNorthwest]

Hi, Fluff. See below.

Employee Accuracy 2.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
3					Review	1	Review	2	Review	3	Quality Totals										YTD Quality Totals
4	Month	Employee	Position	Grade	Elements Checked	Elements Correct	Elements Checked2	Elements Correct2	Elements Checked3	Elements Correct3	Total Reviews	Total Elements Checked	Total Elements Correct	Accuracy Rate this Month		Employee	Position	Grade	Unit	Audit Group	Total Reviews	Total Elements Checked	Total Elements Correct	Accuracy Rate YTD
5	Oct	Audrey Hepburn	Clerk	L6	15	15	16	16	15	15	3	46	46	97.83%		Audrey Hepburn
6	Oct	Barbara Stanwyck	Clerk	L6	15	14	16	14	15	13	3	46	41	97.83%		Barbara Stanwyck
7	Oct	Bette Davis	Clerk	L5	14	14	15	15	15	13	3	44	42	97.83%		Bette Davis
8	Oct	Cary Grant	Clerk	L6	15	12	12	12	15	14	3	42	38	97.83%		Cary Grant
9	Oct	Clark Gable	Clerk	L6	15	13	16	16	15	14	3	46	43	97.83%		Clark Gable
10	Oct	Elizabeth Taylor	Clerk	L6	15	14	15	14	15	13	3	45	41	97.83%		Elizabeth Taylor
11	Nov	Audrey Hepburn	Clerk	L6	15	14	16	15	15	13	3	46	42	97.83%		Bette Davis
12	Nov	Barbara Stanwyck	Clerk	L6	16	16	16	13	15	15	3	47	44	97.83%
13	Nov	Bette Davis	Clerk	L7	15	15	16	15	15	15	3	46	45	97.83%
14	Nov	Cary Grant	Clerk	L6	13	12	15	15	15	13	3	43	40	97.83%
15	Nov	Clark Gable	Clerk	L6	15	13	15	15	15	15	3	45	43	97.83%
16	Nov	Elizabeth Taylor	Clerk	L6	16	15	15	13	15	12	3	46	40	97.83%
17	Dec	Audrey Hepburn	Clerk	L6	15	13	15	12	15	13	16	45	38	97.83%
18	Dec	Barbara Stanwyck	Clerk	L6	5	5	17	16	15	15	3	37	36	97.83%
19	Dec	Bette Davis	Clerk	L7	15	15	16	16	15	14	3	46	45	97.83%
20	Dec	Cary Grant	Clerk	L6	15	13	15	13	15	15	3	45	41	97.83%
21	Dec	Clark Gable	Clerk	L6	18	18	17	17	15	14	3	50	49	97.83%
Summary

Cell Formulas
Range	Formula
Q5:Q11	Q5	=TAKE(UNIQUE(SumTable1[[Employee]:[Grade]]),,1)
Dynamic array formulas.

 

[Fluff]

Thanks for that.
How about

=UNIQUE(SumTable1[[Employee]:[Grade]])

 

[NorthbyNorthwest]

Fluff, so simple, so genius. I didn't know it was possible to fill in multiple columns with a formula. Thank you for coming to my rescue. So grateful to you and the many others who keep this forum going. Have a blessed day.

 

[Fluff]

Glad to help & thanks for the feedback.