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


[TABLE="width: 200"]
<TBODY>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Username[/TD]
[TD]Application[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]User1[/TD]
[TD]App1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]User1[/TD]
[TD]App2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]User1[/TD]
[TD]App3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]User2[/TD]
[TD]App2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]User2[/TD]
[TD]App4[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]User2[/TD]
[TD]App5[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]User2[/TD]
[TD]App6[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]User3[/TD]
[TD]App2[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]User3[/TD]
[TD]App5[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]User3[/TD]
[TD]App7[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]etc..etc..[/TD]
[TD]etc..etc[/TD]
[/TR]
</TBODY>[/TABLE]




This is how I want to present the data

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]App1[/TD]
[TD]App2[/TD]
[TD]App3[/TD]
[TD]App4[/TD]
[TD]App5[/TD]
[TD]App6[/TD]
[TD]App7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]User1[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]User2[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]User3[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]etc..etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



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
*	App1	App2	App3	App4	App5	App6	App7
User1
User2
User3

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]Y[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]*[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]*[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]Y[/TD]
[TD="align: right"]*[/TD]
[TD="align: right"]Y[/TD]

</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
[TABLE="width: 612"]
<TBODY>[TR]
[TD][/TD]
[TD]Alton Water</SPAN>[/TD]
[TD]Anglian House</SPAN>[/TD]
[TD]AWG Property Ltd</SPAN>[/TD]
[TD]Basildon</SPAN>[/TD]
[TD]Beccles District Office</SPAN>[/TD]
[TD]Broadholme</SPAN>[/TD]
[TD]Cambridge</SPAN>[/TD]
[/TR]
[TR]
[TD]AcrobatDistiller</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACTIVControl</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Actiview</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adobeacrobatpro</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AdobeCSSuite5</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adobephotoshop</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AdobePhotoshopElements10</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Autodesk</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Autoroute</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AWSecurityTaskpad</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AWSScan</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>[/TABLE]