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 ...
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.
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.
Hi JKPieterse
Thank you for replying me. I tried to do just like your suggestion, but the result seems like something wrong with the data. Am I missing any steps?
Before
After
Lolipop99 Yes, that is exactly what's expected. Now create a pivot table from that new table