Silverhorne
New Member
- Joined
- Feb 21, 2015
- Messages
- 42
- Office Version
- 365
- 2019
- Platform
- Windows
Good day!
I am looking to pull the unique values from a range (D1:K27) but only if the values in that range match the contents of a cell in that specific row (A1:A27).
I have pasted an example with the results I would like, the formulas would go in column N and carry over to the right.
I was messing around with the TOCOL formula in N9, but it misses the "BB" and gives an #N/A.
I can sort the results later A-Z.
Any help would be much appreciated, thanks in advance!
I am looking to pull the unique values from a range (D1:K27) but only if the values in that range match the contents of a cell in that specific row (A1:A27).
I have pasted an example with the results I would like, the formulas would go in column N and carry over to the right.
I was messing around with the TOCOL formula in N9, but it misses the "BB" and gives an #N/A.
I can sort the results later A-Z.
Any help would be much appreciated, thanks in advance!
Book1 | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | PRO 4 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
2 | PRO 1 | AA | AA | AA | AA | AA | AA | AA | AA | Personnel | AA | BB | CC | DD | ||||||||||
3 | PRO 4 | BB | AA | BB | AA | DD | DD | DD | DD | |||||||||||||||
4 | PRO 3 | BB | AA | BB | AA | AA | AA | AA | AA | PRO 1 | AA | |||||||||||||
5 | PRO 3 | BB | AA | BB | AA | AA | AA | AA | AA | PRO 2 | AA | BB | CC | DD | ||||||||||
6 | PRO 2 | BB | AA | BB | AA | DD | DD | DD | DD | PRO 3 | AA | BB | ||||||||||||
7 | PRO 4 | DD | DD | DD | DD | DD | DD | DD | DD | PRO 4 | AA | BB | DD | |||||||||||
8 | PRO 1 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
9 | PRO 2 | AA | AA | AA | AA | CC | CC | CC | CC | PRO 4 | AA | DD | #N/A | |||||||||||
10 | PRO 3 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
11 | PRO 2 | BB | BB | BB | BB | BB | BB | BB | BB | |||||||||||||||
12 | PRO 2 | BB | BB | BB | BB | BB | BB | BB | BB | |||||||||||||||
13 | PRO 4 | BB | BB | BB | BB | BB | BB | BB | BB | |||||||||||||||
14 | PRO 2 | BB | BB | BB | BB | BB | BB | BB | BB | |||||||||||||||
15 | PRO 4 | BB | BB | BB | BB | BB | BB | BB | BB | |||||||||||||||
16 | PRO 2 | CC | CC | CC | CC | CC | CC | CC | CC | |||||||||||||||
17 | PRO 1 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
18 | PRO 2 | DD | DD | DD | DD | DD | DD | DD | DD | |||||||||||||||
19 | PRO 2 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
20 | PRO 3 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
21 | PRO 3 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
22 | PRO 3 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
23 | PRO 4 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
24 | PRO 4 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
25 | PRO 2 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
26 | PRO 1 | AA | AA | AA | AA | AA | AA | AA | AA | |||||||||||||||
27 | PRO 3 | BB | BB | BB | BB | AA | AA | AA | AA | |||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N2:Q2 | N2 | =TRANSPOSE(SORT(UNIQUE(TOCOL($D$1:$K$27,3)),1,1)) |
N9:Q9 | N9 | =TRANSPOSE(UNIQUE(IF($A$1:$A$27=$M9,TOCOL($D$1:$K$27,3),""))) |
Dynamic array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
_FilterDatabase | =Sheet1!$A$1:$K$27 | N9 |