Converting Data for Databse Spreadsheet

Hello,

I am really stuck on this and i could do with some help converting thousands of lines of data in excel into a totally different format for our company's move to a database management, system.

The tricky part is, our existing data works as follows, property reference (UPRN) runs down a column, elements run along the top row and the cells in between are for the data.. like so:


The database required details to be listed quite differently, the above for one property would be like this.


The new format requires a new line for every UPRN (property) this is somewhat scaled down but should indicate the issue, I have tried all sorts of methods but it is very time consuming, my source includes some 200 element column headings and 7500 UPRN's per sheet, and i have about 5 sheets to process.

I would be really really grateful for any advice on what feature excel has that might be able to assist me in this.

Thank you

Excel 2010, PivotTable
No formulas, no code needed.
http://www.mediafire.com/view/d5dgfif3ewfr3cr/02_21_14.xlsx

Hello Herbds7,

That is Bloody excellent, worked a treat thank you so much for the fast response!

I do have one question which I omitted from the above example as I did not want to have a really long post, I have a subtotal column which, I would like, (if possible) to include in a separate column for each line again for each element, I am using Excel 2007 and it does not offer all for the options shown in your screen shot which might enable me to do this with ease, but might you be able to offer some help on the example below, same data just need one more column.


The required format would be as follows:


From your example, I can work out the follow, your Row = UPRN, Column = Element, Value = QTY and the Page 1 is the separate tables, and that works on a reduced set of data I tried it on. I went on to add the above total columns to your example data to see if I could pull the totals separately, but no amount of moving field about in the pivot manager gave me the right results.

Thank you in advance for any help on this!

Excel 2010 PivotTable
Concatenate > Text to Columns
Reload link.

Hello Herbds7,

Sorry for taking so long to get back to you, thanks for the details.

I truied this on a couple of test sheets which worked so that again is great.

Thank you for responding so quickly each time!