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

D

darrylbster

New Member
Joined
Aug 8, 2016
Messages
42
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
WidthHeightTag
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>
 
Sort by date Sort by votes

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
darrylbster said:
It goes a little crazy after 'Z' though.
Click to expand...

Try:


Excel 2010
ABCD
1Part TypeWidthHeightTAG
2AZYA-A
3AXZA-B
4AXYA-C
5BZXB-A
6BZYB-B
7AXZA-B
8CXYC-A
9BZXB-A
10AXZA-B
11AXLA-D
12CXYC-A
13AZJA-E
14AXZA-B
15BRLB-C
16DGKD-A
17BQWB-D
18ABCA-F
19BTQB-E
20ENQE-A
21DNND-B
22DGKD-A
Sheet13 (8)
Cell Formulas
RangeFormula
D2{=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)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
YES! That is perfect!

If I may, how is it the people here became so good with the formula? Was there schooling involved? Personal study? Trial and error? Or just natural inclination?

Verry impressive, thanks so much.
 
Upvote 0
darrylbster said:
how is it the people here became so good with the formula? Was there schooling involved? Personal study? Trial and error? Or just natural inclination?
Click to expand...

mrexcel.com and similar sites have some of the best teachers around. They learned from others of all different skill levels. I'd rank the remaining factors you listed in order of importance as:

1. Natural inclination
2. Trial and error (practice)
3. Personal study (theory)
 
Upvote 0
sheetspread said:
Try:

Excel 2010
ABCD
1Part TypeWidthHeightTAG
2AZYA-A
3AXZA-B
4AXYA-C
5BZXB-A
6BZYB-B
7AXZA-B
8CXYC-A
9BZXB-A
10AXZA-B
11AXLA-D
12CXYC-A
13AZJA-E
14AXZA-B
15BRLB-C
16DGKD-A
17BQWB-D
18ABCA-F
19BTQB-E
20ENQE-A
21DNND-B
22DGKD-A

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13 (8)​

Array Formulas
CellFormula
D2{=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)))}

<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>
Click to expand...

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..
 
Upvote 0
What if instead the tag was the part type, width, and height, with a sequence number (cumulative countifs) if you need one?
 
Upvote 0
unfortunately that wont work, the team of people who will be utilizing the tags make up the greatest expense in terms of labour at my company. In order to operate efficiently they need to be able to identify and collect parts quickly based on the tags provided. The longer the tag number the longer it takes to identify a match, and the greater the risk of error. The rest of the workbook is actually a two stage check process and automated project inventory requirement, it was developed to eliminate errors in the order process at the front end (by myself and others in my position) while simplyfying the back end to ensure quality and efficiency across the board.
 
