Hi,
I have a maintenance log tool with 3 major tables (preventive, backlog and troobleshoots).
Their columns are quite similar:
Reference/Type/Machine/category/subcategory/Task/Week/Technician/ Time spent (number I wish to sum in a pivot)
but not completely.
I need to analyse those and PowerPivot refuse to link category for example as it is a "many to many" relationship (the 3 columns of "category" do have duplicates and none has them all), which means to me I need a list "category" with unique values without duplicate.
I already have it for Type, Machine and technician thanks to a dropdown list input but not the other ones
A heavy option is to create a table and a macro would copy-paste all the reference one below another and delete blanks duplicates, then category, subcategory, task (thousands). I can then add this to my datamodel and do the pivot I am dreaming of.
As most columns are dynamic (I mean we add unique values from time to time), I need to rerun all the "copy-paste remove duplicate macro's" on each refresh of the pivot and I need to take all filters out before copying, which is a bit annoying.
I have the feeling I overcomplicate it but can't find an easy way to analyse the 3 logs.
How would you keep those updated lists? Or is there any better way to overcome this?
Thank you in advance for any input
I have a maintenance log tool with 3 major tables (preventive, backlog and troobleshoots).
Their columns are quite similar:
Reference/Type/Machine/category/subcategory/Task/Week/Technician/ Time spent (number I wish to sum in a pivot)
but not completely.
I need to analyse those and PowerPivot refuse to link category for example as it is a "many to many" relationship (the 3 columns of "category" do have duplicates and none has them all), which means to me I need a list "category" with unique values without duplicate.
I already have it for Type, Machine and technician thanks to a dropdown list input but not the other ones
A heavy option is to create a table and a macro would copy-paste all the reference one below another and delete blanks duplicates, then category, subcategory, task (thousands). I can then add this to my datamodel and do the pivot I am dreaming of.
As most columns are dynamic (I mean we add unique values from time to time), I need to rerun all the "copy-paste remove duplicate macro's" on each refresh of the pivot and I need to take all filters out before copying, which is a bit annoying.
I have the feeling I overcomplicate it but can't find an easy way to analyse the 3 logs.
How would you keep those updated lists? Or is there any better way to overcome this?
Thank you in advance for any input
Last edited:
