Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I remove duplicate in a column but keep the record of the f
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!! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I remove duplicate in a column but keep the record of the f
Create a PivotTable,
Month on the Page Fields ( you can filter by month) Country & Customer on the Row Fields "sragor" wrote: 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!! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I remove duplicate in a column but keep the record of the f
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. -- Regards Roger Govier "sragor" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicate Record in more than one column | Excel Discussion (Misc queries) | |||
How to remove duplicate events in column | New Users to Excel | |||
delete duplicate record but only determine 1 column data | Excel Worksheet Functions | |||
How to remove duplicate entries in column? | Excel Discussion (Misc queries) | |||
How can I automatically remove duplicate data within a column? | Excel Discussion (Misc queries) |