Forum Discussion
Data analysis with multiple data in one cells
Hi all,
I have a question on how to do analysis if there is multiple data in one cells (picture below in reject type column). I tried to separate in 2 column but when I insert pivot chart and pivot table, it shows duplicate data. Anyone have an idea on how to solve this problem?
Thank you in advanced!
- do analysis
what kind of analysis？
could u show your expected result？ - Is there any relation between the # of rejections and the items in that Reject Type column?
Does a slash between two words separate two rejection types, just as the semi-colon seems to do?- Is there any relation between the # of rejections and the items in that Reject Type column?
Answer: Yes, it have relation. Reject quantity means the number of reject item, whereas reject type is the reason why the item is reject. Sometimes the reject type for the item reject can be more than 1 type. For example, data number 5 in the picture above.
Does a slash between two words separate two rejection types, just as the semi-colon seems to do?
Answer: The slash means it is 1 category of reject type or same/similar reject type. Semi-colon is 2 different types of reject. Meaning that the item have multiple reject type.- In dat case, use Data, From Table. Split the reject type column by the ; delimiter, select all columns to the left of the first reject type column and choose Transform, unpivot other columns. Now you have a table fit for use to create a pivot table.
