Today, we will explore various Excel methods for de-normalizing a table using Excel functions. Input data and the expected result are provided to give an understanding of our requirements.
Method-1:
=LET(stu,B3:B17,sub,C3:C17,HSTACK(UNIQUE(stu),MAP(UNIQUE(stu),LAMBDA(x,
TEXTJOIN(", ",,FILTER(sub,stu=x))))))
Using the LET function, we assign the variable 'stu' to represent the range B3:B17, and the range C3:C17 is assigned to the variable 'sub'.
Use the UNIQUE function to obtain distinct items in the student column. The MAP function will assist in iterating over each element in the unique student list.
Argument of MAP function,
=MAP(array,lambda_or_array2,...)
In array argument, provide an array with distinct student names for iteration.
Within LAMBDA, define a variable, which in our instance is 'x'.
Our customized calculation is,
TEXTJOIN(", ",,FILTER(sub,stu=x))
in 1st iteration,
x -> "A"
We apply a filter to the subject column using "A" as the criterion in student column. This will result in our input data being filtered to show only the subject values for student "A".
{"Maths";"Physics"}
TEXTJOIN function will join above array with delimiter ", ".
Result of the first iteration is,
"Maths, Physics"
MAP function will perform these steps for each student to achieve our desired outcome.
Method-2:
=LET(stu,B3:B17,sub,C3:C17,HSTACK(UNIQUE(stu),SCAN("",UNIQUE(stu),LAMBDA(x,y,
ARRAYTOTEXT(FILTER(sub,stu=y))))))
This is similar to Method-1, but in this case, we utilize the SCAN function instead of the MAP function and the ARRAYTOTEXT function instead of the TEXJOIN function. But the concepts are same.
Method-3:
=GROUPBY(B2:B17,C2:C17,ARRAYTOTEXT,3,0)
We can replace all those steps in Method-1 with thumb size formula using GROUPBY function.
Arguments of this function,
=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
In row field argument, feed B2:B17 with headers,
In values argument, feed C2:C17 with headers,
In function argument, feed ARRAYTOTEXT,
All other arguments are optional; to retain headers, you can choose option 3 for the 4th argument.
This function will position the unique student names in the row field. As we are using text in the value argument, we can only perform concatenation with it. The ARRAYTOTEXT function takes an array of items and returns them as text, concatenated with a comma and space as the delimiter.
Comentarios