We help IT Professionals succeed at work.

Rearrange data

j1981
j1981 asked
on
Hi,

The attached Excel contains Data that is available and the format in which I need them to be reformatted. Please help.

I believe the data is self-explanatory. Please revert if it is not.

Thanks.
Data-to-be-reformatted.xls
Comment
Watch Question

Author

Commented:
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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Finance Analyst
CERTIFIED EXPERT
Commented:
See attached.

Gave the three sets of data range names (VarData, PCData & FXData) (couldn't use % in range name for A%).

Formula on Amalgamated sheet use vlookup for location and use type column to determine which range name to look in. Vlookup can be a bit resource hungry on large sets of data but OK for a small set like this otherwise would have used INDEX but that would have got complicated.

Pivot is then just a single data set table on the amalgamated data.

Hope this is OK. Created in Excel 2007. If problems let me know as I will be back at a 2003 machine tomorrow.

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

Author

Commented:
Thank you so much!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.