Count if unique and tag, else create new tag based on criteria

[darrylbster]

Good afternoon Guru's, I've a question for you all.

First, some history. I was here years ago to get some assistance with an offset code that had me spun, it took someone minutes to figure it out and it began journey down excel lane. Years later I am still not strong with complex formula string, but I have been able to come up with soe very useful itilities for the workplace. Until this one.

Currently I am trying to write a formula that will generate tags for a list of parts, the tags must be associated to the part size, parts with the same size must be assigned the same tag.
Try and try as I might, I can't get this to work, I simply don't have the experience or knowlege base, but this one final formula is the verry on top of an excelent tool for my company.

Lets paint a picture.

<tbody>

The column I need to solve for is 'Tag'. As is stands I have a complex spreadsheet to be used as both order assembly and order cross check, but if the originator of the workbook is not diligent with his tagging, we can end up with the same part being assigned different tags, easily done over the course of hundreds of parts. What I need is a formula that will evaluate the data in width and height, check it against part, then check all parts above for the unique combination of Width and height. If the comnination is found, it assigns the take associated with that combination, if it's unique it has to ecaluate the highest level of tagging for that part and add 1. I've had elements of the evaluation working, but never together, and often with errors. This solution is beyond me. Thanks.

<tbody>

Part Width Height Tag A x y A-1 A y z A-2 B z y B-1 B x y B-2 A y z A-2 C z y C-1 C z x C-2 B z y B-1 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

</tbody>

</tbody>

 

[sheetspread]

Something like this?

Excel 2010
	A	B
1	Part	Number
2	QQ	1
3	QQ	1
4	QQ	1
5	WW	2
6	EE	3
7	EE	3
8	EE	3
9	EE	3
10	EE	3
11	EE	3
12	RR	4
13	RR	4
14	RR	4
15	RR	4
16	RR	4
17	TT	5
18	YY	6
19	UU	7
20	UU	7
21	UU	7
Sheet12

Cell Formulas
Range	Formula
B2	=IF(A2=A1,B1,IF(ISNUMBER(B1),B1,0)+1)

 

[darrylbster]

It would seem that about half of my post was eliminated somehow...

I'm trying to create a tagging convention but I can't get any of the functions to work properly, or together.

Part Type	Width	Height	TAG
A	Z	Y	A-1
A	X	Z	A-2
A	X	Y	A-3
B	Z	X	B-1
B	Z	Y	B-2
A	X	Z	A-2
C	X	Y	C-1
B	Z	X	B-1

<tbody>
</tbody>

What I'm trying to do is create a formula that will check the combination of Width and Height against part type associated. If at any point that unique combination of width and height is associated witht he same part type, it will return the assocaited tag, if the unique combination does not exist it must check for the maximum value of TAG and add 1. The kicker is that this can't be done with a macro because the evaluation must be live, we take this TAG and apply it to our drawings as we are creating. Also note that the TAG is derived from the part type.

There was a time when I thought I'd figured this one out, but I cound never get it to evaluate more than one combination.

Thanks everyone, this is probably like a childs question to some of you.

 

[oldbrewer]

			blue	red	green
			size1	size2	size3
		part1	blue1	red1	green1
		part2		red2	green2
		part3	blue2
		part4		red3
		part5			green3
			is this what you mean ?
			note part2 is not manufactured in size1

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[darrylbster]

			blue	red	green
			size1	size2	size3
		part1	blue1	red1	green1
		part2		red2	green2
		part3	blue2
		part4		red3
		part5			green3
			is this what you mean ?
			note part2 is not manufactured in size1

<tbody>
</tbody>

. My first post was cut off and it makes my question hard to read. I've contacted the admin to correct my first post, but in the mean see post 3.
Not quite

 

[sheetspread]

There's better but this worked on an extended sample, test it some more and tell me if there are any breaks:

Excel 2010
	A	B	C	D	E
