Auto populate columns in one tab based on entries in another

[Aewilliams]

I have a tab (Tab A) that contains information about certain projects. Each project has a reference. Each project may also be tagged as being part of a group. If a project is not a member of a group or is a member of one group then it will have one row. If it is part of two groups then it will have two rows etc. All information on each row will be the same except the column showing the name of the group.

On a second tab I want to summarise all of the groups the project is part of so I want the spreadsheet to have one row for each project and columns headings for each group. If the project is part of the group(s) then that cell(s) will contain a "Y" and if not will be left blank e.g. the info is in tab A and the result I want is shown in Tab B - what formula could I use in tab B in the second, third and fourth columns to pull the info from tab A?

Tab A
Project Ref	Group Name
AAA	Portfolio
AAA	SLT
BBB
CCC	Portfolio
DDD	SLT
EEE	PDC
EEE	SLT

Tab B
Project Ref	Portfolio	SLT	PDC
AAA	Y	Y
BBB
CCC	Y
DDD		Y
EEE		Y	Y

 

[dermie_72]

you could use the sort - unique function to get the list of Project references in sheet 2.

=SORT(UNIQUE(Sheet1!A2:A8),1)

where Sheet1 Range A2:A8 contains your data. Expand as required.
Then use a countifs to identify which project theyre in.
If you highlight the range (IE B2:D6 in your example) and type the following formula (Adjusting tab names as required.) Then press control + Enter

=IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)=0,"","Y")

You will get the result you're looking for as described in your request.

 

[Fluff]

What version of Excel are you using?

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’)

 

[Aewilliams]

you could use the sort - unique function to get the list of Project references in sheet 2.

=SORT(UNIQUE(Sheet1!A2:A8),1)

where Sheet1 Range A2:A8 contains your data. Expand as required.
Then use a countifs to identify which project theyre in.
If you highlight the range (IE B2:D6 in your example) and type the following formula (Adjusting tab names as required.) Then press control + Enter

=IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)=0,"","Y")

You will get the result you're looking for as described in your request.

Thanks for this. This is just returning a blank....

 

[Aewilliams]

What version of Excel are you using?

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’)

Microsoft® Excel® for Microsoft 365 MSO

 

[Fluff]

Microsoft® Excel® for Microsoft 365 MSO

Thanks for that, please don't forget to update your profile, as it saves people from having to ask.

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Project Ref	Group Name			Portfolio	SLT	PDC
2	AAA	Portfolio		AAA	Y	Y
3	AAA	SLT		BBB
4	BBB			CCC	Y
5	CCC	Portfolio		DDD		Y
6	DDD	SLT		EEE		Y	Y
7	EEE	PDC
8	EEE	SLT
9
Main

Cell Formulas
Range	Formula
E1:G1	E1	=TOROW(UNIQUE(B2:B100),1)
D2:D6	D2	=UNIQUE(TOCOL(A2:A100,1))
E2:G6	E2	=IF(COUNTIFS(A:A,D2#,B:B,E1#),"Y","")
Dynamic array formulas.

 

[dermie_72]

Did you change the sheet name in the formula to your sheet name that contains the data?