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

darrylbster · Aug 8, 2016

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>

darrylbster · Aug 15, 2016

oldbrewer said:
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......

We have no control over the architectural drawings, either at the beginning or end of any project, and glass suppliers do not and will not read drawings.

oldbrewer · Aug 15, 2016

def last post rofl

once you get the architects drawings code each piece of glass as I said. Then place your order with the glass suppliers 25 of code1, 75 of code 2 etc..

surely the glass suppliers will identify each item with YOUR SPECIFIED TAG

if they don't / won't HOW DO YOU IDENTIFY WHAT THEY SEND TO YOU

darrylbster · Aug 15, 2016

oldbrewer said:
def last post rofl

once you get the architects drawings code each piece of glass as I said. Then place your order with the glass suppliers 25 of code1, 75 of code 2 etc..

surely the glass suppliers will identify each item with YOUR SPECIFIED TAG

if they don't / won't HOW DO YOU IDENTIFY WHAT THEY SEND TO YOU

Thats exactly what we're doing, providing a code for each peice of unique glass to provide to the manufacturer. Sheetspreads solution does a great job of providing the unique codes, I just have to find a solutions for corrections. I think I'm going to explore VBA for thatg portion.

sheetspread · Aug 15, 2016

darrylbster said:
Thats exactly what we're doing, providing a code for each peice of unique glass to provide to the manufacturer. Sheetspreads solution does a great job of providing the unique codes, I just have to find a solutions for corrections. I think I'm going to explore VBA for thatg portion.

Do you know how the corrections would work and it's just a matter of teaching the method to Excel? Or are you still deciding? Maybe you need VBA (I can help with that also), but a formula solution may still be possible.

darrylbster · Aug 15, 2016

darrylbster said:
Actually I found one issue. I was energetically entering data into my now completed workbook when I discovered an error on my part. Two parts with the same stats were entered several rows apart, these parts were tagges the same, just as intended. After filling out a few more rows I realized that those parts were NOT the same, I had made an error. When I corrected the issue, it re-tags the once identical part as a new part, and subsequently re-tags all parts below. This is an issue because we make records of all part tags on our drawings, so if we were far into the job we would have to go back through the whole job and track the changes from excel and update the drawings... at first I was thinking that the issue would be solved if the formula checked above AND below for the next tag, thus giving a modified part in the middle of the list a new tag... but what if the part was previously unique and now common..

for an example, in your list above, if A-B had been a common part (occuring several times through the list) and a sinlge instance was CORRECTED, the ideal would be for the new part to scan above and below and see that the next available value was A-F so as not to rename everything.

It gets even more complicated when the part changing is not becomming unique to the list, but JOINING the ranks with a common part. For example if A-B had been completely unique (1 count) and was CORRECTED to become an A-C.. what would happen is A-C would A-B, A-D would become A-C, and A-E would Become A-D. Ideally A-B would simply get reused by the next part with an 'A' headding without renaming the other parts...

Do you think there is a way to prevent that? Even if the last unique item on the list took the old spot, like A-E renames itself to become A-B and colours itself red or something..

Ok, so follow-up time!

The formula above provided by sheetspread above worked EXACTLY as intended; however, as noted above there were issues when it came to correcting errors after a second party review. I was able to solve all renumbering issued with a little VBA work. A shout out to Rick Rothstein for helping me figure out how to use the .Hidden command.http://www.mrexcel.com/forum/members/rick-rothstein.html

You can see the solutions in this link. http://www.mrexcel.com/forum/excel-questions/958970-commandbutton-macro-hide-row.html#post4606212

Now, Sheetspreads formula will automatically and dynamically name all parts. Initially if the originator made an error that was caught by the checker, the revision of said error would, or COULD result in the auto update of all part tags below. Using the VBA in the link above (attached to a commandbutton) the originator can select the faulty cell, click the buttonm, and the eroneous row will be copied and hidden (thus removing it from the list WITHOUT changing its original tag) and paste it in the first available blank row below. The originator can now determine the source if the error and correct it, the part will be assigned a new tag without affecting the existing list.

Thanks so much Sheetspread.

sheetspread · Aug 15, 2016

darrylbster · Sep 9, 2016

Hey Sheetspread. So it's been a little while, and I'm not looking over the formula to see if I can understand how and why it works and repurpose it for another sheet....I hit my first stumbling block right away.

Your first rendition created an alpha-numeric tag by using the follwoing.
{=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)))} 

Your next rendition revised the tags to an alpha-alpha tag using the following.
{=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)))}

The difference between the two is clear, you've defined the unique alpha tag witht he CHAR variable. It gets muddy in the third rendition..

You revised the previous to create tags based on the Tag history, ensureing only glass with unique dimensions and composition are given a new tag number, reusing the old tags as required.{=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,A2&"-"SUBSTITUTE(ADDRESS(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)),4),1,""),INDEX($D$1:$D1,MATCH(A2&B2&C2,$A$1:$A2&$B$1:$B2&$C$1:$C2,0)))}

This is where I get lost....er. The third and final rendition continues to produce an alpha-alpha tag, but why? Without the CHAR variable what defines the unique tag as an alpha? I get the feeling that its associated with the SUBSTITUTE variable?

sheetspread · Sep 9, 2016

I replaced the CHAR with ADDRESS so you could just keep going after Z to AA, AB..........all the way to XFD (Excel's limit, column 16,384, hopefully you don't need more!). The substitute just gets rid of the row number 1 since you can't leave it out.

darrylbster · Sep 9, 2016

Oh awesome, so in order to generate an alpha tag you're literally utilizing the column address (A,B,C,etc.)? And when you say substitute gets rid of row 1, you're making reference to the first requirement of the address function?

sheetspread · Sep 9, 2016

Yes, because the address function cannot return just a column letter (I think)

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

darrylbster

New Member

darrylbster

New Member

Excel Facts

oldbrewer

Well-known Member

darrylbster

New Member

sheetspread

Well-known Member

darrylbster

New Member

sheetspread

Well-known Member

darrylbster

New Member

sheetspread

Well-known Member

darrylbster

New Member

sheetspread

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock