I have data showing different information relating to sale of
poultry goods from the year 1990 onwards spread over around 10000
rows in a worksheet as detailed below

5 Columns are used for the name of the customer, address1, address2,
address3, address4

one column each for Receipt No; Bill No; Delivery Challan No; Demand
Draft No; Date of Demand Draft; Amount of Demand Draft etc etc.

We have mainly three catogories of poultry goods for sale viz. 1)
Hatching Eggs of different breeds; 2)Chicks/Birds of different
breeds; 3)Others which include items like feed, CDs, other items
from which revenue is geneated

For H.Egg category we have 6 coloumns to fill the data of number of
eggs sold under different breeds viz. with Vanaraja; Grama Priya;
KrishiBro; etc headings

Like wise around 8 coloumns to fill the date of nuumber of
chicks/birds sold under different breeds heading

Under other category we are using around 5 coloumns for sale of
other items

Thus around 40 columns are used in the worksheet in all

90% of customers purchase only one item and hence other columns are
to be kept blank

only 10% of customers purchase more than one item in one
bill/transaction

Coming to the point, now I want to reduce the above 6+8+5 number of
columns and change the placement of this entire data into 3 coloumns
only, the first and second column would be name of the catagory and
name of the item respectively (these will be filled through
validated drop down list in each row of transaction for future
sales) and the third and next column would be for the number sold

There should not be any problem for the customers data who purchased
only one item

Wherever customers had purchased more than one item, while his name
and address should be repeated equal to the number of items
purchased and posed in rows one below the other alongwith the number
of goods purchased in next column after category and item columns

I can continue to get lot of consolidated information through Pivot
Tables as I am already getting but more number of unnecessary
columns while the sale is only for one item is annoying me.

I hope my requirement is understood.

Can I change the entire data on one stroke by writing a macro or VBA
program. How best I can bring the data under different headings in
to one heading while using the name of the category and item in
previous two coloumns