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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Product A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=1ProductA[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=2ProductA[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=3ProductA[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Product B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=1ProductB[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=2ProductB[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=3ProductB[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

you can use concatenate or simply &

If I guess that C 1 is product A the 1 would be at 3 you could use in A3 = C3&$C$1 and drag down

 

[Bering]

Thanks Mole999, the problem is that by simply fixing $C$1 and dragging down the result would not be the expected one when the product changes to Product B, Product C and so on.
My real file contains many products and that's basically how we are currently dealing with the problem: by manually changing the "$C$1" reference every time. It is this kind of manual intervention that I would like to avoid.

 

[mole999]

The only other way I can see is to add an additional column that reflects the product I can't think of an intelligent way the formula would auto adjust

 

[brownbread]

With your sheet set up like this:

Enter this formula in cell A3, and drag it down:

=IF(ISNUMBER(C3),IF(ISNUMBER(C2),C3&SUBSTITUTE(MID(A2,LEN(C2)+1,LEN(A2)-LEN(C2))," ",""),C3&SUBSTITUTE(C1," ","")),"-")

Note that your data needs to have no blank rows.

 

[Bering]

Yep Mole999, that's why I said it was a tricky one

 

[Bering]

Wow, that's great!!! Thank you so much brownbread!!!

With your sheet set up like this:

Enter this formula in cell A3, and drag it down:

=IF(ISNUMBER(C3),IF(ISNUMBER(C2),C3&SUBSTITUTE(MID(A2,LEN(C2)+1,LEN(A2)-LEN(C2))," ",""),C3&SUBSTITUTE(C1," ","")),"-")

Note that your data needs to have no blank rows.

 

[Bering]

Hi again bownbread, I find your solutions fascinating.

I was wondering how your formula could be changed to work in a situation where there is a numeric code instead of Product A, Product B etc, and the types are text rather than numbers (just the opposite of what we had in my previous example).


Thanks!

With your sheet set up like this:

Enter this formula in cell A3, and drag it down:

=IF(ISNUMBER(C3),IF(ISNUMBER(C2),C3&SUBSTITUTE(MID(A2,LEN(C2)+1,LEN(A2)-LEN(C2))," ",""),C3&SUBSTITUTE(C1," ","")),"-")

Note that your data needs to have no blank rows.

 

[Bo_Ry]

Please try at A3
=IF(OR(C3="type",C4="type",C3=""),"",C3&" "&LOOKUP(2,1/(C$2:C3="type"),C$1:C2))

 

[Bo_Ry]

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))