Need Help with Arranging Excel Data!

Microsoft Excel 2003 (full product)
July 21, 2010 at 22:14:55
Specs: Windows 7
Hello! For all those excellent macro/VBA-masterminds, I am struggling a LOT with an issue, and could use any help if here is a challenge! I have a long pivot table in Excel (that was compiled for a VERY big data file), and I have to now get the info from that pivot table into a special type of layout/format. Here is an example of what I have compiled in the pivot:

Place | Pipeline | OilType | Volume
x1 | PL#1 | A | 1
(blank) | PL#2 | B | 2
x2 | PL#3 | C | 3
x3 | PL#4 | D | 4
(blank) | (blank) | E | 5
x4 | PL#5 | F | 6
(blank) | (blank) | G | 7
(blank) | PL#6 | H | 8

...and it continues on like this, with varying spaces in between the Places and PLs (there is ALWAYS an Oiltype and an a corresponding Volume, though) Just to explain this chart, real quick, OilTypes F&G have volumes 6&7, respectively, and both run on PL#5 into x4.... and that is how it can be read (also noting that in addition, OilType H which has Volume 8, ALSO runs into x4, but through PL#6!)

BUT HERE IS WHAT I NEED.....what I need is for it to be transposed with all info corresponding back to the 'Place' all onto ONE ROW...and you need to be able to tell which 'OilType' and direct corresponding 'Volume' are associated with which Pipeline# of that Station. So for example, here is what the final result needs to be ( | = column separator, each new line is a new row):

Place | 1st Pipeline-OilType(Vol) | 2nd Pipeline-OilType(Vol) | 3rd Pipeline-OilType(Vol)
x1 | PL#1-A(1) | PL#2-B(2) | --
x2 | PL#3-C(3) | --
x3 | PL#4-D(4) | PL#4-E(5) | --
x4 | PL#5-F(6) | PL#5-G(7) | PL#6-H(8)

It should be a simple task, but the problem is the data within the rows vary a lot. For example, 'OilType' and 'Volume' must ALWAYS be together (because they directly correspond to one another), but the pipeline for each Place can vary, leaving lots of blank spaces within the pivot chart.

I have already figured out a way to get PL#-Type(Vol) altogether in one cell, but it doesn't delete what is used from underneath it. And I cannot figure out how to transpose it in such a manner.

Basically, I was wondering if anyone had any idea? Any help at all would be greatly appreciated, I have been stuck messing with this for so long, now!

Thanks so much!

See More: Need Help with Arranging Excel Data!

July 22, 2010 at 06:20:16

I have written a macro that converts the data.
It works with the sample data you posted.
I suspect that the actual data may have subtle, or not so subtle differences compared to the posted example, so at this stage I suggest that you test the macro and report back on what worked and what didn't.

A post of some actual source data used and the actual out put would help (change names etc. for confidentiality but don't change format).

When posting please use the Pre Icon above the reply box - enter your data between the <pre> and </pre> tags, and it more or less maintains the layout. Use Preview and then check 'Check To Show Confirmation Page Again', to review your layout further times. Adding tab spaces can be done by copying an existing tab space and pasting it - the Tab key won't work.

Here is the source and output using the macro:

	A	B		C	D
1	Place	Pipeline	OilType	Volume
2	x1	PL#1		A	1
3		PL#2		B	2
4	x2	PL#3		C	3
5	x3	PL#4		D	4
6				E	5
7	x4	PL#5		F	6
8				G	7
9		PL#6		H	8

	G	H	I	J	K	L	M	N	O	P
2	x1	PL#1	A	1	PL#2	B	2			
3	x2	PL#3	C	3						
4	x3	PL#4	D	4	PL#4	E	5			
5	x4	PL#5	F	6	PL#5	G	7	PL#6	H	8

I suggest running it from a button on the worksheet

On the Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Sort' or something else suitable
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestRowOffst As Integer
Dim intDestColOffst As Integer
Dim intDestTotalRows As Integer
Dim strDestName As String
Dim blnDestPresent As Boolean
Dim strCrntDestName As String
Dim strCrntPLName As String
Dim n As Integer

'As column C (oil type) is always populated use this
'to size the source data area

'set start of data row (in column C)
'assumes one header row - change as required
Set rngStart = ActiveSheet.Range("C2")
'find end of data
Set rngEnd = ActiveSheet.Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'set start cell for consolidated result table
Set rngDestStart = ActiveSheet.Range("G2")

'initialize destination offset counters
intDestRowOffst = 0
intDestColOffst = 0

'loop through source data to create a list of destinations
'for the destination table
'remember that we are using column C as the 'base'
For Each rngCell In Range(rngStart, rngEnd)
    'test if column A (offset -2 from C) is not empty
    strDestName = rngCell.Offset(0, -2).Text
    If strDestName <> "" Then
        'we have a value
        'see if it's already in the destination
        blnDestPresent = False
        For n = 1 To intDestRowOffst
            If rngDestStart.Offset(n, 0) = strDestName Then
                'flag present
                blnDestPresent = True
            End If
        Next n
        'if not present add it
        If blnDestPresent = False Then
            rngDestStart.Offset(intDestRowOffst).Value = strDestName
            'increment destination counter
            intDestRowOffst = intDestRowOffst + 1
        End If
    End If
Next rngCell

'save number of destination rows used
intDestTotalRows = intDestRowOffst

'now get data from each row
For Each rngCell In Range(rngStart, rngEnd)
    'save a current destination name
    'use it until a new name is found
    If rngCell.Offset(0, -2) <> "" Then
        strCrntDestName = rngCell.Offset(0, -2).Text
        'reset destination column offset as we have a new destination name
        intDestColOffst = 1
    End If
    'save a current pipeline number from column B
    If rngCell.Offset(0, -1).Text <> strCrntPLName And _
           rngCell.Offset(0, -1).Text <> "" Then
        strCrntPLName = rngCell.Offset(0, -1).Text
    End If
    'move pipeline and oil data to destination table
    'find row in destination table
    For n = 0 To intDestTotalRows - 1
        If rngDestStart.Offset(n, 0).Text = strCrntDestName Then
            'save current PL name
            rngDestStart.Offset(n, intDestColOffst).Value _
                    = strCrntPLName
            intDestColOffst = intDestColOffst + 1
            'save Oil type (no offset as rngCell uses the oil type column)
            rngDestStart.Offset(n, intDestColOffst).Value _
                    = rngCell.Offset(0, 0).Value
            intDestColOffst = intDestColOffst + 1
            'saveVolume (offset one column to right of oil type)
            rngDestStart.Offset(n, intDestColOffst).Value _
                    = rngCell.Offset(0, 1).Value
            intDestColOffst = intDestColOffst + 1
        End If
    Next n
Next rngCell
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Edit start and cell location and destination table start in these lines:

'set start of data row (in column C)
'assumes one header row - change as required
Set rngStart = ActiveSheet.Range("C2")

'set start cell for consolidated result table
Set rngDestStart = ActiveSheet.Range("G2")

Note that the source data column chosen must have data in every row, and there must not be any other data below the table in that column as this is used to calculate the range of source data rows.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Sort' button to run the macro


Report •

July 22, 2010 at 07:27:45
Hi, Humar!

Thank you SO much for your help! That pretty much solved all of our problems with some rearranging. There is one small little issue that we need to ask for. Is there something that can be added into the macro that will put the corresponding PL-Oil Type(Vol) all into ONE cell, so that the original table is transformed into this:

	A	B	C	D
1	x1	PL#1-A (1)	PL#2-B (2)	-
2	x2	PL#3-C (3)	PL#4-E (5)	-
3	x3	PL#4-D (4)	PL#4-E (5)	-
4	x4	PL#5-F (6)	PL#5-G (7)	PL#6-H (8)

Let me know if this is possible. Thanks again so much for your help!!


Report •

July 22, 2010 at 08:22:46

Here is the modified output:

	G	H		I		J
2	x1	PL#1-A (1)	PL#2-B (2)	
3	x2	PL#3-C (3)		
4	x3	PL#4-D (4)	PL#4-E (5)	
5	x4	PL#5-F (6)	PL#5-G (7)	PL#6-H (8)

and here is the modified part of the code:

    For n = 0 To intDestTotalRows - 1
        If rngDestStart.Offset(n, 0).Text = strCrntDestName Then
            'save current PL name, Oil type & Volume in one cell
            'Oil type - no offset & Volume offset = +1
            rngDestStart.Offset(n, intDestColOffst).Value _
                    = strCrntPLName & "-" & _
                        rngCell.Offset(0, 0).Value & " (" & _
                        rngCell.Offset(0, 1).Value & ")"
            'increment column offset
            intDestColOffst = intDestColOffst + 1
        End If
    Next n


Report •
Related Solutions

Ask Question