Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding the mode (alpha numeric)
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. |
#2
|
|||
|
|||
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. "Jamesy" wrote: 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. |
#3
|
|||
|
|||
James, one way to go with the example you have given is to change the caharacters in to numbers. You do this using the CODE function. Then add or multiply this to the numeric portion of the product code to 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 the RIGHT(G1,3) the numbers. if you muliply these you get 527670, but AC123 provides 535665. So then 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 use VLookUp to return the product ID! HTH Art -- HiArt ------------------------------------------------------------------------ HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953 View this thread: http://www.excelforum.com/showthread...hreadid=390156 |
#4
|
|||
|
|||
I'm not sure what you want but here are two alternatives Pictu http://www.excelforum.com/attachment...tid=3632&stc=1 HTH Ola Sandström +-------------------------------------------------------------------+ |Filename: Clipboard01.jpg | |Download: http://www.excelforum.com/attachment.php?postid=3632 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=390156 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
VLOOKUP WITH ALPHA NUMERIC | Excel Worksheet Functions | |||
Search string for alpha or numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) |