How to change data structure by month #'s

**stephme55** · 12-30-2015, 05:02 PM

Hello. This might be a complicated task that might need vba but maybe could be settled by a formula as well. I have sets in my rows and months in my columns. I would like to reformat the data content so that it's set up in month #'s. The first month is the first month with a % in it. So, for January in the attached sheet. The first month is the %'s from 2014 January (because that is the first month that has information for that data set) Is there a way that I can do this with code or formulas? My real data set is much larger.

**stephme55** · 12-30-2015, 05:04 PM

Another way of stating this, is that if there are 0's to the left of the data, pull the data from the right over those 0%s.

**FDibbins** · 12-30-2015, 06:21 PM

Actually, for Jan, the 1st column with a value in, is Nov 2013 (.0285...%), not Jan 2014

Put this ARRAY formula in B19 and copy down and across as needed...
=INDEX($B3:$AC3,SMALL(IF($B$3:$AC$3>0,COLUMN($B$3:$AC$3)-1),COLUMNS($A$1:A1)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If you want to start at something higher than 0, adjust the 0 (bolded) in my formula as needed (or, you could put that in it's own cell and reference it)

How to change data structure by month #'s

LinkBack

Thread Tools

Rate This Thread

Display

How to change data structure by month #'s

Re: How to change data structure by month #'s

Re: How to change data structure by month #'s

Thread Information

Users Browsing this Thread

Similar Threads

#VALUE! Error from one version to another (without any structure or content change)

Prevent structure of a range to change when row is inserted

Creating a Pyramid Hierarchy structure from a flat structure

need data to change from month to month

VBA to change structure of excel file

"convert" data with logfile structure to spreadsheet structure

Excel 2007 : Creating Macro's to Create Columns and turn change data structure

Bookmarks

Bookmarks

Posting Permissions