Today we are going to discuss numerous methods in Excel about Table Normalization. Input data and expected result is provided here to get an idea about our requirement.
Method-1:
=VSTACK(B2:C2,DROP(TEXTSPLIT(CONCAT(
SUBSTITUTE(", "&C3:C8,",","|"&B3:B8))," ","|"),1))
Let me start this from scratch,
=SUBSTITUTE(", "&C3:C8,",","|"&B3:B8)
Arguments of SUBSTITUTE function,
=SUBSTITUTE(text,old_text,new_text,[instance_num])
in text argument,
Concatenate comma and space inside double quotes (", ") with C3:C8.
{", Maths, Physics";", English, Accountancy, CS";", History, Civics, Chemistry";", Geography, Chemistry, Biology";", Civics, History";", Economics, Biology"}
in old_text argument,
use comma inside double quotes (",").
in new_text argument,
Concatenate pipe symbol inside double quotes ("|") with B3:B8.
{"|A";"|B";"|C";"|D";"|E";"|F"}
If we replace the comma in text argument with new text, output will look like below,
{"|A Maths|A Physics";"|B English|B Accountancy|B CS";"|C History|C Civics|C Chemistry";"|D Geography|D Chemistry|D Biology";"|E Civics|E History";"|F Economics|F Biology"}
CONCAT function will concatenate this array of texts into one text value as shown below,
"|A Maths|A Physics|B English|B Accountancy|B CS|C History|C Civics|C Chemistry|D Geography|D Chemistry|D Biology|E Civics|E History|F Economics|F Biology"
Here is the argument details of TEXTSPLIT function,
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])
TEXTSPLIT function will help us to split the above text by space (" ") as column delimiter and pipe symbol ("|") as row delimiter.
Output of TEXTSPLIT function is,
{"",#N/A;"A","Maths";"A","Physics";"B","English";"B","Accountancy";"B","CS";"C","History";"C","Civics";"C","Chemistry";"D","Geography";"D","Chemistry";"D","Biology";"E","Civics";"E","History";"F","Economics";"F","Biology"}
To get rid of the first row with error, we can use DROP function, specify number of rows to dropped in 2nd argument, in our case it is 1.
To stack the column headers, we can use VSTACK function.
Method-2:
=REDUCE(B2:C2,C3:C8,LAMBDA(a,v,LET(stu,OFFSET(v,,-1),VSTACK(a,IF({1,0},stu,
TEXTSPLIT(v,,", "))))))
This method uses LAMBDA helper function REDUCE to accomplish our task.
Arguments of REDUCE function,
=REDUCE(initial_value,array,function)
This function will deliver final result after the iteration. To pick each result during iteration, we can use VSTACK function inside function argument.
in initial value argument, feed {"Student","Subject"} column headers of our output,
in array argument, feed C3:C8 which is the subject column in our input data,
in function argument,
use LAMBDA with 2 variables, we can name it anything, but in our case,
'a' -> accumulator,
'v' -> current value.
in first iteration,
a -> {"Student","Subject"}
v -> "Maths, Physics"
OFFSET(v,,-1) will help us to move one column in left side pick value from that range. This will pick 'A' from B3 cell and we name it as 'stu'.
in logical test argument of IF function, we fed {1,0}.
IF function will look 1 as TRUE and zero as FALSE, both 1 and 0 wrapped inside curly braces, so output of IF function will be an array. This construction will help us to stack 2 results horizontally. (This is the replacement of HSTACK function).
If condition is TRUE then deliver student name else deliver TEXTSPLIT(v,,", "). Here we split current value which is "Maths, Physics" with comma and space as row delimiter.
Output of TEXTSPLIT function is,
{"Maths";"Physics"}
Output of IF function is,
{"A","Maths";"A","Physics"}
These steps will be carried out for each iteration, and the output of each iteration will be stacked vertically. Final output of REDUCE function is,
{"Student","Subject";"A","Maths";"A","Physics";"B","English";"B","Accountancy";"B","CS";"C","History";"C","Civics";"C","Chemistry";"D","Geography";"D","Chemistry";"D","Biology";"E","Civics";"E","History";"F","Economics";"F","Biology"}.
Comments