vba code to check if there is a name in a column and check that column for a desired char

[MrRed]

Good day everyone,
I'm quite new to the site but known it for a while.
I would like to ask for your help in automating an excel file to perform a task.
In particular, I have a table where in the first column theres a list on names and across there is a list of abilitations.
The table is filled with "x" where the person has that particular abilitation.
I would like to input a persons name and have returned the list of obtained abilitations, like in the picture attached, without the spaces.
Additionally each abilitation has a responsable, it would be nice to have their names printed next to the corresponding abilitation.
Hope I managed to explain myself decently.
Thank you in advance for any kind of help.

 

[Joe4]

You have three tables shown in your example. Which one is it that you are trying to populate?
It might be more helpful to show us a BEFORE image (of what you have to start) and then your desired result.

Also, it would be much more helpful if you posted your data in a way which we can copy it do our side (which we cannot do with images).
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.

 

[MrRed]

Thank you for replying so quickly.
The following is the table where the data is inserted.

project2.xlsm
	C	D	E	F	G	H	I	J	K	L	M
4	nome\abilitazione	abilitazione 1	abilitazione 2	abilitazione 3	abilitazione 4	abilitazione 5	abilitazione 6	abilitazione 7	abilitazione 8	abilitazione 9	abilitazione 10
5	lavoratore 1	x		x		x		x		x
6	lavoratore 2		x	x		x	x		x	x
7	lavoratore 3	x			x			x			x
Foglio1