Upvote 0
Part TypeWidthHeightTAGwidth
AZYAYZABCDEFGHIJKLMNOPQRSTUVWXYZ
AXZAZXAAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
AXYAYXBBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ
BZXBXZCCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZ
BZYBYZDDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZ
AXZAZXEEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZ
CXYCYXFFAFBFCFDFEFFFGFHFIFJFKFLFMFNFOFPFQFRFSFTFUFVFWFXFYFZ
BZXBXZGGAGBGCGDGEGFGGGHGIGJGKGLGMGNGOGPGQGRGSGTGUGVGWGXGYGZ
AXZAZXHHAHBHCHDHEHFHGHHHIHJHKHLHMHNHOHPHQHRHSHTHUHVHWHXHYHZ
AXLALXheightIIAIBICIDIEIFIGIHIIIJIKILIMINIOIPIQIRISITIUIVIWIXIYIZ
CXYCYXJJAJBJCJDJEJFJGJHJIJJJKJLJMJNJOJPJQJRJSJTJUJVJWJXJYJZ
AZJAJZKKAKBKCKDKEKFKGKHKIKJKKKLKMKNKOKPKQKRKSKTKUKVKWKXKYKZ
AXZAZXLLALBLCLDLELFLGLHLILJLKLLLMLNLOLPLQLRLSLTLULVLWLXLYLZ
BRLBLRMMAMBMCMDMEMFMGMHMIMJMKMLMMMNMOMPMQMRMSMTMUMVMWMXMYMZ
DGKDKGNNANBNCNDNENFNGNHNINJNKNLNMNNNONPNQNRNSNTNUNVNWNXNYNZ
BQWBWQOOAOBOCODOEOFOGOHOIOJOKOLOMONOOOPOQOROSOTOUOVOWOXOYOZ
ABCACBPPAPBPCPDPEPFPGPHPIPJPKPLPMPNPOPPPQPRPSPTPUPVPWPXPYPZ
BTQBQTQQAQBQCQDQEQFQGQHQIQJQKQLQMQNQOQPQQQRQSQTQUQVQWQXQYQZ
ENQEQNRRARBRCRDRERFRGRHRIRJRKRLRMRNRORPRQRRRSRTRURVRWRXRYRZ
DNNDNNSSASBSCSDSESFSGSHSISJSKSLSMSNSOSPSQSRSSSTSUSVSWSXSYSZ
DGKDKGTTATBTCTDTETFTGTHTITJTKTLTMTNTOTPTQTRTSTTTUTVTWTXTYTZ
UUAUBUCUDUEUFUGUHUIUJUKULUMUNUOUPUQURUSUTUUUVUWUXUYUZ
VVAVBVCVDVEVFVGVHVIVJVKVLVMVNVOVPVQVRVSVTVUVVVWVXVYVZ
WWAWBWCWDWEWFWGWHWIWJWKWLWMWNWOWPWQWRWSWTWUWVWWWXWYWZ
XXAXBXCXDXEXFXGXHXIXJXKXLXMXNXOXPXQXRXSXTXUXVXWXXXYXZ
YYAYBYCYDYEYFYGYHYIYJYKYLYMYNYOYPYQYRYSYTYUYVYWYXYYYZ
ZZAZBZCZDZEZFZGZHZIZJZKZLZMZNZOZPZQZRZSZTZUZVZWZXZYZZ
another way is to have 676 unique two digit codes joined to the part type
if a new size combo for say part E is defined, it will be pulled from the matrix

<colgroup><col span="6"><col span="26"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hmmmm, the formula can't check above and below. What if you dragged the formula down one extra row first, pasted everything above as values, manually corrected, then continued as normal? Or does oldbrewer's suggestion work for you?
 
Upvote 0
sheetspread said:
Hmmmm, the formula can't check above and below. What if you dragged the formula down one extra row first, pasted everything above as values, manually corrected, then continued as normal? Or does oldbrewer's suggestion work for you?
Click to expand...

I think the matrix idea would get out of hand quickly, I just cpmpleted a very small job and got up to tag AA. The problem is that there is no set width or height, one part might varry from the next by no more than 1/16". So lets say I make a correction and I see that it has resulted in renumbering of the parts... is there a way for me to incorporate some kind of "adjustment" column that the tag generator ignores, ot even if the "adjustment" column contains a value make the Cell of the adjacent tag white so the text is invisible?

For example...

Part TypeWidthHeightTAG
2
AZYA-A
3AXZA-B This becomes ZZ
4AXYA-C We don't want this to renumber
5BZXB-A
6BZYB-B
7AXZA-B We don't want this to renumber

<tbody>
</tbody>

So..

Part TypeWidthHeightTAG Adjustment
AZYA-A
3AXZA-B A-D - based on next in sequence, which would be displayed when I try to make the initial adjustment.
4
AXYA-C
5BZXB-A
6BZYB-B
7AXZA-B

<tbody>
</tbody>


Result

Part TypeWidthHeightTAG Adjustment
2AZYA-A
3AXZA-B A-D
4
AXYA-C
5BZXB-A
6
BZYB-B
7AX
ZA-B

<tbody>
</tbody>
Part TypeWidthHeightTAG Adjustment
2AZYA-A
3AXZA-B A-D
4AXYA-C
5BZXB-A
6BZYB-B
7AXZA-B
8A
XL
A-E - Numbering resumes?

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
179
zero269
Z
D
  • Solved
Find lowest value with conditions
Replies
5
Views
864
Fluff
Fluff
A
  • Question
Dynamic Controls and Objects at Runtime
Replies
1
Views
1K
AgentSmith2000
A
J
  • Question
VBA - Create Folder/Subfolder based on Today() and single cell values - Copy cells to new sheet
Replies
0
Views
211
JoAv
J
L
  • Question
Duplicate rows and fill in new columns based on master list
Replies
3
Views
814
lata_m
L

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top