Pull in data from other cells without duplication within a range

[schrieber]

Goal: Cell range B3 through L3 (in green) to pull in the first 4 digits only of the project names that reside in all of the Project Name columns (in yellow: cells A9 through A19 and D9 through D19, and etc.), with 1 project name per cell without duplicates. If possible, do not pull in Project Name that is 9999.

The project numbers that will show in row 3 (in green) do not have to be in any particular order.

Each month the project names and their order change on the spreadsheet but will reside within the yellow cells.

Thanks in advance for any help provided and please let me know if I haven't provided enough information.

 

[Peter_SSs]

For the future BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. (Then you won't get such small 'rubbish' sample data like mine.

range B3 through L3 (in green) to pull in

I take that to mean that you only want a maximum of 11 values even if there might be more.

23 08 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
3		4251	4283	4381	4386	4432	1111	2222	3333	4444	5555	6666
4
5
6
7
8
9	4251-aa			4251-vv			3333-a			6666-b
10	4283-v			4283-z			4444-b			7777-b
11	4381-jgh			4432-a			5555-b
12	4386-lll			1111-a			9999-v
13	4432-ff			2222-a
14	9999-dd
15
Project Numbers

Cell Formulas
Range	Formula
B3:L3	B3	=LET(d,LEFT(TOCOL(CHOOSECOLS(A9:M19,1,4,7,10,13),1,1),4),TRANSPOSE(TAKE(UNIQUE(FILTER(d,LEFT(d,4)<>"9999","")),11)))
Dynamic array formulas.

 

[schrieber]

This works perfectly. Thank you!

I have now found that in the data that may get pulled in from time to time within A:9:M19 that a cell may also have a 0 (zero) or the word DAILY (For instance in the example above A:15 might have 0 and/or G:13 may have DAILY. I didn't show that in my original question to you (sorry!) Can those be excluded, as well?

 

[Peter_SSs]

Try this. It excludes any text (not just "DAILY") and 0

23 08 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
3		4251	4283	4381	4386	4432	1111	2222	3333	4444	5555	6666
4
5
6
7
8
9	4251-aa			4251-vv			3333-a			6666-b
10	4283-v			4283-z			4444-b			7777-b
11	4381-jgh			4432-a			5555-b
12	4386-lll			1111-a			9999-v
13	4432-ff			2222-a
14	9999-dd			DAILY
15	0
16	ABC
17
Project Numbers (2)

Cell Formulas
Range	Formula
B3:L3	B3	=LET(d,LEFT(TOCOL(CHOOSECOLS(A9:M19,1,4,7,10,13),1,1),4),TRANSPOSE(TAKE(UNIQUE(FILTER(d,(LEFT(d,4)<>"9999")*(d<>"0")*ISNUMBER(d+0),"")),11)))
Dynamic array formulas.

 

[schrieber]

Beautiful. Thank you very much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.