Search code examples
excelmatrix

How to assign values from rows to columns with the same name in a matrix containing 1 and 0 in Excel?

I have a large dataset of habitats. It resembles the one shown in the picture. It consists of multiple columns containing habitats (either one per column or separated into individual cells). Each row contains a different number of habitats. Then, I have columns named after these habitats separately. I need Excel to assign the number 1 to cells that match the habitat. For example, if there is a habitat named L3 in a row, I want to have 1 in the column with this name, and 0 elsewhere.

I was considering VLOOKUP/XLOOKUP, but apparently, this problem requires a more sophisticated solution.

enter image description here

This is an example of my dataset:

biotop         A1      A5
A1, L3, L5            
A5, A1               
L3, L5                
L6                    
A1

this is the desired output (there are many columns like those named A1 and A5):

biotop         A1      A5
A1, L3, L5     1       0
A5, A1         1       1
L3, L5         0       0
L6             0       0
A1             1       0
Solution

  • You can do this with simple formulas in excel

    Given your data in the orange, you can enter the formula

    `B2: =--ISNUMBER(FIND(B$1,$A2))`

    and fill down and to the right

    If your actual data in Column A is such that the simple FIND function will be confronted by ambiguity, more complex functions can be used to remove that ambiguity.

    enter image description here