Friday , Happy Friday…..Formula time…..

Ok, so I get a lot of downloaded, imported, exported data in all sorts of files and formats.  Sometimes tabular data can be imported into Excel as a single column of data a bit like the example below.

The data in this example contains Product information in Column A, with each record of the product contained in three consecutive cells; Product Name, Product Ref and Product Price.  We are aiming to convert this data so that each record appears as a single row with three columns.

So, here is a cheeky little formula that will help us.

=INDIRECT(“A” &COLUMN()-2+(ROW()-1)*3)

So, the formula explained….

As my formula is starting in Column C  this part of the formula COLUMN()-2 will give a result of 1 and will increment up by one for each cell that the formula is dragged to the right.

But we also need to increase the Row reference by 3 for each row that the formula is dragged into which is this part: (ROW()-1)*3

The INDIRECT part of the formula allows us to use our cell reference as a string , it evaluates the reference and shows its content.

If you have more than three rows then you need to change the ROW part of the formula to refelt this for example if you had five rows then the (ROW()-1)*3 will change to (ROW()-1)*5

The result once we drag our formula is  our data being transformed into a data table….hoorraayyyy….

 

 

 

Want to Learn More Formulas?

Join the Excel Formulas Crash Course. If you want to learn SUMIFS, SUMPRODUCT, OFFSET and 40 other day to day formulas, then consider Chandoo’s Excel Formula Crash Course.

It has 31 lessons split in to 6 modules and makes you awesome in Excel formulas.