Suggestions wanted on how to display table of info

[meileetan]

Hello,
I have a sheet of raw data like this:
Column A Column B Column C
Item Condition X Condition Y
A Y Y
A N N
B Y Y
B N Y
B Y Y

I would like a table that counts the no. of "Y" for each of the items A and B. So something that looks like this:
Item Condition X Condition Y
A 1 1
B 2 3

Can a pivot table do this?

Thank you.

 

[Gerald Higgins]

Can a pivot table do this?

Yes. Post back if you need more help doing it.

 

[hiker95]

meileetan,


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	Item	Condition X	Condition Y
2	A	Y	Y
3	A	N	N
4	B	Y	Y
5	B	N	Y
6	B	Y	Y
7
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	Item	Condition X	Condition Y		Item	Condition X	Condition Y
2	A	Y	Y		A	1	1
3	A	N	N		B	2	3
4	B	Y	Y
5	B	N	Y
6	B	Y	Y
7
Sheet1

If you were to add more data to columns A, B and C, and run the macro again, columns E, F, and G will be cleared, and then columns E, F, and G will be re-created.




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CountItem()
'  hiker95, 07/18/2011
' http://www.mrexcel.com/forum/showthread.php?t=564946
Dim LR As Long, LR2 As Long
If Range("A1") <> "Item" Then
  MsgBox "Cell A1 does not contain 'Item' - macro terminated!"
  Exit Sub
End If
Application.ScreenUpdating = False
Columns("E:G").ClearContents
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns("E"), Unique:=True
Range("F1").Resize(, 2).Value = Range("B1").Resize(, 2).Value
LR = Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Cells(Rows.Count, 5).End(xlUp).Row
With Range("F2:F" & LR2)
  .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC5),--(R2C2:R" & LR & "C2=""Y""))"
  .Value = .Value
End With
With Range("G2:G" & LR2)
  .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC5),--(R2C3:R" & LR & "C3=""Y""))"
  .Value = .Value
End With
Columns("E:G").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the CountItem macro.

 

[meileetan]

Hi! Can you please explain how to do the pivot table?

 

[Gerald Higgins]

Try the Pivot Table Wizard.

Select your data.
Then choose Data, Pivot Table, to invoke the wizard.
Then follow the steps it takes you through.

 

[meileetan]

Hi Gerald,
The wizard doesn't give me the layout I want. It is giving the following:
Item Condition X=Y Condition X=N
Condition Y=Y Condition Y=N Condition Y=Y Condition Y=N