determining how many unique items across two or more non-sequential rows

[BaronVonTeabag]

Hey all!

sheet.png

in the file above I need to find a way to tally unique items based on the number in columns AB and AC
each column represents a sheet on the printing press and the "4"s in the column represent how many of each image will appear on the sheet

Columns G thru V list the colours for each image.

so for instance...row 8 and row 17 will be on one print sheet.
is there a formula that can look at the colours on each row and determine how many unique colours there are?

Looking for several tidbits from this...

1) need to get the # of unique items
2) from the count of unique items I'll be able to setup a conditional format where it flags if the total number of unique colours exceeds 8 (max colours on press)

3) this is the hard part in my opinion...I need to compare the unique colours represented in column AB with the # of unique colours represented in column AC and determine how many colour changes there are between them

the answer based on this model will be two colour changes between the runs but how can I create a formula that will tell me that?

If you need any additional details let me know.

Thanks in advance for your contributions!

Scott

[mehmetcik]

Formula for AA8 =IF(TEXTJOIN(", ",TRUE,G8:Y8)<>"",(LEN(UNIQUE(TEXTJOIN(", ",TRUE,G8:Y8)))-LEN(SUBSTITUTE(UNIQUE(TEXTJOIN(", ",TRUE,G8:Y8)),", ","")))/2+1,0)

[BaronVonTeabag]

AA8 already counts the number of colours from G8 to Y8 with a simple COUNTA formula. Any one colour will never appear more than once in any given row (ie: you will never see "SPEC3" appear twice in the same row.
What I need is a formula that can use the "4"s in AB8 and AB17 to trigger a comparison of the unique colours between those two rows. The unique colours being C, M, Y, K, SPEC1, SPEC2, SPEC3 and SPEC12.
then it needs to do the same thing for column AC. The formula needs to determine that the difference in colours between column AB reference (SPEC3 & SPEC12) are different from AC reference (SPEC5 & SPEC6) and repost the resultant "2" in cell AC32.

That's just one of my issues (#3) as listed above.

item 1 & 2 are parts of the same issue I guess. it needs to tally the unique items based on where the "4"s are in column AB and return the total of unique items cell AB30...same for the other columns

Sorry if my wording was confusing.

[JeteMc]

If I understand correctly you may be able to use the following:
For AB30 and across:

Please Login or Register  to view this content.

For AC32 and across:

Please Login or Register  to view this content.

Let us know if you have any questions.

[BaronVonTeabag]

Unfortunately that does not work either.

Let me see if I can explain this differently....

each item C M Y K SPEC1 - represents a colour or 1 printing plate for a run (column AB, AC, etc...)

C= cyan, M = magenta, Y = yellow, K = Black, SPEC1 = special colour #1, etc...

in column AB (run #1) the 4 represents the number up or impressions being printed on each sheet
so lets say the first "4" in that column represents strawberry pop tarts and the second one represents chocolate pop tarts.
that means there are two different print impression on the sheet... four are chocolate and 4 are strawberry.
between those two print impressions we see several common colours (C M Y K SPEC1 SPEC2)
so that means there are 6 plates (colours) that are common and will appear on the same plates
the "unique" colours ...based on where the 4's appear are SPEC3 and SPEC12
because these are different colours they will be on different plates.
based on that we have 6 common colours (plates) and 2 unique colours (plates for a total of 8 plates required to complete RUN #1

the first thing I need to do is in cell AB30, 31, etc....is determine the total number of colours or plates required (in this case the answer needs to be 8)
I need a formula that will look at the colours in row 8 and row 17 and determine that there are 8 different colours (C M Y K SPEC1 SPEC2 SPEC3 SPEC12)

secondly, I need to be able to now compare the "unique" colours from the result in column AB and compare them to the "unique" colours from column AC and determine how many colour changes there are between runs.
since the C M Y K SPEC1 & SPEC2 are "common" between all runs these are never going to need to be changed out on press. the wells will be filled with those colours and they will stay from start to finish. The "unique" colours on the other hand will be changed as soon as the requisite number of sheets have been run. Therefore I need a formula that will recognize that the SPEC3 & SPEC12 from RUN #1 (col AB) are different from the SPEC5 & SPEC6 in RUN #2 (col AC)
the resultant qty of 2 colour changes needs to be placed in cell AC32

example...
RUN #1 -- C M Y K SPEC1 SPEC2 SPEC3 SPEC12
RUN #2 -- C M Y K SPEC1 SPEC2 SPEC5 SPEC6

I know that's a lot of information but hopefully that makes my requirements clearer?

[JeteMc]

I felt as if I had taken that into account, however I did omit saying that I had copied the formula in cell AV8 across and then down so as to correct the values in columns BP:CH
I have now modified the formula in row 30 to read: =IF(SUM(AB8:AB27)=0,"",SUM(6,SUMPRODUCT((AB7:AB28<>"")*($M7:$Y28<>""))))
Please tell us if/where the formulas are yielding incorrect values.
Let us know if you have any questions.

[BaronVonTeabag]

You are on the right path I think but the 6 in your formula won't always be a fixed number like that
the number of common colours could be different based on the number of colours in the rows where the number of impressions is listed (col AB & AC)
common colours could be anywhere from 2 to 8

[JeteMc]

Perhaps someone can come up with an eloquent formula/solution, however I am now going with a brute force method.
For row 30:

Please Login or Register  to view this content.

For row 32:

Please Login or Register  to view this content.

Let us know if you have any questions.

[hrlngrv]

Sorry, I screwed up. Correction.

When would TEXTJOIN(", ",TRUE,G8:Y8)<>"" produce a different result than COLUMNS(G8:Y8)>COUNTBLANK(G8:Y8) ?

Since TEXTJOIN always returns a single result and never an array, when would UNIQUE(TEXTJOIN(...)) return a different result than TEXTJOIN(...) alone? Perhaps COUNTA(UNIQUE(TRANSPOSE(G8:Y8)))-SIGN(COUNTBLANK(G8:Y8)) .

Note: UNIQUE requires vertical ranges. Enter {"n","p","p","n","p","n","p","n","n","n"} in A3:J3, and =UNIQUE(A3:J3) returns that 1-row by 10-column array. OTOH, =UNIQUE(TRANSPOSE(A3:J3)) returns {"n";"p"}. Example.

[BaronVonTeabag]

Well good sir...brute force appears to work just fine after a few different tests.
The only issue I see is if my last item or run has only one impression on a sheet instead of 2 then it thinks all the colours need to change but if thats all I have to watch out for then I'm good with that.
Thanks very much for you help and patience with this.