Thank you for your time to view my question.
I have production data that will list component part #s as well as production part #in one column (Product Code in table below); in the adjacent column, the serial number of each component and production unit is listed (Barcode Serial Number). Whenever we replenish a box of components used in the production process, we scan a ticket with the part number but has a different serial number from box to box.
I am trying to devise a way to count the number of serial #s between the same component #s; here is a sample of my data. BTW, I've tried a pivot table but it gives only an average; I need count of serial numbers so that I can get a range of how many units are produced between boxes.
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65, align: center"]Scan Date
[/TD]
[TD="class: cms_table_xl65, width: 104, align: center"]Operation Type[/TD]
[TD="class: cms_table_xl65, width: 99, align: center"]Product Code[/TD]
[TD="class: cms_table_xl65, width: 71, align: center"]Machine #[/TD]
[TD="class: cms_table_xl65, width: 78, align: center"]Workpost #[/TD]
[TD="class: cms_table_xl65, width: 152, align: center"]Barcode Serial Number[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:49[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCJ80S2586[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]58625478[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 23:49[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl69, align: center"]T1[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]65804077[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:28[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl67, align: center"]T1[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]65804076[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 23:29[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCJ80S2586[/TD]
[TD="class: cms_table_xl69, align: center"]S2[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]58625477[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:31[/TD]
[TD="class: cms_table_xl67, align: center"]<acronym title="Google Page Ranking">PR</acronym>[/TD]
[TD="class: cms_table_xl67, align: center"]FECX187029[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]FEX[/TD]
[TD="class: cms_table_xl67, align: center"]17092014-A85[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl70, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl72, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 22:58[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl67, align: center"]T1[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]65804070[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 16:29[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARC9L0S2688[/TD]
[TD="class: cms_table_xl69, align: center"]S2[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]68850901[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 16:23[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARC9L0S2688[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]68850900[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 16:20[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl69, align: center"]T1[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]65804012[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 16:24[/TD]
[TD="class: cms_table_xl67, align: center"]<acronym title="Google Page Ranking">PR</acronym>[/TD]
[TD="class: cms_table_xl67, align: center"]FECX166884[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]FEX[/TD]
[TD="class: cms_table_xl67, align: center"]18092014-D14
[/TD]
[/TR]
</tbody>[/TABLE]
I am working with several thousand lines of data; any and all help is greatly appreciated!
Thanks and always in search of my next cold one,
NearBeer
I have production data that will list component part #s as well as production part #in one column (Product Code in table below); in the adjacent column, the serial number of each component and production unit is listed (Barcode Serial Number). Whenever we replenish a box of components used in the production process, we scan a ticket with the part number but has a different serial number from box to box.
I am trying to devise a way to count the number of serial #s between the same component #s; here is a sample of my data. BTW, I've tried a pivot table but it gives only an average; I need count of serial numbers so that I can get a range of how many units are produced between boxes.
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65, align: center"]Scan Date
[/TD]
[TD="class: cms_table_xl65, width: 104, align: center"]Operation Type[/TD]
[TD="class: cms_table_xl65, width: 99, align: center"]Product Code[/TD]
[TD="class: cms_table_xl65, width: 71, align: center"]Machine #[/TD]
[TD="class: cms_table_xl65, width: 78, align: center"]Workpost #[/TD]
[TD="class: cms_table_xl65, width: 152, align: center"]Barcode Serial Number[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:49[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCJ80S2586[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]58625478[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 23:49[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl69, align: center"]T1[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]65804077[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:28[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl67, align: center"]T1[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]65804076[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 23:29[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCJ80S2586[/TD]
[TD="class: cms_table_xl69, align: center"]S2[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]58625477[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 23:31[/TD]
[TD="class: cms_table_xl67, align: center"]<acronym title="Google Page Ranking">PR</acronym>[/TD]
[TD="class: cms_table_xl67, align: center"]FECX187029[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]FEX[/TD]
[TD="class: cms_table_xl67, align: center"]17092014-A85[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl70, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[TD="class: cms_table_xl71, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl72, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[TD="class: cms_table_xl73, align: center"]…[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 22:58[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl67, align: center"]T1[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]65804070[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 16:29[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARC9L0S2688[/TD]
[TD="class: cms_table_xl69, align: center"]S2[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]68850901[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 16:23[/TD]
[TD="class: cms_table_xl67, align: center"]CA[/TD]
[TD="class: cms_table_xl67, align: center"]CARC9L0S2688[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]F01[/TD]
[TD="class: cms_table_xl67, align: center"]68850900[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl68, align: center"]09/19/2014 16:20[/TD]
[TD="class: cms_table_xl69, align: center"]CA[/TD]
[TD="class: cms_table_xl69, align: center"]CARCAF0T1658[/TD]
[TD="class: cms_table_xl69, align: center"]T1[/TD]
[TD="class: cms_table_xl69, align: center"]F01[/TD]
[TD="class: cms_table_xl69, align: center"]65804012[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, align: center"]09/19/2014 16:24[/TD]
[TD="class: cms_table_xl67, align: center"]<acronym title="Google Page Ranking">PR</acronym>[/TD]
[TD="class: cms_table_xl67, align: center"]FECX166884[/TD]
[TD="class: cms_table_xl67, align: center"]S2[/TD]
[TD="class: cms_table_xl67, align: center"]FEX[/TD]
[TD="class: cms_table_xl67, align: center"]18092014-D14
[/TD]
[/TR]
</tbody>[/TABLE]
I am working with several thousand lines of data; any and all help is greatly appreciated!
Thanks and always in search of my next cold one,
NearBeer