Log In
Register
Home
April 9, 2020
Offline
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...
October 5, 2010
Offline
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
April 9, 2020
Offline
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...
October 5, 2010
Offline
Hi Gokhan,
No file is attached to your reply.
Did you try my file?
Regards
Phil
April 9, 2020
Offline
Ciao Phil; sorry; the file is attached. I tried but something wrong on my formulas I guess.
July 16, 2010
Offline
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
April 9, 2020
Offline
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...
July 16, 2010
Offline
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
1 Guest(s)
