Maintain position of value in UNIQUE function

[ncbf87]

Hi guys,

Hope you're well. I'd appreciate some guidance from the pros here.

I've a table of all employee's position title which I need to summarise into a table of unique position titles --> do a count --> and allow manual input in another column what is the required headcount of a position end of year. The challenge comes when a manager may change what a current position title is called, which will then refresh the UNIQUE formula, the sorting of values will differ, and this will mess up the manual input of headcounts

Example (screenshot attached):

Col D (Table 2) = line by line of all available positions in the company

Col F = UNIQUE(Table2[All Positions]))

Col G = COUNTIF(Table2[All Positions],F2#)

Col H = Manual Input

Column H is a data we want to track, maintain, and allow future changes. However, it needs to match the "unique position" line in column F.

If the position title in column D changes (rename, new row), column F will refresh with a change in order of the positions, which will make column H's input redundant.

Is there any alternative to approach this challenge, or a solution to fix the position of the position titles (maybe by having a unique ID for sorting purpose?)

Excel ver: Microsoft 365

 

[jasonb75]

maybe by having a unique ID for sorting purpose?

That is the most sensible way. The unique ID would need to be added to the table on the left. Assuming that the ID is added to column E the formula would be something like.

=INDEX(SORT(UNIQUE(Table2[[All Positions]:[Unique ID]]),2),,1)

Which would sort on the order of the ID instead of the position. Note that it is still likely to mess up if you use a formula to generate the unique ID from the position name or if some, but not all instances of a position are changed, which will effectively create an additional position. If this happens, the order would remain but the new position would be inserted next to the original position with the same ID, shifting the rest down.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Maintain position of value in UNIQUE function
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ncbf87]

Thanks Fluff, sorry about the cross-posting and thanks for posting the link on behalf.

I've also attached the mini-sheet here for better reference.

Note that the top half of the minisheet shows the original data going out to the business. The business will then start their manual headcount input in the last column. Hence why sorting of the summarised position title must stay fixed.

However, the business user may also rename the position title in column B/C. If the title is existing, it's fine, the sorting won't change, and the manual input not affected. However, if the title is new (referring to the bottom half of the minisheet, position 777), it messes up the sorting by removing position 4, and appending position 777 before position B. The ideal sorting/outcome is showed in ell J18

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1					Current
2	Original Position Title	Manual Change to Position Title	Final Position Title (to be summarised)		Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
3	Position21		Position21		Position1	1	5
4	Position65		Position65		Position21	1	3
5	Position63		Position63		Position22	1	2
6	Position45		Position45		Position4	1	5
7	Position58	Position B	Position B		Position45	1	1
8	Position77		Position77		Position53	1
9	Position22		Position22		Position63	1
10	Position1		Position1		Position65	1
11	Position53		Position53		Position77	1
12	Position4		Position4		Position B	1
13
14
15
16					Current					IDEAL
17	Original Position Title	Manual Change to Position Title	Final Position Title (to be summarised)		Unique Positions (from Final Position Title)	Count	Manual Input (FTE)			Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
18	Position21		Position21		Position1	1	5			Position1	1	5
19	Position65		Position65		Position21	1	3			Position21	1	3
20	Position63		Position63		Position22	1	2			Position22	1	2
21	Position45		Position45		Position45	1	5			Position4	0	5
22	Position58	Position B	Position B		Position53	1	1			Position45	1	1
23	Position77		Position77		Position63	1				Position53	1
24	Position22		Position22		Position65	1				Position63	1
25	Position1		Position1		Position77	1				Position65	1
26	Position53		Position53		Position 777	1				Position77	1
27	Position4	Position 777	Position 777		Position B	1				Position B	1
28										Position 777	1
29
30
31
32
33
34
35
36
37
38
39
40
Sheet1

