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

Sample Sheet attached. this data is posted in table manually i want it to be done automatically from above data...

Re: Data conversion to table

Anyone ???

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..