How to Rebuild Data, Case Study

Sometimes we deal with data that is not standardized that we might give up, you should learn how to rebuild you data, OK, let's start the case.

Source Data

Source Data

Rebuild Data

Rebuild Data

INDEX formula

You might think use INDEX() function, right! but how?

The INDEX() function syntax:
INDEX(array, row_num, [column_num])

After analysis, you may find:

row_num = 1;3;5
column_num =1,2,3,4,5,6,7

row_num

row_num = ODD number = 2n + 1
In Excel, you can use ROW() function instead n, so, row_num = 2*ROW() + 1

But, we need row_num repeat 7 times, OK, INT(ROW()/7), just repeat 7 times, let's see below picture to show the process:

row_num

So, row_num completed formula: =2*INT((ROW()-1)/7)+1

column_num

In this, we use MOD() function to create our data, see below:

column_num

So, column_num completed formula: =MOD(ROW()-1,7)+1

Completed formula

I1 =INDEX($A$1:$G$6,2*INT((ROW()-1)/7)+1,MOD(ROW()-1,7)+1)
J1 =INDEX($A$1:$G$6,2*(INT((ROW()-1)/7)+1),MOD(ROW()-1,7)+1)

Comments

  1. stephan says

    I used two columns as counters, one for row# and one for column#. row# starts at =2 and increases by 2 periodically when next col# would be >7. Column number increases by 1 every row, until next col#>7 in which case it resets to 1.

Leave a Reply

Your email address will not be published. Required fields are marked *