Rearrange data

Also, the locations may increase in the future, and the months may extend upto January. I wanted a solution that will automatically check these two and give the output accordingly.

Thanks.

Manually - select a complete row such as Feb-Sep, then copy and paste special/transpose.  Or a macro could be created that would loop through the data and do the copy/paste special/transpose.
Or, use the indexing function in the destination data location - sample attached/formulas are in F2-G4. Indexing-sample.xls

You can use a Pivot Table for this, using the consolidate Multiple Data ranges option.

See attached.

This could be expanded to make the data ranges dynamic so that each time the table is refreshed it gets all data.

Thanks
Rob H Data-to-be-reformatted.xls

Can you also get the me the percentages (Data1) in an adjacent column using the pivot? I am unable to do that.

also, is there some way to take off the totals that are in between the rows(Eg: Feb Total)? Else I will have to manually delete them as I intend to take this output and put into Minitab for further statistical analysis.

Thanks.

Apologies, I hadn't spotted the different data types, percentage and numerical values.

The table in my example does have both but they are added together!!

I will keep looking at it. Are you averse to an extra column in the data tables determining data type, % or Number?

Thanks
Rob H

See attached amended file.

Original data with added column - Sheet1.

Amended data layout - sheet3. I have done this with a simple copy & paste as it was simple with the volume of data but this could done with formulas if the real data is not as easy to just copy & paste.

Table - sheet4. This had row headers of "Sum of Feb", "Sum of Mar", etc which I was able to change with a Edit Replace of "Sum of" with "". I left the leading space on the month as this makes it different to the header of the column from which the data came.

If you need the sheet3 data layout to be formula driven then let us know and we will be able to do something with lookups or index/match.

I have added columns for the extra months. If you need more, as you get new months of data the range for the pivot can be made dynamic so that they will appear in the list on the right on refresh and will need dragging into the data area.

Thanks
Rob H

Data-to-be-reformatted.xls

Hi Rob,

This looks to be exactly what I need. I was hoping to customize your solution to my specific business data, but for some reason I was not able to modify or refresh the pivot table.

Attached is the original data which I needed to format,  in the earlier mentioned format. It has 26 Locations and there are 3 sets of data. Earlier you had named the sets as NUM and PC. Now there are Variance, A% and FX.

Could you do the same formatting that you did earlier, on this data?. Also, at this point, the month data is only from Mar to August. So you dont have to be concerned about other months.

I have never pivoted multiple sets of data. Let me know if there is some place I can read further to do that.

Let me know if I am not clear anywhere.

Thanks.
Final-data-to-be-reformatted.xls

I suspect the pivot table would not refresh due to [] in the file name when downloaded from the net.

If you save as a file name without the brackets it should be OK.

The last upload with the two columns was not from multiple sets but from the one combined set of data. I can work on making that a dynamic combo if so required.

The Pivot from multiple sets is actually fairly simple. IN the Pivot Table Wizard step 1 there is the option for multiple sets. You then just follow the remaining steps through. The only step that is different really is specifying the data areas but that is as simple as selecting the area and clicking to add to a list.

Thanks
Rob H

Thank you so much!!