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?

yes, but i dont have direct access to tfe db, just the list
–
MartinDec 8 '12 at 8:28

1

ok. is it safe to say you can completely ignore any row with N for the value column?
–
joseph4twDec 8 '12 at 16:54

@joseph4tw: yes, that should be safe. In reality, there will be also many other attributes, which can be ignored, but I can filter them out before converting the table. Thanks a lot for your answer and your code - I'll try that and give you feedback
–
MartinDec 8 '12 at 23:20

you're very welcome! If you have any questions about adding conditions or how the code works please let me know.
–
joseph4twDec 9 '12 at 18:36