Results 1 to 5 of 5

Thread: Converting Data for Databse Spreadsheet

  1. #1

    Converting Data for Databse Spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

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

  3. #3

    Smile

    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!

  4. #4
    Excel 2010 PivotTable
    Concatenate > Text to Columns
    Reload link.

  5. #5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •