• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Formula Help for Duplicate Count in 2 Columns|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Formula Help for Duplicate Count in 2 Columns|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Formula Help for Duplicate Count in…
sp_PrintTopic sp_TopicIcon
Formula Help for Duplicate Count in 2 Columns
Avatar
Gokhan Kuzey
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 9, 2020
sp_UserOfflineSmall Offline
1
September 30, 2020 - 11:14 pm
sp_Permalink sp_Print

Dears; as you can see in attach file; I need your advise. On data sheet you can see the people who are working for job-1 & job-2. As you can see some of them working both jobs in a same day. I need to know somehow (formula or pivot) how many people working on those days. When you go to pivot table you will understand what I mean (Please check column E). Does anyone can help me for that issue? Many thanks...

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
October 1, 2020 - 2:10 pm
sp_Permalink sp_Print

Hi,

This can be done in a Power Pivot Pivot Table.  Have you got PP? What version of Excel do you have?

See attached for solution, and this post to describe the process

Excel PivotTable Distinct Count

I've also rearranged the data into a table in proper tabular format.

Regards

Phil

Avatar
Gokhan Kuzey
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 9, 2020
sp_UserOfflineSmall Offline
3
October 1, 2020 - 2:53 pm
sp_Permalink sp_Print sp_EditHistory

Dear Philip;

Thank for your quick reply. I added a file which I am exactly working on it. I tried the study but couldn't reach the correct welder numbers. Actually on "Workshop Progress" sheet we have welder column (AB) but regarding that I added two columns (AN,AO). I am not sure without that columns we can get the correct number of welders by the way. My problem is that I want to see on the same pivot (Welder&Dia sheet), welding date (column AC), Inch (column R) and welder numbers (Column AB or column AN/AO). Can you please help me for that? 

Note: Please remind that some welders are duplicated one the same day which we shouldn't count. If it is possible to don't show the blank cell information.

Many thanks...

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
October 1, 2020 - 3:37 pm
sp_Permalink sp_Print

Hi Gokhan,

No file is attached to your reply.

Did you try my file?

Regards

Phil

Avatar
Gokhan Kuzey
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 9, 2020
sp_UserOfflineSmall Offline
5
October 1, 2020 - 4:08 pm
sp_Permalink sp_Print

Ciao Phil; sorry; the file is attached. I tried but something wrong on my formulas I guess.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
October 1, 2020 - 8:17 pm
sp_Permalink sp_Print

Hi Gokhan,

In Phil's example file he has split welder 1 and 2 onto separate rows, not separate columns as you have done. You need to format your data in a tabular format like Phil has in his file.

Then you can use PivotTables to summarise the data and get your counts, like Phil's example. Note: you haven't said how the Inches should be split between welders 1 and 2. e.g. row 31 has 24 inches and 2 welders. Do they get 12 inches each or 24 or something else?

Also, if a welder appears multiple times on the same date, which inches do you count for them?

Mynda

Avatar
Gokhan Kuzey
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 9, 2020
sp_UserOfflineSmall Offline
7
October 1, 2020 - 11:00 pm
sp_Permalink sp_Print

Ciao Mynda;

Good to hear you. My first problem to see how many welders we have per day. Inch I can handle in another basic pivot. No problem. The table is welding database so more less at construction sites; table should be like that. I can't change it but if you prefer to do it easily like Phil format; I am glad to learn it also.

Thanks...

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
October 2, 2020 - 3:26 pm
sp_Permalink sp_Print

Hi Gokhan,

You can use Power Query to fix the layout of the data into a tabular format enabling you to count the number of welders per day. See Sheet1 of the attached file where I've fixed the layout of the Workshop Progress data and then used a PivotTable to count the number of welders per welding date.

I hope that points you in the right direction. If you'd like to learn how to use Power Query, please consider my Power Query course.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Nada Perovic, Denise Lloyd
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.