Count unique text values based on condition in another column

[dodgeratcools]

I want to create a summary from the following table : <o></o>
<o> </o>

Date<o></o>	Name<o></o>	Location<o></o>
19-feb<o></o>	John<o></o>	DS1<o></o>
19-feb<o></o>	John<o></o>	DS1<o></o>
19-feb<o></o>	John<o></o>	DS2<o></o>

<TBODY>
</TBODY>

<o> </o>
By adding a condition [if John made 3 observation cards at the same day , then each unique site will be considered as 1 site visit] so at the end it will look like the following table :<o></o>
<o> </o>

Name<o></o>	No. Of cards For February<o></o>​
John<o></o>	2<o></o>​
William<o></o>	1<o></o>​
Mark<o></o>	2<o></o>​

<TBODY> </TBODY>

<o> </o>
The register I am using for one year and have several sheets each sheet reflect a particular month , any idea whether it’s a formula to calculate this or a macro it will be great as long as it does the job<o></o>
Appreciate any help

 

[Teeroy]

Have you thought about using a Pivot Table? You will probably need one for each sheet.

 

[dispelthemyth]

Add a helper column at the end as below


Sheet1

*	A	B	C	D
1	Date	Name	Location	Helper column
2	19/02/2014	John	DS1	0.33
3	19/02/2014	John	DS1	0.33
4	19/02/2014	John	DS2	0.33
5	19/02/2014	Joan	DS1	0.50
6	19/02/2014	Joan	DS2	0.50
7	20/02/2014	Joan	DS3	1.00
8	19/02/2014	Peter	DS2	1.00
9	20/02/2014	Peter	DS1	0.50
10	20/02/2014	Peter	DS2	0.50

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =1/COUNTIFS(B:B,B2,A:A,A2) D3 =1/COUNTIFS(B:B,B3,A:A,A3) D4 =1/COUNTIFS(B:B,B4,A:A,A4) D5 =1/COUNTIFS(B:B,B5,A:A,A5) D6 =1/COUNTIFS(B:B,B6,A:A,A6) D7 =1/COUNTIFS(B:B,B7,A:A,A7) D8 =1/COUNTIFS(B:B,B8,A:A,A8) D9 =1/COUNTIFS(B:B,B9,A:A,A9) D10 =1/COUNTIFS(B:B,B10,A:A,A10) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Then add a pivot table to it and put

the name in the row field
Date in the column field and
the helper column in the value/data field


In the column field you will likely have loads of dates for each month you are doing it for, to make this just be a month

select on of the sates on the pivot table and on the ribbon you should see pivot tools at the end, select options then group selection and select month (put in an appropriate start and end date)


you will get something like the below


Sheet4

*	A	B	C
1
2
3	Sum of Helper column	Column Labels
4	Row Labels	Feb
5	Joan	2
6	John	1
7	Peter	2
8
9
10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:114px;"><col style="width:79px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4