In this tutorial, you will learn how to clean the excel data into the right matrix.
Sometimes when you copy paste the data from other sources, all the pasted data comes in a disordered manner, into a single column .
Now let us see how to arrange the data into correct columns using the formula.
In this example, let us consider data of some people and their living places, which has come into a single column after extracting from some source.
Now let us arrange this data by name and their corresponding State name adjacent to each other.
Before we get into the exact process of cleaning up, Just make sure that our first entry of the data range starts from the odd row number, here it is 5th Row.
Here, I would be using combination of two function ROW and MOD.
As discussed earlier, ROW function gives the row number of the cell selected and MOD gives the remainder of the number.
Step 1: Copy the entire Data from Column C and paste it from cell H5.
Step 2: In cell I5, type the formula as
here, ROW(H5) gives the row number of cell H5, i.e. 5
MOD(5,2) gives the remainder as 1 and by IF condition, as it is true it should give the result as H6, if false it will