PDA

View Full Version : Converting Data for Databse Spreadsheet



Sebbie
2014-02-21, 09:22 AM
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:



UPRN

Roof Tiling

Leadwork

PVCu

Kerb edgings



88102

1

21

12

7



88104

1

2

12

8



88108

1

15

12

1



88109

1

5

12

3




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



UPRN

Element

QTY



88102

Roof Tiling

1



88102

Leadwork

21



88102

PVCu

12



88102

Kerb edgings

7




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

Herbds7
2014-02-21, 02:52 PM
Excel 2010, PivotTable
No formulas, no code needed.
http://www.mediafire.com/view/d5dgfif3ewfr3cr/02_21_14.xlsx

Sebbie
2014-02-21, 03:54 PM
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.



UPRN

Roof Felt M2

TOTAL

Fascia LM

TOTAL



28576

9

187.00

9

141.00



61245
5
128.30
4
88.00


90737
6
113.00
2
119.00


33459
5
115.00
9
114.00



The required format would be as follows:



UPRN

Element

QTY

Total



28576

Roof Felt M2
9
187.00


28576

Fascia LM
9
141.00


61245
Roof Felt M2
5
128.30


61245
Fascia LM
4
88.00



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!

Herbds7
2014-02-21, 04:58 PM
Excel 2010 PivotTable
Concatenate > Text to Columns
Reload link.

Sebbie
2014-02-28, 07:56 AM
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!