Tricky Unique ID

[Bering]

Hello,

hope you guys can hep me with this problem. Below is an example of what my file looks like.

I would like to create a unique ID in column A by concatenating the Types in column C and the Product ID also in column C above each table.

So for Product A, the expected result in column A should be: 1Product A, 2ProductA and so on (or Product A1, Product A2, whatever)

Of course I have no idea how this could be achieved.

Many thanks.

A	B	C
		Product A
		Type	Quantity	Price	Value
=1ProductA		1
=2ProductA		2
=3ProductA		3
		Product B
		Type	Quantity	Price	Value
=1ProductB		1
=2ProductB		2
=3ProductB		3

<tbody>
</tbody>

 

[Bering]

Really impressive Bo_Ry!!! I will try to understand how your formula works... Thank you so much ray:

Or remove &" " if no space need between type and product
=IF(OR(C3="type",C4="type",C3=""),"",C3&LOOKUP(2,1/(C$2:C3="type"),C$1:C2))