MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Change the placement of the data spread in several columns

  Asked By: Kristopher    Date: Nov 23    Category: MS Office    Views: 2007

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

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



1 Answer Found

Answer #1    Answered By: Silvia Chapman     Answered On: Nov 23

I don't think you would necessarily benefit from VBA with this. I
think that this just requires some brute force.

Insert the three blank new columns  that you want. Insert three
additional columns to sum the 6+8+5 values. From here it is just a
sorting game. Sort the records by the column that adds the 6 other
columns. These are all a single category, right? In the one blank
column you can now identify these records as the common category. You
can add Validation to this column after you have classified them.

Handling those records with multiple purchases:
Adding a new column just count the number of transactions for any
single line. This would allow you to isolate the single purchase
records. Sort the worksheet and separate these out. That would leave
you with 1,000 to 2,000 records.

Again, this can be managed by sorting. Those records with totals in
more than one category just need to be copied and pasted at the bottom
of the sheet.

Does this help at all? These instructions are based on my
interpretation of what you shared. I might be missing something.

Didn't find what you were looking for? Find more on Change the placement of the data spread in several columns Or get search suggestion and latest updates.