Identify & count multiple entries in an array

[gn82]

Hi everyone,

I want to automate the way in which I keep track of which people show up for the weekend hikes based on a simple entry attendance. I have a simple table as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]7 Oct[/TD]
[TD]14 Oct[/TD]
[TD]21 Oct[/TD]
[TD]28 Oct[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Participants[/TD]
[TD]Mike[/TD]
[TD]Danielle[/TD]
[TD]Mike[/TD]
[TD]Danielle[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Melissa[/TD]
[TD]Dan[/TD]
[TD]Dan[/TD]
[TD]Julie[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Dan[/TD]
[TD]Alexandra[/TD]
[TD]Robert[/TD]
[TD]Melissa[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Alexandra[/TD]
[TD]Mike[/TD]
[TD]Raphael[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Raphael[/TD]
[TD]Melissa[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can you please help me obtain a simple descending attendance report like the example bellow:
Attendance: Mike (4x), Dan (3x), Melissa (3x), Alexandra (2x), Danielle (2x), Raphael (2x), Julie (1x), Robert (1x)
PS: I apologize in advance if my counting wasn't fully accurate. Also, I use MS Office 2007, if that helps in any way.

Thanks in advance.

 

[JoeMo]

Copy the formula in B10 across to match the list of participants. I missed Dan but you can just add him to the names in row 9.
Test1

*	A	B	C	D	E	F	G	H
Date	*	*	*
Participants	Mike	Danielle	Mike	Danielle	*	*	*
*	Melissa	Dan	Dan	Julie	*	*	*
*	Dan	Alexandra	Robert	Melissa	*	*	*
*	Alexandra	Mike	Raphael	Mike	*	*	*
*	*	Raphael	Melissa	*	*	*	*
*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*
Name	Alexandra	Danielle	Julie	Melissa	Mike	Raphael	Robert
*	2X	2X	1X	3X	4X	2X	1X

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:73px;"><col style="width:73px;"><col style="width:60px;"><col style="width:59px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]7-Oct[/TD]
[TD="align: right"]14-Oct[/TD]
[TD="align: right"]21-Oct[/TD]
[TD="align: right"]28-Oct[/TD]

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

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

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

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

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

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

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula B10 =COUNTIF($B$2:$E$6,B$9)&"X" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[circledchicken]

Hi,

A PivotTable can do this easily. Here are the steps:

• Press Alt -> D -> P to bring up the Wizard
• Select 'Multiple consolidation ranges' and click Next
• Select 'Create a single page field for me' and click Next
• For the 'Range:' box Select all your data (including the column headers)
• Click Add and then click Next
• Choose where you want to put the PivotTable and click Finish

In the resulting PivotTable:

• Remove any automatically added fields (uncheck all the checked boxes in the field list)
• Drag the 'Value' field to the 'Row Labels' section
• Drag the 'Value' field again, this time to the 'Values' section
• Click the filter drop-down next to the Row Labels header in the PivotTable, click on 'More Sort Options...', choose 'Descending (Z to A) by: Count of Value'

Notes:

• If you can, you should re-design your layout. It is much easier to work with this kind of data in a 2-column set-up - i.e. Date in column A and the participants listed in column B next to each associated date.
• For an introduction to PivotTables see for example:
  • Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
  • Overview of PivotTable and PivotChart reports - Excel - Office.com
  • Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

 

[gn82]

@ JoeMo - Thanks for your suggestion.
Coming back to my initial question: can all that be put in 1 descending formula so that I won't have to add each participant manually?

Copy the formula in B10 across to match the list of participants. I missed Dan but you can just add him to the names in row 9.
Test1

*	A	B	C	D	E	F	G	H
Date	*	*	*
Participants	Mike	Danielle	Mike	Danielle	*	*	*
*	Melissa	Dan	Dan	Julie	*	*	*
*	Dan	Alexandra	Robert	Melissa	*	*	*
*	Alexandra	Mike	Raphael	Mike	*	*	*
*	*	Raphael	Melissa	*	*	*	*
*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*
Name	Alexandra	Danielle	Julie	Melissa	Mike	Raphael	Robert
*	2X	2X	1X	3X	4X	2X	1X

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

[TD="align: right"]7-Oct[/TD]
[TD="align: right"]14-Oct[/TD]
[TD="align: right"]21-Oct[/TD]
[TD="align: right"]28-Oct[/TD]

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

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

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

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

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

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

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula B10 =COUNTIF($B$2:$E$6,B$9)&"X" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JoeMo]

@ JoeMo - Thanks for your suggestion.
Coming back to my initial question: can all that be put in 1 descending formula so that I won't have to add each participant manually?

Possibly, but I think you'd be better off with circledchicken's advice to go with a pivot table.

 

[mumps]

Hello gn82. Click here to download your sample file and give it a try. Will this suit your purpose?

Cheers!

 

[hiker95]

gn82,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G
1	Date	7-Oct	14-Oct	21-Oct	28-Oct
2	Participants	Mike	Danielle	Mike	Danielle
3		Melissa	Dan	Dan	Julie
4		Dan	Alexandra	Robert	Melissa
5		Alexandra	Mike	Raphael	Mike
6			Raphael	Melissa
7
8
9
10
Sheet1

After the macro in a new worksheet Attendance:

Excel Workbook
	A	B
1	Name	Count
2	Mike	4
3	Dan	3
4	Melissa	3
5	Alexandra	2
6	Danielle	2
7	Raphael	2
8	Julie	1
9	Robert	1
10
11
12
13
Attendance

If you add the data for the hike on Nov 2:

Excel Workbook
	A	B	C	D	E	F	G
1	Date	7-Oct	14-Oct	21-Oct	28-Oct	2-Nov
2	Participants	Mike	Danielle	Mike	Danielle	Dan
3		Melissa	Dan	Dan	Julie	Alexandra
4		Dan	Alexandra	Robert	Melissa	Mike
5		Alexandra	Mike	Raphael	Mike	Raphael
6			Raphael	Melissa		gn82
7						hiker95
8
9
10
Sheet1

Then we get this:

Excel Workbook
	A	B
1	Name	Count
2	Mike	5
3	Dan	4
4	Alexandra	3
5	Melissa	3
6	Raphael	3
7	Danielle	2
8	gn82	1
9	hiker95	1
10	Julie	1
11	Robert	1
12
13
Attendance

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 GetUniques()
' hiker95, 11/30/2012
' http://www.mrexcel.com/forum/excel-questions/672289-identify-count-multiple-entries-array.html
Dim w1 As Worksheet, wA As Worksheet
Dim lr As Long, lr2 As Long, lc As Long
Dim d As Object, c As Range
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Attendance!A1)") Then Worksheets.Add(After:=w1).Name = "Attendance"
Set wA = Worksheets("Attendance")
wA.UsedRange.Clear
wA.Cells(1, 1).Resize(, 2).Value = [{"Name","Count"}]
lr = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
lc = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
Set d = CreateObject("Scripting.Dictionary")
With d
  For Each c In w1.Range(w1.Cells(2, 2), w1.Cells(lr, lc))
    If c <> "" Then
      If .Exists(c.Value) Then
        .Item(c.Value) = .Item(c.Value) + 1
      Else
        .Add c.Value, 1
      End If
    End If
  Next c
  wA.Range("A2").Resize(.Count, 2).Value = WorksheetFunction.Transpose(Array(.Keys, .Items))
End With
lr2 = wA.Cells(Rows.Count, 1).End(xlUp).Row
wA.Range("A2:B" & lr2).Sort key1:=wA.Range("B2"), order1:=2, key2:=wA.Range("A2"), order2:=1
wA.Cells.EntireColumn.AutoFit
wA.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the GetUniques macro.

 

[gn82]

@ mump - Thanks for the sample. Looks nice though I was hoping for a simple way to get just the report without having to perform any additional tasks. At the moment I simply adjust the report at the end of every weekend based on who was present in the last hiking trip. It is a manual process and prone to error but it is quite fast so the only significant improvement would have been to get the report directly.

Hello gn82. Click here to download your sample file and give it a try. Will this suit your purpose?

Cheers!