View original (French)

Complicated Gantt chart

Jerome_aaaaarh Posted messages 2 Registration date   Status Member Last intervention   -  
 Jerome_aaaaarh -
Hello everyone,

I’m having trouble creating a pivot table because my data is poorly structured.

Basically, I will have in a column:
- A a person
- B their name
- C their status
- E whether they entered or left, etc. in FEB 2019
- G whether they entered or left, etc. in MAR 2019
- I whether they entered or left, etc. in APR 2019
- ETC.

And what I want is to create a pivot table that in rows gives:
- My statuses
- the info on entry/exit, etc. from columns E, G, I, ETC.

And have as values the count of times I entered, left, etc., but by month so I can compare them.

But I can’t; it always shows the same data. I’m attaching the file so you can probably understand better than with my explanations

https://cijoint.org/r/58FguRCy#3UzOaBoKO0yD36IBT4fEg99UtXm/WaSEJHWjZH2+PXc=

Thanks for your help

6 answers

  1.
    cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
     
    Hello,

    Is this the kind of result?

    With Power Query, this is easily achievable (if you indeed have Excel version >= 2016)
    1
    1. Jerome_aaaaarh
       
      Hello, Thank you very much for your feedback and help. Actually, for now I have this in the database: 
      Personne Nom Statut févr-19 mars-19 1 DUPONT PH Entrée Entrée 2 MARTIN PC Présent Sortie 3 DURAND PH Entrée Présent
      But what I want is to create a pivot table with a base that allows it 
      Statut Situation oct-19 nov-19 déc-19 janv-20 PH Entrée 15 8 12 10 PH Sortie 7 11 9 14 PH Présent 350 347 350 346 PH Baisse QTT 20 8 0 4 PH Augment QTT 16 25 12 8 PC Entrée 4 6 3 5 PC Sortie 2 3 5 4 PC Présent 80 83 81 82
      But to do that I must redo my base but I don’t know exactly how to do it. I don’t know if it’s clearer now? Thank you very much for your help.
      0
  2. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
     

    Re-,

    What could this look like?

    1
    1. Jerome_aaaaarh
       

      Exactly :) thank you very much

      0
  3. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
     

    RE-,

    Or rather this?

    1
    1. Jerome_aaaaarh
       

      Hi again,

      I don’t see the difference?

      0
      1. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390 > Jerome_aaaaarh
         

        I assign -1 if "Sortie" or "non présent", and +1 otherwise

        But we can end up with negative totals (Attached: short-term contracts, December 2019) for example

        1
      2. Jerome_aaaaarh > cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention  
         

        Oh yes, it's good like that too :)

        0
  4. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
     

    Ok, so we’re going to use the solution from the post <3>

    But before, a few clarifications are necessary.

    I think I notice that this data comes from an "Access" database.

    Is the tab name still the same? (or what name is given to the tab?)

    Is there only one tab?

    Have you tried importing this database via Power Query? (in which case, you would only need to transform directly from that database)

    Re-reading you, with these precisions

    1
    1. Jerome_aaaaarh
       

      Thank you very much.

      It indeed comes from extracting from a BI base, but I wrote a formula to compare m-1 and m and obtain the Entries/Exits columns.

      Yes, the sheet name is the same but I didn’t perform the extraction and, above all, I added data manually. In the base extraction there were several tabs but I removed them (useless for me).

      Thank you very much,

      0
  6. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
     

    Ok,

    In the attached file, in the "Param" tab

    - You enter the path and file name in cell A2

    - You enter the tab name in cell A5

    And in the "TCD" tab, right-click inside the PivotTable, then "Refresh"

    If you get an error message, you need to configure your Excel application (once and for all) by following the instructions in the "Read Me" tab

    1
    1. Jerome_aaaaarh
       

      Thank you very much,

      I configured my Excel as indicated, but it says the link is not found

      Nevertheless I did put my path + the file name and my tab

      Path = C:\Users\4121759\Desktop\Entrée sortie par mois par année\Nouveau dossier

      File name = Effectifs PM entrée sortie - Copie sans f

      0
      1. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390 > Jerome_aaaaarh
         

        The file name seems incorrect to me (it should end with xlsb or xlsx or...).

        Rename it to "Essai.xls?" (replace the question mark with the actual format—the one you had ended with xlsb).

        0
  7. Jerome_aaaaarh
     

    It’s indeed xlsb

    But it still doesn’t work. Maybe because I put an underscore between the path and the file name _Essai.xlsb

    C:\Users\4121759\Desktop\Entrée sortie par mois par année\Nouveau dossier_Essai.xlsb

    0
    1. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390
       

      Ok, we’re going to do a trick to properly retrieve the right file

      In Excel, you click on "Data/From a file/From Excel Workbook"

      You search for your file, then the tab, and "Transform Data"

       And in the editor, step "Source", you retrieve the path and the name:

      And you paste this info into cell A2 (without the quotes)

      0
    2. Jerome_aaaaarh > cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention  
       

      I have an error message ... It says the table is not in the expected format

      0
    3. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390 > Jerome_aaaaarh
       

      Re-,

      I don't know why (well, yes, but...), but according to the versions, PQ doesn't like xlsb files (at work, I had Excel 2016, and I couldn't read these files)

      At home, xl2024, no problem

      Open the xlsb file, and save it in xlsx or xlsm format (note, you must not simply change the extension, but use "Save As...")

      And try again with this new format

      0
    4. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390 > cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention  
       

      Re-,

      From the preview, I can see this isn’t going to work...

      You didn’t provide a "faithful" example to your real file (the first column in line 4 does not include the title "Agent matricule"...

      Does the column of "Statuts" indeed be named "Statut Niveau 2 PM"?

      If not, provide a file with only the first 4 lines of the "BDD" tab

      1
    5. cousinhub29 Posted messages 1133 Registration date   Status Member Last intervention   390 > Jerome_aaaaarh
       

      Ok, the value "Agent..." is in column 20

      And the name of the tab is "BDD"

      1