Matching multiple rows and if no match insert missing data

Hello,
I am having trouble with an excel file which is quite large and I would need to rearrange all data so it can be used as database for a certain statistical analysis software. It would take me way too much time to arrange it manually and I can’t figure out how to do it automatically.

I try to explain the situation I’m dealing with below:

I have three columns A, B and C filled with data.The rows contain different characteristics for a number of products but not all products have all features. The products are not listed in a separate column, but they are put also in characteristics column in different rows. It would be somehow easier to match data if each product would have the same number of characteristics, with adjacent value 0, but they are missing and, again, it would take me too long to manually insert them.
I have 7876 rows, 69 products, 9 characteristics groups.

WHAT I HAVE
ID Characteristics Values
Product 1
Characteristic 1
1301 Sub- characteristic 1.1 Value 1
1302 Sub- characteristic 1.2 Value 2
1304 Sub- characteristic 1.4 Value 3
1306 Sub- characteristic 1.6 Value 4
Characteristic 2
1403 Sub-characteristic 2.3 Value 5
1404 Sub- characteristic 2.4 Value 6
1417 Sub- characteristic 2.17 Value 7
Characteristic 3
…..
Product 2
1301 Sub- characteristic 1.1 Value 28
1302 Sub- characteristic 1.2 Value 29
1303 Sub- characteristic 1.3 Value 30
1307 Sub- characteristic 1.7 Value 31
Characteristic 2
1401 Sub-characteristic 2.1 Value 5
1402 Sub- characteristic 2.2 Value 6
1417 Sub- characteristic 2.17 Value 7
Characteristic 4
….



WHAT I NEED TO GET
ID Characteristics Values
Product 1
Characteristic 1
1301 Sub- characteristic 1.1 Value 1
1302 Sub- characteristic 1.2 Value 2
1303 Sub- characteristic 1.3 0
1304 Sub- characteristic 1.4 Value 3
1306 Sub- characteristic 1.6 Value 4
1307 Sub- characteristic 1.7 0
Characteristic 2
1401 Sub-characteristic 2.1 0
1402 Sub- characteristic 2.2 0
1403 Sub-characteristic 2.3 Value 5
1404 Sub- characteristic 2.4 Value 6
1417 Sub- characteristic 2.17 Value 7
Characteristic 3
….. Value 8
Product 2
1302 Sub- characteristic 1.1 Value 28
1303 Sub- characteristic 1.2 Value 29
1303 Sub- characteristic 1.3 Value 30
1304 Sub- characteristic 1.4 0
1306 Sub- characteristic 1.6 0
1307 Sub- characteristic 1.7 Value 31
Characteristic 2
1401 Sub-characteristic 2.1 Value 5
1402 Sub- characteristic 2.2 Value 6
1403 Sub-characteristic 2.3 0
1404 Sub- characteristic 2.4 0
1417 Sub- characteristic 2.17 Value 7
Characteristic 3
….. 0
…. 0
Characteristic 4
….


This way I could transpose my data in a format that could be used as database in a statistical analysis software.
A database such as
Sub characteristic 1 Sub characteristic 2 Sub characteristic 3 Sub characteristic 4 Sub characteristic 5 ….. Sub characteristic n
Product 1 Value1 Value 2 …..
Product 2
….
Product n …..


Practically what I need is for all products to have all characteristics and sub-characteristics, and where no value is available to have 0 in the value column. I gather that I could use as reference column A only since the codes are unique. But I still don't now ho to search through all rows and have the data displayed either the same but with new rows so that the number of codes matches the number of products or to have it organized in multiple set of 3 columns, one set for each product.

Thank you so much in advance for any help you could provide

I was looking to see if I received any suggestions to me issue and I realized that I could explain it better:



Column A = ID (1 ... n)
Colum B = characteristics (description of ID in words )
Column C = value


For fewer lines the situation will look as follows:

ID Characteristics Value
empty cell Product X empty cell
1 feature 1 value for feature 1 for Product X
2 feature 2 value for feature 2 for Product X
3 feature 3 value for feature 3 for product X
empty cell Product Y empty cell
1 feature 1 value for feature 1 for Product Y
(the same feature (different value then the value of same feature but for Product X)
as above)
3 feature 3 value for feature 3 for Product Y

Example.jpg

Manual Solution:
above the red row I would insert a new row containg the data below
2 feature 2 0


In the above example, in order to display the same number of features for all products, I would first look to see if the order of features displayed for both Product X and Y are the same and I would notice that below Product Y feature 2 is missing. Therefore I would manually insert a new row above the row containing feature 3 (the red one) and I would write in this row 2 in the cell corresponding to column A (standing for the ID of the missing item), feature 2 in the cell corresponding to column B (standing for the description of the feature missing), and value 0 in cell corresponding to Column C, because I don't have a measurement of this feature for Product Y. In the above example the green line is obtained after going trough the process just described. But I am looking for a way to do this automatically (maybe using a script or a set of formulas), because it would take me way to much time to insert rows manually considering that i have over 90 products and approx 120 features.

Could anybody help me with this?

I am in desperate need for a solution and I would appreciate any suggestions tremendously!