making a "table" from an array

[smartphreak]

Hi All,

I have long list of data which is effectively Client in ColA and Project in ColB - both of theses are not unique.

Essentially looking to make a casscading drop down to be able to choose client and project to find the rest of the data in another sheet.

For some reason my filter is not wokring and gives me a Calc error - so trying a different approach, in that i want to create a table with unique clients in ColC and then all the projects that match the Client fill in fomr DolD onwards to the right of the corresponding client. So if 4 project names match the client then it woudl return:

ColC	ColD	ColE	ColF	ColG
Client1	Project2	Project 4	Project 6
Client2	Project 1	Project 2	Project 5
Client3	Project 1	Project 3	Project 4	Project 6

is there a formula thatI can put in ColD to populate the row?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For some reason my filter is not wokring and gives me a Calc error -

Perhaps we need to see exactly how you are trying to use it if you want to try to find our what the problem is.

Anyway, see if this is the sort of thing you want

24 11 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1											Client	Project
2	Client4	Project1	Client4	Project1	Project6	Project16	Project9	Project3			Client5
3	Client4	Project6	Client1	Project6	Project4	Project10	Project7	Project14			Client3
4	Client1	Project6	Client5	Project2	Project3	Project4	Project8
5	Client1	Project4	Client3	Project10	Project2	Project6
6	Client5	Project2	Client2	Project10	Project1
7	Client1	Project10
8	Client3	Project10
9	Client2	Project10
10	Client5	Project3
11	Client3	Project2
12	Client1	Project7
13	Client4	Project16
14	Client4	Project9
15	Client5	Project4
16	Client5	Project8
17	Client1	Project14
18	Client4	Project3
19	Client2	Project1
20	Client3	Project6
Sheet2 (3)

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(A2:A20)
D6:E6,D5:F5,D4:G4,D2:H3	D2	=TRANSPOSE(FILTER(B$2:B$20,A$2:A$20=C2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
K2:K10	List	=$C$2#
L2:L10	List	=INDEX($D:$D,MATCH(K2,$C:$C,0))#

Example of the Project drop-down use:

 

[Sergius]

Hello! It would be easier if you provided a piece of data (not real) to understand what's what. As it is, as it should be. Then the answer will be faster. And also specify the Excel version by changing the data in your profile.

 

[smartphreak]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Perhaps we need to see exactly how you are trying to use it if you want to try to find our what the problem is.

Anyway, see if this is the sort of thing you want

24 11 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1											Client	Project
2	Client4	Project1	Client4	Project1	Project6	Project16	Project9	Project3			Client5
3	Client4	Project6	Client1	Project6	Project4	Project10	Project7	Project14			Client3
4	Client1	Project6	Client5	Project2	Project3	Project4	Project8
5	Client1	Project4	Client3	Project10	Project2	Project6
6	Client5	Project2	Client2	Project10	Project1
7	Client1	Project10
8	Client3	Project10
9	Client2	Project10
10	Client5	Project3
11	Client3	Project2
12	Client1	Project7
13	Client4	Project16
14	Client4	Project9
15	Client5	Project4
16	Client5	Project8
17	Client1	Project14
18	Client4	Project3
19	Client2	Project1
20	Client3	Project6
Sheet2 (3)

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(A2:A20)
D6:E6,D5:F5,D4:G4,D2:H3	D2	=TRANSPOSE(FILTER(B$2:B$20,A$2:A$20=C2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
K2:K10	List	=$C$2#
L2:L10	List	=INDEX($D:$D,MATCH(K2,$C:$C,0))#

Example of the Project drop-down use:

View attachment 119134

Perfect - exactly what I was looking for.

 

[Peter_SSs]

Perfect - exactly what I was looking for.

You are welcome. Thanks for the follow-up.

In return, what about that profile update?