逗号将值分隔为Excel中的列

I have multiple comma separated rows in excel with drugs name e.g.

我在excel中有多个逗号分隔的行,其中包含药物名称,例如

1) drug1,drug2,drug3,drug4

2) drug1,drug2,drug3,drug5

3) drug1,drug2,drug5,drug6

What I want to do is make these rows column such that all these rows distinct values becomes column. So in our case drug1,drug2,drug3,drug4,drug5,drug6 will become column and in rows they should have 0 if that row doesn't have that drug and 1 if it has that drug in that row.

我想要做的是使这些行列,使所有这些行不同的值成为列。因此,在我们的案例中,药物1,药物2,药物3,药物4,药物5,药物6将成为列,如果该行没有该药物,则它们应该为0,如果该行中含有该药物则应为1。

Any help will be appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

Here's a screenshot of how I would do it in multiple steps.

这是我将如何在多个步骤中执行此操作的屏幕截图。

1. split the data text to columns (data menu, text to columns)

将数据文本拆分为列(数据菜单,文本到列)

2. copy data into a single column (B) (copy range B2:E4) into B10, then copy out of C10-End of range and put back into B13 etc) (and have 2 columns one for rowID and one for value

将数据复制到单个列(B)(复制范围B2:E4)到B10,然后复制出C10-范围结束并放回到B13等)(并且有2列用于rowID,1列用于值

3. For rows 10,11,12 manually 1-3. copy and paste using ctrl-down arrow to fill in each series until all filled in .

对于行10,11,12手动1-3。使用ctrl-down箭头复制并粘贴以填充每个系列,直到全部填写完毕。

4. generate pivot Table (layout included now.)

生成数据透视表(现在包括布局。)

This is assumign this is a one time thing and throw away. If this has to be done repeatedly I'd macro it.

这是偶然的事情,扔掉了。如果必须重复这样做,我会把它变成宏。