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>

 

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