How do I remove duplicate in a column but keep the record of the f

  • Thread starter Thread starter sragor
  • Start date Start date
S

sragor

Hi,

I am given a whole list of Customers from different Countries who stayed in
my Hotel for different months in a financial year beginning with October. My
goal is to get generate a Monthly report of Customers (according to their
country)who stayed in my hotel, on the condition that Customer who stayed
earlier in the previous months of the financial year do not contribute to the
statistics in the subsequent months. A pivot table is probable, and I need to
count the heads at the same time. For example,

Given data:
Month Country Customer
Oct UK James Collin
Jan UK James Collin
May UK James Collin
May UK Steven D.
May UK Angie B.
May Japan Nakata
Aug Japan Nakata
Aug Japan Naomi


Expected result:
(Oct Report for UK)
Month Country Customer
Oct UK James Collin

(May Report for UK)-James Collin was not counted because he was counted for
Oct
Month Country Customer
May UK Steven D.
May UK Angie B.

(May Report for Japan)
Month Country Customer
May Japan Nakata

(Aug Report for Japan)-Nakata was not counted because he was counted in May
Month Country Customer
Aug Japan Naomi


Thank you. And any help will be greatly appreciated!!
 
Create a PivotTable,
Month on the Page Fields ( you can filter by month)
Country & Customer on the Row Fields
 
Hi

Add a 4th column to your source data with the formula
=IF(COUNTIF($C$2:C2,C2)>1,0,COUNTIF($C$2:C2,C2))
Call the column Count

Drag Month to Page area
Drag Country and Customer to Row Area
Drag Count to Data Area

Customer James Collin will show up in the list for May UK, but there will be
a 0 next to his name and the Count for the Month for Uk will be 2.
 
Back
Top