Decompose a column into multiple columns with TRUE/FALSE values instead

Question

Here is a very small version of what I want to achieve but not able to figure out what exactly should I Google search for -

Column A | Column B
Alice    |  Present
Bob      |  Absent

Should translate to -

          Present    Absent
Alice      True
Bob                   True

So basically, each unique value in column B should become a new column and the intersection of row and column should be True.

Further extension - Its even more involved, but is this something achievable?-

Convert this -

Column A |  HashTag 1 | HashTag 2 | HashTag 3
Alice    |   corona   | staySafe  | 
Bob      |   lucky    | corona    |  blessed  

to

          corona    staySafe   lucky   blessed 
Alice      True       True
Bob        True                 True    True

Here all HashTag columns are of same weight, but each tag is in different column.

Thanks in advance.

Answer 1

For your first example, where the values are known, you can just create a new column alongside your existing data for each, like this: =if($A1="Alice","Present","Absent")

Having done so, if you want to you can copy the data then 'paste values' back into the same cells and then delete the original B column then it should be as you wanted in the first place.

Your second example could be done in the same way if the potential values are limited. It could be adapted for all use cases using the unique() and transpose() functions. If you want me to spend further time explaining then let me know.

Good luck.

Answer 2

First Formula

=ARRAYFORMULA(
   VSTACK(
     HSTACK("Name", "Present", "Absent"), 
     HSTACK(A1:A10, 
       IF(B1:B10="Present",true,), 
       IF(B1:B10="Absent",true,))))

Or the equivalent using braces {} to create arrays:

INDEX(
  {{"Name", "Present", "Absent"};
    {A1:A10, IF(B1:B10="Present",true,), 
       IF(B1:B10="Absent",true,)}})

Second Formula

=LET(rng,A1:D, 
   arr,FILTER(rng,INDEX(rng,,1)<>""), 
   vals,TRANSPOSE(QUERY(TRANSPOSE(arr), "OFFSET 1")) ,
   unq,TOROW(UNIQUE(FLATTEN(vals)),1), 
 {{""; INDEX(arr,,1)}, {unq; MAKEARRAY(ROWS(arr), COLUMNS(unq), 
   LAMBDA(r, c, IFNA(MATCH(INDEX(unq,, c),INDEX(vals, r),0)>0)))}})