1	Part Type	Width	Height	TAG
2	A	Z	Y	A-1	1
3	A	X	Z	A-2	2
4	A	X	Y	A-3	3
5	B	Z	X	B-1	1
6	B	Z	Y	B-2	2
7	A	X	Z	A-2	2
8	C	X	Y	C-1	1
9	B	Z	X	B-1	1
10	A	X	Z	A-2	2
11	A	X	L	A-4	4
12	C	X	Y	C-1	1
13	A	Z	J	A-5	5
14	A	X	Z	A-2	2
15	B	R	L	B-3	3
16	D	G	K	D-1	1
17	B	Q	W	B-4	4
Sheet13 (7)

Cell Formulas
Range	Formula
D2	=A2&"-"&E2
E2	{=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,SUMPRODUCT(1/COUNTIFS($A$2:$A2,$A$2:$A2,$B$2:$B2,$B$2:$B2,$C$2:$C2,$C$2:$C2)*($A$2:$A2=A2)),INDEX($E$1:$E1,MATCH(A2&B2&C2,$A$1:$A2&$B$1:$B2&$C$1:$C2,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[darrylbster]

Well I can say two things about this. A) I never thought of splitting the formua like that, it works great. B) I wasn't even close.

I think for the most part I'm able to get certain things done, but any time one of you guys put it together it's far more condensed and refined.

Is there any way to get column E to display letters?

 

[sheetspread]

This?

Excel 2010
	A	B	C	D
1	Part Type	Width	Height	TAG
2	A	Z	Y	A-1
3	A	X	Z	A-2
4	A	X	Y	A-3
5	B	Z	X	B-1
6	B	Z	Y	B-2
7	A	X	Z	A-2
8	C	X	Y	C-1
9	B	Z	X	B-1
10	A	X	Z	A-2
11	A	X	L	A-4
12	C	X	Y	C-1
13	A	Z	J	A-5
14	A	X	Z	A-2
15	B	R	L	B-3
16	D	G	K	D-1
17	B	Q	W	B-4
18	A	B	C	A-6
Sheet13 (8)

Cell Formulas
Range	Formula
D2	{=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,A2&"-"&SUMPRODUCT(1/COUNTIFS($A$2:$A2,$A$2:$A2,$B$2:$B2,$B$2:$B2,$C$2:$C2,$C$2:$C2)*($A$2:$A2=A2)),INDEX($E$1:$E1,MATCH(A2&B2&C2,$A$1:$A2&$B$1:$B2&$C$1:$C2,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[darrylbster]

Sorry, I think I've crossed some lines here. The first solution worked great, no issues with function at all, the second is an improvement on that for sure. What I was asking was weather or not column e could generate sequencual letters as opposed to numbers, creating an A-A tag rather than an A-1 tag.

Thanks so much fo the help, I was so off based with my attempts.

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D
1	Part Type	Width	Height	TAG
2	A	Z	Y	A-A
3	A	X	Z	A-B
4	A	X	Y	A-C
5	B	Z	X	B-A
6	B	Z	Y	B-B
7	A	X	Z	A-B
8	C	X	Y	C-A
9	B	Z	X	B-A
10	A	X	Z	A-B
11	A	X	L	A-D
12	C	X	Y	C-A
13	A	Z	J	A-E
14	A	X	Z	A-B
15	B	R	L	B-C
16	D	G	K	D-A
17	B	Q	W	B-D
18	A	B	C	A-F
19	B	T	Q	B-E
20	E	N	Q	E-A
21	D	N	N	D-B
22	D	G	K	D-A
Sheet13 (8)

Cell Formulas
Range	Formula
D2	{=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,A2&"-"&CHAR(64+SUMPRODUCT(1/COUNTIFS($A$2:$A2,$A$2:$A2,$B$2:$B2,$B$2:$B2,$C$2:$C2,$C$2:$C2)*($A$2:$A2=A2))),INDEX($D$1:$D1,MATCH(A2&B2&C2,$A$1:$A2&$B$1:$B2&$C$1:$C2,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.