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>

 

[darrylbster]

Whiting out the cell wouldn't work, I was thinking backwards... Maybe this?

	Part Type	Width	Height	TAG
2	A	Z	Y	A-A
3	A	X	Z	A-B This becomes ZZ
4	A	X	Y	A-C We don't want this to renumber
5	B	Z	X	B-A
6	B	Z	Y	B-B
7	A	X	Z	A-B We don't want this to renumber

<tbody>
</tbody>

	Part Type	Width	Height	TAG	Adjustment
	A	Z	Y	A-A
3	A	X	Z	A-B	A-D - based on next in sequence, which would be displayed when I try to make the initial adjustment.
4	A	X	Y	A-C
5	B	Z	X	B-A
6	B	Z	Y	B-B
7	A	X	Z	A-B

<tbody>
</tbody>

<tbody>
</tbody>

	Part Type	Width	Height	TAG	Adjustment
2	A	Z	Y	A-A
3	A	X	Z	A-D	A-B
4	A	X	Y	A-C	- Does not renumber because A-B is registered to Adjustments
5	B	Z	X	B-A
6	B	Z	Y	B-B
7	A	X	Z	A-B	- I could see this trying to renumber itself though..

<tbody>
</tbody>

	Part Type	Width	Height	TAG	Adjustment
2	A	Z	Y	A-A
3	A	X	Z	A-D	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	A	X	L	A-E	- Numbering Resumes?

<tbody>
</tbody>

Not sure if this would work, I think the second instance of A-B would renumber regardless because it would become unique..

 

[oldbrewer]

I am working with 26 widths and 26 heights - if either is more than 26 you need to rethink this imho.....

 

[sheetspread]

Can you have two new columns, one for the adjustment, and another =if(no adjustment, same as before, otherwise zz etc)?

 

[oldbrewer]

let us say part A is a cylinder width 0.567 and height 7.35

surely that is end of story - if drawing calls for a part A then fetch it from the stores from the box labelled part A

if a new drawing calls for a cylinder width 0.567 and height 7.35 then the draughtsman needs to check the Company parts list to see if there is an EXISTING part, and if there is he amends the drawing to say, part J

a prefix code could be A=solid cylinder, B=hollow cylinder, C=rectangle, D= sheet etc etc

 

[darrylbster]

That would probably work, sounds to me like the initial tagging column would become hidden in lieu of the if statement?

 

[darrylbster]

Oldbrewer:

It's actually for architectural glass, so there is absolutely no standard job to job, no company master list could ever be created.

 

[oldbrewer]

but you don't keep every possible size of glass sheet in the stores - but does glass come in say 1 mm thickness increments ? and you cut one of those to size ?

 

[sheetspread]

That would probably work, sounds to me like the initial tagging column would become hidden in lieu of the if statement?

I think you need something like an if statement so it knows when to use the old tag vs the new one. So keeping the letters and numbers separate again:

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

Cell Formulas
Range	Formula
E2	=A2&"-"&SUBSTITUTE(ADDRESS(1,D2,4),1,"")
D2	{=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($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.

Enter the adjustment as an X, then have a max(if()) formula add one to ZZ or wherever you want to start. At least that's my first idea after looking at what you've posted above.

but you don't keep every possible size of glass sheet in the stores - but does glass come in say 1 mm thickness increments ? and you cut one of those to size ?

I was wondering that too

 

[darrylbster]

but you don't keep every possible size of glass sheet in the stores - but does glass come in say 1 mm thickness increments ? and you cut one of those to size ?

Architectural glass can be complex, one unit will have several factors (eg. outboard lite thickness, laminate interlayer, argon percentage, color, films, low e coating, spacer size, spacer material, inboard lite thickness, silicone color, tempering, heat soaking, annealing, heat strengthening) that make up the first half of its tag, and this will varry job by job which is why the first part is rather generic. The -# is the variant or size of the unit, which changes often but often repeates in a single job, but never works from job to job.

 

[oldbrewer]

so where do you get the glass from - or does your factory make glass ?