How to return a "Y" if 2 values are on the same row

[nathanhogan]

Hello, I'm trying to do a table of data of users vs applications. users are listed in column A and the applications are along the top in Row A. I also have the data listed in 2 columns on anther sheet. What I need to do is where the cell lines up with a user and is under an application their name appears next to in the main data I want to return a Y.

This is my data layout

	1	2
A	Username	Application
B	User1	App1
C	User1	App2
D	User1	App3
E	User2	App2
F	User2	App4
G	User2	App5
H	User2	App6
I	User3	App2
J	User3	App5
K	User3	App7
L	etc..etc..	etc..etc

<TBODY>
</TBODY>

This is how I want to present the data

	1	2	3	4	5	6	7	8
A		App1	App2	App3	App4	App5	App6	App7
B	User1	Y	Y	Y
C	User2		Y	Y	Y	Y
D	User3		Y		Y		Y
E	etc..etc..

<TBODY>
</TBODY>

So where cell B2 is I need a formula thats say IF the data has a row that has the username and the application in it return a Y.

Any help is very much appreciated. I am using Office 2010. I have tried adding it into a pivot table, I get the right layout however when I select count it doesn't fill anything in. I'm doing this for over 5500 users hence requirement for a formula.

Thank You.

 

[njimack]

Sheet2

*	A	B	C	D	E	F	G	H
1	*	App1	App2	App3	App4	App5	App6	App7
2	User1	Y	Y	Y	*	*	*	*
3	User2	*	Y	*	Y	Y	Y	*
4	User3	*	Y	*	*	Y	*	Y

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=B$1)) C2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=C$1)) D2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=D$1)) E2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=E$1)) F2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=F$1)) G2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=G$1)) H2 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A2),--(Sheet1!$B$2:$B$11=H$1)) B3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=B$1)) C3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=C$1)) D3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=D$1)) E3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=E$1)) F3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=F$1)) G3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=G$1)) H3 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A3),--(Sheet1!$B$2:$B$11=H$1)) B4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=B$1)) C4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=C$1)) D4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=D$1)) E4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=E$1)) F4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=F$1)) G4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=G$1)) H4 =SUMPRODUCT(--(Sheet1!$A$2:$A$11=$A4),--(Sheet1!$B$2:$B$11=H$1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Format the cells as follows:

"Y";;

 

[nathanhogan]

Thank you for the help, I have amended my formulas to the following - =SUMPRODUCT(--(data=$A2),--(data=B$1)) and I have done a format on the cell formatting under custom to say "Y";; however every cell returns a blank. Do you have any further tips at all please?

 

[njimack]

Thank you for the help, I have amended my formulas to the following - =SUMPRODUCT(--(data=$A2),--(data=B$1)) and I have done a format on the cell formatting under custom to say "Y";; however every cell returns a blank. Do you have any further tips at all please?

What's 'data'? You can't refer to the same named range in both arguments, you need separate ranges for columns A and B.

 

[nathanhogan]

Hi again, this worked perfectly in the end (despite being a huge amount of data and slowing down massively)

However, I need to do again but add the number of times the text in cells A2 & B1 appear on the same row and so on and so on up to BB1 A58.

I hope this makes sense, any help much appreciated....again!

 

[nathanhogan]

Here is a little cut of the table and in each cell it counts the number of times the column header and leftmost text appears on the same row

	Alton Water</SPAN>	Anglian House</SPAN>	AWG Property Ltd</SPAN>	Basildon</SPAN>	Beccles District Office</SPAN>	Broadholme</SPAN>	Cambridge</SPAN>
AcrobatDistiller</SPAN>
ACTIVControl</SPAN>
Actiview</SPAN>
Adobeacrobatpro</SPAN>
AdobeCSSuite5</SPAN>
Adobephotoshop</SPAN>
AdobePhotoshopElements10</SPAN>
Autodesk</SPAN>
Autoroute</SPAN>
AWSecurityTaskpad</SPAN>
AWSScan</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>