I have a table containing data of sold products (shown in the
example on the left):
Number of the order
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
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
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?