Data conversion to table

  • Hi All,
    I want to convert my available data which is already being updated from other sheets to pivot format continuously. Suppose. I already have this data in following sheet.
    [TABLE="class: cms_table_outer_border, width: 500"]

    [tr]


    [td]

    Inv #

    [/td]


    [td]

    Name

    [/td]


    [td]

    Address

    [/td]


    [td]

    Area

    [/td]


    [td]

    VARIETY A

    [/td]


    [td]

    VARIETY B

    [/td]


    [td]

    VARIETY C

    [/td]


    [td]

    AMOUNT

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    A

    [/td]


    [td]

    Z

    [/td]


    [td]

    AA

    [/td]


    [td]

    5

    [/td]


    [td][/td]


    [td]

    10

    [/td]


    [td]

    1000

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    B

    [/td]


    [td]

    Y

    [/td]


    [td]

    BB

    [/td]


    [td][/td]


    [td]

    20

    [/td]


    [td][/td]


    [td]

    1500

    [/td]


    [/tr]


    [/TABLE]


    Now i want to link this data in a new table which i can use for reporting as pivot table in following sequence.
    [TABLE="class: cms_table_outer_border, width: 500"]

    [tr]


    [td]

    INV #

    [/td]


    [td]

    NAME

    [/td]


    [td]

    ADDRESS

    [/td]


    [td]

    AREA

    [/td]


    [td]

    VARIETIES

    [/td]


    [td]

    QTY

    [/td]


    [td]

    RATE

    [/td]


    [td]

    AMOUNT

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    A

    [/td]


    [td]

    Z

    [/td]


    [td]

    AA

    [/td]


    [td]

    VAR-A

    [/td]


    [td]

    5

    [/td]


    [td]

    60

    [/td]


    [td]

    300

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    A

    [/td]


    [td]

    Z

    [/td]


    [td]

    AA

    [/td]


    [td]

    VAR-C

    [/td]


    [td]

    10

    [/td]


    [td]

    70

    [/td]


    [td]

    700

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    B

    [/td]


    [td]

    Y

    [/td]


    [td]

    BB

    [/td]


    [td]

    VAR-B

    [/td]


    [td]

    20

    [/td]


    [td]

    75

    [/td]


    [td]

    1500

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]



    Can Anyone help Using Excel, VB or whatsoever. Waiting for early response...



    http://www.mrexcel.com/forum/e…ata-conversion-table.html


  • Re: Data conversion to table


    Try to upload your sample workbook, click "Go Advanced" button and find Paperclip button to attach your workbook.. and explain your desired results..


    thanks

  • Re: Data conversion to table


    The data is already a table, except you should remove the empty rows. ATable will expand as data is added automatically so is ideal as a source for a PivotTable. I can't actually see what the othe table is created from.

  • Re: Data conversion to table


    Quote from royUK;727793

    The data is already a table, except you should remove the empty rows. ATable will expand as data is added automatically so is ideal as a source for a PivotTable. I can't actually see what the othe table is created from.


    yes dear you are right but,
    1- Source data if used for pivot table, cannot fulfill requirements because variety is in one row.
    2- Many cells in Headers of Source data contains Same values which is not allowed in table.
    3- I use source data for some other reporting & linkage to other sheets so i need to link this data in pivot table from source in same or other sheet....



    other table is sample, created manually. I want data given in first 4-5 rows to be converted/Linked in table which is given below. (Note: these 4-5 rows and table (Source & Destination) are just sample actual data is much more than this)..



    I am beginner in VB so i think maybe some VB code may solve this problem.. plz i have to do this..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!