Need Help with Arranging Excel Data! (pivots, transposing, etc)
 

Hello! For all those excellent macro/VBA-masterminds, I am struggling a LOT with an issue, and could use any help if necessary...so 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 see 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... 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 PL (for that Place)-OilType(Vol) | 2nd PL-OilType(Vol) | 3rd PL-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!!!