Cell Formulas
Range	Formula
E3:E12	E3	=UNIQUE(SORTBY(Table2[Final Position Title (to be summarised)],REPT("Ω",Table2[Manual Change to Position Title]>0)&Table2[Final Position Title (to be summarised)]))
F3:F12	F3	=COUNTIF(Table2[Final Position Title (to be summarised)],E3#)
C3:C12,C18:C27	C3	=IF(B3="",A3,B3)
E18:E27	E18	=UNIQUE(SORTBY(Table22[Final Position Title (to be summarised)],REPT("Ω",Table22[Manual Change to Position Title]>0)&Table22[Final Position Title (to be summarised)]))
F18:F27	F18	=COUNTIF(Table22[Final Position Title (to be summarised)],E18#)
Dynamic array formulas.

 

[ncbf87]

That is the most sensible way. The unique ID would need to be added to the table on the left. Assuming that the ID is added to column E the formula would be something like.

=INDEX(SORT(UNIQUE(Table2[[All Positions]:[Unique ID]]),2),,1)

Which would sort on the order of the ID instead of the position. Note that it is still likely to mess up if you use a formula to generate the unique ID from the position name or if some, but not all instances of a position are changed, which will effectively create an additional position. If this happens, the order would remain but the new position would be inserted next to the original position with the same ID, shifting the rest down.

Thanks jasonb75,
I've posted a minisheet showing the scenario and ideal outcome for better reference. Your solution unfortunately doesn't solve the issue, very likely I didn't explain myself well enough, sorry.

 

[jasonb75]

Why has Position 58 been omitted from the ideal output instead of being shown with a zero count like Position 4? On the assumption that the omission was an error and that it should be included for consistency, this appears to work as needed.

Edit:- I went back to do a little more testing and immediately noticed that the sequence array doesn't stop as it should when there are duplicates in column B. I'll leave the original here for you to test and have a look at fixing the error shortly.

Book1
	A	B	C	D	E	F	G
17	Original Position Title	Manual Change to Position Title	Final Position Title (to be summarised)		Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
18	Position21		Position21		Position1	1	5
19	Position65		Position65		Position21	1	3
20	Position63		Position63		Position22	1	2
21	Position45		Position45		Position4	0	5
22	Position58	Position B	Position B		Position45	1	1
23	Position77		Position77		Position53	1
24	Position22		Position22		Position58	0
25	Position1		Position1		Position63	1
26	Position53		Position53		Position65	1
27	Position4	Position 777	Position 777		Position77	1
28					Position 777	1
29					Position B	1
Sheet1

Cell Formulas
Range	Formula
E18:E29	E18	=LET(o,UNIQUE(Table22[Original Position Title]),m,Table22[Manual Change to Position Title],IF(SEQUENCE(COUNTA(o,m))<=COUNTA(o),SORT(o),INDEX(SORT(m),SEQUENCE(COUNTA(o,m))-COUNTA(o))))
F18:F29	F18	=COUNTIF(Table22[Final Position Title (to be summarised)],E18#)
C18:C27	C18	=IF(B18="",A18,B18)
Dynamic array formulas.

 

[jasonb75]

This works but it's a bit clunky, maybe somebody else can clean it up a little. Note that if there are no manual changes in column B then the dynamic arrays will return a blank row at the bottom of the results table. It would be possible to omit that but I've left it for now to reduce the length of the formula.

Book1
	E	F	G
17	Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
18	Position1	1	5
19	Position21	1	3
20	Position22	1	2
21	Position4	0	5
22	Position45	1	1
23	Position53	1
24	Position58	0
25	Position63	1
26	Position65	1
27	Position77	1
28	Position 777	1
29	Position B	1
Sheet1

Cell Formulas
Range	Formula
E18:E29	E18	=LET(o,UNIQUE(Table22[Original Position Title]),m,UNIQUE(Table22[Manual Change to Position Title]),f,FILTER(m,m<>"",""),s,SEQUENCE(COUNTA(o,f)),IF(s<=COUNTA(o),SORT(o),INDEX(SORT(f),s-COUNTA(o))))
F18:F29	F18	=IF(E18#="","",COUNTIF(Table22[Final Position Title (to be summarised)],E18#))
Dynamic array formulas.

 

[ncbf87]

This works but it's a bit clunky, maybe somebody else can clean it up a little. Note that if there are no manual changes in column B then the dynamic arrays will return a blank row at the bottom of the results table. It would be possible to omit that but I've left it for now to reduce the length of the formula.

Book1
	E	F	G
17	Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
18	Position1	1	5
19	Position21	1	3
20	Position22	1	2
21	Position4	0	5
22	Position45	1	1
23	Position53	1
24	Position58	0
25	Position63	1
26	Position65	1
27	Position77	1
28	Position 777	1
29	Position B	1
Sheet1

Cell Formulas
Range	Formula
E18:E29	E18	=LET(o,UNIQUE(Table22[Original Position Title]),m,UNIQUE(Table22[Manual Change to Position Title]),f,FILTER(m,m<>"",""),s,SEQUENCE(COUNTA(o,f)),IF(s<=COUNTA(o),SORT(o),INDEX(SORT(f),s-COUNTA(o))))
F18:F29	F18	=IF(E18#="","",COUNTIF(Table22[Final Position Title (to be summarised)],E18#))
Dynamic array formulas.

Thanks jason75.

If only I can upload an attachment with the comments. Please see screenshot of comments (of the process) below on the upper half of the minisheet earlier. Your question is valid, but not an error.
Position58 was omitted because it happens when I'm prepping the file before going to the business. Another screenshot below showing the bottom half of the minisheet

Upper half:

Bottom half:

Unfortunately (correct me if I'm wrong), the formula doesn't work if the new position title is before the alphabet "B". For example, renaming a position to position A will sort it before position B. While renaming it to position C will sort it after position B.

Example below. I've renamed the title based on this order (position B was already there):
1. Position A
2. Position A1
3. Position C

Book1.xlsx
	A	B	C	D	E	F	G
16					Current
17	Original Position Title	Manual Change to Position Title	Final Position Title (to be summarised)		Unique Positions (from Final Position Title)	Count	Manual Input (FTE)
18	Position21		Position21		Position1	0	5
19	Position65		Position65		Position21	1	3
20	Position63		Position63		Position22	1	2
21	Position45		Position45		Position4	0	5
22	Position58	Position B	Position B		Position45	1	1
23	Position77		Position77		Position53	0
24	Position22		Position22		Position58	0
25	Position1	Position A1	Position A1		Position63	1
26	Position53	Position A	Position A		Position65	1
27	Position4	Position C	Position C		Position77	1
28					Position A	1
29					Position A1	1
30					Position B	1
31					Position C	1
Sheet1 (2)

Cell Formulas
Range	Formula
E18:E31	E18	=LET(o,UNIQUE(Table225[Original Position Title]),m,UNIQUE(Table225[Manual Change to Position Title]),f,FILTER(m,m<>"",""),s,SEQUENCE(COUNTA(o,f)), IF(s<=COUNTA(o),SORT(o),INDEX(SORT(f),s-COUNTA(o))))
F18:F31	F18	=IF(E18#="","",COUNTIF(Table225[Final Position Title (to be summarised)],E18#))
C18:C27	C18	=IF(B18="",A18,B18)
Dynamic array formulas.

 

[jasonb75]

That makes it clearer, what you are asking will not be possible with formulas in that exact format.

At best you could either have the output unsorted, or possibly sorted by the original list with the manual changes replacing the originals in the correct row (at present untested theory that may or may not work).

To get exactly as you are asking for would require two vba procedures, one for you to set up the initial sheet, another for the end user that will detect any subsequent changes and update the output accordingly. Naturally this has potential to fail if the user doesn't allow macros to run.

 

[ncbf87]

That makes it clearer, what you are asking will not be possible with formulas in that exact format.

At best you could either have the output unsorted, or possibly sorted by the original list with the manual changes replacing the originals in the correct row (at present untested theory that may or may not work).

To get exactly as you are asking for would require two vba procedures, one for you to set up the initial sheet, another for the end user that will detect any subsequent changes and update the output accordingly. Naturally this has potential to fail if the user doesn't allow macros to run.

Thanks! To have confirmation that this cannot work is still progress. What about Power Query though?