Under the C column there are the names of workers and in row 4 the possible abilitations each worker can have. By filling an "x" in the table each worker is assigned one or more abilitations.
I would like to have a macro that takes a workers name as input, finds the name in the C column, checks how many x there are in the matching row and returns the name of that abilitation/s as follows (the formulas that are present at the moment are an attempt to make this work without macros, but if I were to update the workers list or add more abilitations in the future I would pretty much have to change all of the formulas in all of the cells which isn't realy desirable)

project2.xlsm
	A	B	C	D
9		search worker	lavoratore 1
10			corso	responsabile
11			abilitazione 1	Mario
12				0
13			abilitazione 3	Pino
14				0
15			abilitazione 5	Rino
16				0
17			abilitazione 7	Cino
18				0
19			abilitazione 9	Pino
20				0
Foglio1

Cell Formulas
Range	Formula
C11	C11	=IFERROR(IF($C$9=$C$5,INDEX($D$4,MATCH("x",$D$5,0)),IF($C$9=$C$6,INDEX($D$4,MATCH("x",$D$6,0)),IF($C$9=$C$7,INDEX($D$4,MATCH("x",$D$7,0))))),"")
D11	D11	=IF($C$11=Foglio3!$C$4,Foglio3!$D$4,IF($C$11=Foglio3!$C$5,Foglio3!$D$5,IF($C$11=Foglio3!$C$6,Foglio3!$D$6,IF(Foglio1!$C$11=Foglio3!$C$7,Foglio3!$D$7,IF(Foglio1!$C$11=Foglio3!$C$8,Foglio3!$D$8,IF(Foglio1!$C$11=Foglio3!$C$9,Foglio3!$D$9,IF(Foglio1!$C$11=Foglio3!$C$10,Foglio3!$D$10,IF(Foglio1!$C$11=Foglio3!$C$11,Foglio3!$D$11,IF(Foglio1!$C$11=Foglio3!$C$12,Foglio3!$D$12,IF(Foglio1!$C$11=Foglio3!$C$13,Foglio3!$D$13,""))))))))))
C12	C12	=IFERROR(IF($C$9=$C$5,INDEX($E$4,MATCH("x",$E$5,0)),IF($C$9=$C$6,INDEX($E$4,MATCH("x",$E$6,0)),IF($C$9=$C$7,INDEX($E$4,MATCH("x",$E$7,0))))),"")
D12:D16	D12	=IF(C12=Foglio3!C5,Foglio3!D5,IF(C12=Foglio3!C6,Foglio3!D6,IF(C12=Foglio3!C7,Foglio3!D7,IF(Foglio1!C12=Foglio3!C8,Foglio3!D8,IF(Foglio1!C12=Foglio3!C9,Foglio3!D9,IF(Foglio1!C12=Foglio3!C10,Foglio3!D10,IF(Foglio1!C12=Foglio3!C11,Foglio3!D11,IF(Foglio1!C12=Foglio3!C12,Foglio3!D12,IF(Foglio1!C12=Foglio3!C13,Foglio3!D13,IF(Foglio1!C12=Foglio3!C14,Foglio3!D14,""))))))))))
C13	C13	=IFERROR(IF($C$9=$C$5,INDEX($F$4,MATCH("x",$F$5,0)),IF($C$9=$C$6,INDEX($F$4,MATCH("x",$F$6,0)),IF($C$9=$C$7,INDEX($F$4,MATCH("x",$F$7,0))))),"")
C14	C14	=IFERROR(IF($C$9=$C$5,INDEX($G$4,MATCH("x",$G$5,0)),IF($C$9=$C$6,INDEX($G$4,MATCH("x",$G$6,0)),IF($C$9=$C$7,INDEX($G$4,MATCH("x",$G$7,0))))),"")
C15	C15	=IFERROR(IF($C$9=$C$5,INDEX($H$4,MATCH("x",$H$5,0)),IF($C$9=$C$6,INDEX($H$4,MATCH("x",$H$6,0)),IF($C$9=$C$7,INDEX($H$4,MATCH("x",$H$7,0))))),"")
C16	C16	=IFERROR(IF($C$9=$C$5,INDEX($I$4,MATCH("x",$I$5,0)),IF($C$9=$C$6,INDEX($I$4,MATCH("x",$I$6,0)),IF($C$9=$C$7,INDEX($I$4,MATCH("x",$I$7,0))))),"")
C17	C17	=IFERROR(IF($C$9=$C$5,INDEX($J$4,MATCH("x",J5,0)),IF($C$9=$C$6,INDEX($J$4,MATCH("x",J6,0)),IF($C$9=$C$7,INDEX($J$4,MATCH("x",$J$7,0))))),"")
D17:D20	D17	=IF(C17=Foglio3!C10,Foglio3!D10,IF(C17=Foglio3!C11,Foglio3!D11,IF(C17=Foglio3!C12,Foglio3!D12,IF(Foglio1!C17=Foglio3!C13,Foglio3!D13,IF(Foglio1!C17=Foglio3!C14,Foglio3!D14,IF(Foglio1!C17=Foglio3!C15,Foglio3!D15,IF(Foglio1!C17=Foglio3!C16,Foglio3!D16,IF(Foglio1!C17=Foglio3!C17,Foglio3!D17,IF(Foglio1!C17=Foglio3!C18,Foglio3!D18,IF(Foglio1!C17=Foglio3!C19,Foglio3!D19,FALSE))))))))))
C18	C18	=IFERROR(IF($C$9=$C$5,INDEX($K$4,MATCH("x",K5,0)),IF($C$9=$C$6,INDEX($K$4,MATCH("x",K6,0)),IF($C$9=$C$7,INDEX($K$4,MATCH("x",$K$7,0))))),"")
C19	C19	=IFERROR(IF($C$9=$C$5,INDEX($L$4,MATCH("x",L5,0)),IF($C$9=$C$6,INDEX($L$4,MATCH("x",L6,0)),IF($C$9=$C$7,INDEX($L$4,MATCH("x",$L$7,0))))),"")
C20	C20	=IFERROR(IF($C$9=$C$5,INDEX($M$4,MATCH("x",M5,0)),IF($C$9=$C$6,INDEX($M$4,MATCH("x",M6,0)),IF($C$9=$C$7,INDEX($M$4,MATCH("x",$M$7,0))))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D11:D20	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C9	List	=$C$5:$C$7

I would like it not to have blanks where an abilitations is not aquired by the worker.

Lastly, each abilitation is managed by a supervisor whos name should show up next to the corresponding abilitation as shown above. The following is the data for supervisors.

project2.xlsm
	C	D
3	abilitazione	responsabile
4	abilitazione 1	Mario
5	abilitazione 2	Gino
6	abilitazione 3	Pino
7	abilitazione 4	Lino
8	abilitazione 5	Rino
9	abilitazione 6	Bino
10	abilitazione 7	Cino
11	abilitazione 8	GIno
12	abilitazione 9	Pino
13	abilitazione 10	Lino
Foglio3

Thank you for any kind of help.

Let me know if I have to add more info.

 

[Fluff]

You can do that without a macro if you interested.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4			nome\abilitazione	abilitazione 1	abilitazione 2	abilitazione 3	abilitazione 4	abilitazione 5	abilitazione 6	abilitazione 7	abilitazione 8	abilitazione 9	abilitazione 10
5			lavoratore 1	x		x		x		x		x
6			lavoratore 2		x	x		x	x		x	x
7			lavoratore 3	x			x			x			x
8
9		search worker	lavoratore 1
10			corso	responsabile
11			abilitazione 1	Mario
12			abilitazione 3	Pino
13			abilitazione 5	Rino
14			abilitazione 7	Cino
15			abilitazione 9	Pino
16
17
18
19
20
21
22
Main

Cell Formulas
Range	Formula
C11:C19	C11	=IFERROR(INDEX($D$4:$M$4,AGGREGATE(15,6,(COLUMN($D$4:$M$4)-COLUMN($D$4)+1)/($C$5:$C$7=$C$9)/($D$5:$M$7="x"),ROWS(C$11:C11))),"")
D11:D19	D11	=IF(C11="","",VLOOKUP(C11,Foglio3!C3:D13,2,0))

Cells with Data Validation
Cell	Allow	Criteria
C9	List	=$C$5:$C$7

 

[MrRed]

It works like a charm you absolute leggend. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback.