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]

Multiple suppliers, and when we order it we provide them with all sizes and glass tags associates so they can be appropriately groped prior to site delivery. The site crew is very expensive in comparrison to office staff, so we make it as simple as possible to prevent confision and avoidable expenses.

 

[oldbrewer]

I think I see the picture now. your company gets a contract and to fulfill it you order in glass. So say this is the 35th commission this year, ask your suppliers to tag the glass 35- followed by some sort of logical code. (maybe the thickness of the glass)

Do you ever have glass left over from a commission. Do you hold it for re-use ?

 

[darrylbster]

I think I see the picture now. your company gets a contract and to fulfill it you order in glass. So say this is the 35th commission this year, ask your suppliers to tag the glass 35- followed by some sort of logical code. (maybe the thickness of the glass)

Do you ever have glass left over from a commission. Do you hold it for re-use ?

We build aluminum framing for stip malls, high rise, condo's, hospitals, hotels, etc. Part of our contact is to complete these facade systems with the glass specified in architectural specifications. Typically the atchitectural drawings or specifications will provide a glass tag to each different type of glass they desire. We use this architecturally assigned glass tag and apply a unique value to it in order to map glass sizes per glass type across a job.

A prime example woule be a 1" sealed unit with grey tint on the outboard lite, the architect tags it GL1 and shows that tag across the building. We can't simply put GL1 because our installers would have to measure every piece before installing it, this would take way too much time and cost a fortune, so we manyally go through a whole set of drawings and identify each different size for each different tag and apply the variable. GL1 becomes GL1-A, GL1-B and on and on, thus creating an installation map for site while creating a corresponding order form to submit to the glass company.

The initial solution was beautiful, it does exactly what we were hoping it would do, the only draw back is the renumbering. Mistakes will certainlly be made during the data entry, which is why the workbook I created was built as a two part check system. I suppose if there is no work around for the problem, it may better train those performing the glass take-off lol.

 

[sheetspread]

Would this work?

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		0	A-A
3	A	X	Z	2	A-B		0	A-B
4	A	X	Y	3	A-C	x	1	A-ADJ1
5	B	Z	X	1	B-A		0	B-A
6	B	Z	Y	2	B-B		0	B-B
7	A	X	Z	2	A-B	x	2	A-ADJ2
8	C	X	Y	1	C-A		0	C-A
9	B	Z	X	1	B-A		0	B-A
10	A	X	Z	2	A-B		2	A-B
11	A	X	L	4	A-D		2	A-D
12	C	X	Y	1	C-A		0	C-A
13	A	Z	J	5	A-E		2	A-E
14	A	X	Z	2	A-B		2	A-B
15	B	R	L	3	B-C		0	B-C
16	D	G	K	1	D-A		0	D-A
17	B	Q	W	4	B-D		0	B-D
18	A	B	C	6	A-F		2	A-F
19	B	T	Q	5	B-E		0	B-E
20	E	N	Q	1	E-A		0	E-A
21	D	N	N	2	D-B		0	D-B
22	D	G	K	1	D-A		0	D-A
Sheet13 (10)

Cell Formulas
Range	Formula
E2	=A2&"-"&SUBSTITUTE(ADDRESS(1,D2,4),1,"")
G2	=COUNTIFS($A$2:A2,A2,$F$2:F2,"x")
H2	=IF(F2="",E2,A2&"-"&"ADJ"&G2)
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.

You just put an x at each corrected part and you get a new label which can be matched to the old one without retro adjustments

 

[oldbrewer]

get fred to do variable application and bill to check it afterwards - next week bill does it and fred checks it

all the GL1's are different sizes I see, so get the supplier of the glass to tag it GL1-A etc for different sizes and check it at goods inward....

 

[darrylbster]

get fred to do variable application and bill to check it afterwards - next week bill does it and fred checks it

all the GL1's are different sizes I see, so get the supplier of the glass to tag it GL1-A etc for different sizes and check it at goods inward....

Thats a hands off approach and no good in a production situation. To maintain production speed you want to create the variable to submit to a vendor not the other way around. That would require the time and expense of an employee reading every single glass tag that comes in, reverse it to get the DLO, search the drawings for a DLO that matches that value and tag it. Thats a day's worth of work for a small job, thats 1 days delay and 1 days labour that can be saves with a little extra time on the front end.

 

[darrylbster]

Would this work?

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		0	A-A
3	A	X	Z	2	A-B		0	A-B
4	A	X	Y	3	A-C	x	1	A-ADJ1
5	B	Z	X	1	B-A		0	B-A
6	B	Z	Y	2	B-B		0	B-B
7	A	X	Z	2	A-B	x	2	A-ADJ2
8	C	X	Y	1	C-A		0	C-A
9	B	Z	X	1	B-A		0	B-A
10	A	X	Z	2	A-B		2	A-B
11	A	X	L	4	A-D		2	A-D
12	C	X	Y	1	C-A		0	C-A
13	A	Z	J	5	A-E		2	A-E
14	A	X	Z	2	A-B		2	A-B
15	B	R	L	3	B-C		0	B-C
16	D	G	K	1	D-A		0	D-A
17	B	Q	W	4	B-D		0	B-D
18	A	B	C	6	A-F		2	A-F
19	B	T	Q	5	B-E		0	B-E
20	E	N	Q	1	E-A		0	E-A
21	D	N	N	2	D-B		0	D-B
22	D	G	K	1	D-A		0	D-A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13 (10)​

Worksheet Formulas Cell Formula E2 =A2&"-"&SUBSTITUTE(ADDRESS(1,D2,4),1,"") G2 =COUNTIFS($A$2:A2,A2,$F$2:F2,"x") H2 =IF(F2="",E2,A2&"-"&"ADJ"&G2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You just put an x at each corrected part and you get a new label which can be matched to the old one without retro adjustments

Unfortunately I would still have to change the dimensions associated with ADJ-1, and as soon as X-Y becomes Z-Y the tag may change to ADJ-1 but A-C still becomes A-C.

This is kind of making me think though. One possibility to solve this issue is to black out the entire affected row from Column A-H when X is entered into 'F', better yet entering X copies the affected row from A-H and inserts at after the last entry, then blacks out the incorrect row. The originator can then go in and correct the new bottom entry and the glass tag will auto update and the existing list is unharmed. This methos may completey eliminate a tag, Like A-F may never appear in a job if it gets blacked out and becomes an A-A but that would do no harm to the final output.

 

[sheetspread]

So why not have the part type include a prefix like ADJ or something that way the others don't get changed and you can still find where it began?

 

[darrylbster]

So why not have the part type include a prefix like ADJ or something that way the others don't get changed and you can still find where it began?

Basically it would interfere with the two stage check. If both the calculations from the originator mismatch in nay way the calculations of the checker, the parts order list evaluates an error. Though indicating an adjustment will apply a new tag to the dimensional unit, it will not change the sizes displayed and the check process breaks. This is because the tag is never wrong, it's now auto generated, the incorrect information will alway be in the dimensional calculations completed by either the originator or the checker. Once the error is corrected, the tags below renumber regardless if you've changed the tag using a adjustment cell.

Maybe a VBA function... I'll have to do some digging.

 

[oldbrewer]

this will be my final post on this topic...

say the set of architects drawings has 137 different types of glass (size, thickness, glass specification). Have the architects code these on the drawings from 1 to 137. And say the project is your 27th project this year, the code becomes for one type of glass say 27-118. Now send a copy of the drawings to your glass suppliers obviously telling them how many of each of the 137 types are required, and putting in the contractual terms that EVERY piece of glass supplied must be tagged with the project number and the glass type from the drawings. Thus the glass will be delivered to, say, the shopping mall project, and the highly skilled highly paid construction crew will know which glass to fit where.......good bye and good luck......