Finding the mode (alpha numeric)

[Guest]

I find myself really struggling with this one and in the past you guys have
all ways come up with the goods, so don't let me down this time!

Let me explain my problem in simple form: -

1.) I start with an invoice
2.) One invoice holds many product lines
3.) An invoice fails, on this invoice there could be 1 line, 2 lines or 20
lines failing
4.) Within this invoice (failing product lines) alpha numeric codes are
provided to help us understand how to fix these individual problems

My end goal is to understand an average mode failure for each invoice. Let
me provide an example invoice: -

Invoice number Product Error
123456 Coke can AB123
123456 Lemonade can AC123
123456 Milk carton AC123
123456 Juice container AC123

Now from the above even though there are 4 lines this is actually one
invoice, when you uniquely identify the invoice numbers I'll get the top one
and when I look across I'll see error code AB123 which isn't a fair
reflection for this invoice.

Any help/advice would be warmly welcomed!

Thanks,
James.

 

[Guest]

one sort of brute force method to do it would be to sort first by invoice
(column a?)and secondly by error (column C?) and assuming your data starts in
row 2. add a helper column (column D?) and enter in D2
=if(and(A1=A2,C1=C2),D1+1,1)
and copy down to the bottom of the data
In E2 enter
=max(offset(d2,0,0,countif(A:A,A2))
in E3
=if(A2=A3,E2,max(offset(d3,0,0,countif(A:A,A3)))
and copy to the bottom of the data
In F2
=if(D2=E2,1,'')
and copy to the bottom of the data
use auto filter to first select only ones in column F and the invoice of
interest in coulme A this will give you the errors which occur the most in
each invoice. Note if two or three errors happen the same number of times,
they will all show up.

 

[HiArt]

James,

one way to go with the example you have given is to change th
caharacters in to numbers. You do this using the CODE function. The
add or multiply this to the numeric portion of the product code t
derive a (hopefully) unique number.

So if cell G1 held "AB123" you could enter a formula of
=CODE(MID(G1,1,1))*CODE(MID(G1,2,1))*RIGHT(G1,3)

The MID(G1,1,1) and MID(G1,2,1) selects the alpha characters and th
RIGHT(G1,3) the numbers.

if you muliply these you get 527670, but AC123 provides 535665. So the
it's a simple matter to use the MODE function to find the mode.

If you put these columns in the correct order you could even us
VLookUp to return the product ID!

HTH

Ar

 

[olasa]

I'm not sure what you want but here are two alternatives
Picture:
http://www.excelforum.com/attachment.php?attachmentid=3632&stc=1

HTH
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3632 |
+-------------------------------------------------------------------+