Excel or Access: how to group several lines in a table and insert contents in columns? ("split column")
- by Martin
I have a table containing data of sold products (shown in the example on the left):
Columns:
Number of the order
Product Name
Attribute - specifies what is given in the following field "value", e. g. Customer Name or Product Variant
Value - is the value of the Attribute
Count - is the number of products of this variant sold in the order
That means:
Product B has 2 variants "c" and "d"
Note that in Order 1 Product B was sold in Variant d only, because the letter "N" in field "D4" means "none".
Note, that in OrdnerNo 3 Product B was sold only in Variant c, because for Variant d field "D9" is "N"!!
This is confusing, but it is the structure of the original data (which I can not change).
I need a way to convert the table on the left in a table like that on the right:
one line for each product type
Order Number
Product Name
Customer Name
Count (number of products sold in this order)
Variant - this is the problem, as it has to be filled with the
So all rows with the same OrderNo and same product have to be grouped in to one, and
I hope it is clear what I need. I tried to do it with Pivot Tables, but that fails, as the Count is always in each line, no matter if it has Value "N" or not and for the products without variants there is only one line for each order, however for products with variants there are several...
So how could I create the right table with a VBA macro in MS Excel or maybe there is a trick in MS Access to do it directly or with an SQL query?
