How to populate the unit # based on the last max number in a range in excel?

[lowestsky27]

I tried creating a table to aid a manufacturing process. Where technicians perform some test and record the results. One of the column (unit #) auto populates based on other column entries of the table.

The unit # column helps keep track since a technician is supposed to test 14 units in a shift (4 different shifts - 1,2,3,4). The way I wrote the formula, it populates an "out of range" message when the unit being tested is not conforming to the standards and the tech needs to move on to the next unit. Leaving the "out of range" entry as is (this entry is needed for record keeping).

The table works as intended unless there are consecutive "out of range" units in the column. Then it goes back to unit# 1 (instead of unit#8 in case of this example)

Formula:

=IFERROR(IF(C16="","",IF(OR(AG16="Too Thick",AG16="Too Thin"),"Out of Range",IF(OR([@DATE]<>D15,C16<>C15),1,IF(OR(AG15="Too Thick",AG15="Too Thin"),IF([@DATE]=D14,AE14+1,1),AE15+1)))),"1")

What changes would you suggest I make to my formula? I tried MAXIFS at the end but got a !spill# error.

 

[johnny51981]

I don't want to assume what the whole formula is trying to accomplish, as the working in rows 14 through 16 confuses me a bit...but I'm not even one full coffee in today.

But what I can offer is, anytime you want to result in a Unit #, you'll want to include a MAX($AE$14:$AE14)+1. Making sure that the $AE14 is the referring to the row directly above the row in which the formula is populated. Another example, the formula in populated in cell AE26 would read MAX($AE$14:$AE25)+1

Then as you have a need to drag it down, it should grow correctly.

Also take a look at each time you are looking for your TRUE result to be "1", that would also cause your Unit #'s to start from the beginning. You'll want to use the same MAX formula as provided above.

Good luck!

 

[Fluff]

Hi & welcome to MrExcel.

How about

=IF(OR(AG14={"Too thick","Too Thin"}),"Out of range",MAXIFS(AE$13:AE13,C$14:C14,C14,D$14:D14,D14)+1)

 

[lowestsky27]

Hi & welcome to MrExcel.

How about

=IF(OR(AG14={"Too thick","Too Thin"}),"Out of range",MAXIFS(AE$13:AE13,C$14:C14,C14,D$14:D14,D14)+1)

Thank you, @Fluff. Works perfect! And glad to be here.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[lowestsky27]

You're welcome & thanks for the feedback.

Hope you have been well Fluff! I came across another issue and haven't been able to fix it. If there are multiple out of ranges in the next shift that falls on the same date the numbers are not adding up correctly. I tried removing the date criteria from MAXIFS but it did not work. Any more ideas?

=IFERROR(IF(C33="","",IF(OR(AG33="Too Thick",AG33="Too Thin"),"Out of Range",IF(OR([@DATE]<>D32,C33<>C32),1,IF(OR(AG32="Too Thick",AG32="Too Thin"),IF([@DATE]=D31,AE31+1,1),AE32+1)))),IF(OR(AG33={"Too thick","Too Thin"}),"Out of range",MAXIFS(AE$13:AE32,C$14:C33,C33,D$14:D33,D33)+1))

 

[Fluff]

That is not the formula I suggested.

 

[lowestsky27]

That is not the formula I suggested.

My mistake, I thought that was extension suggested for my formula. But when I use your suggested formula as is, it does not populate the right unit #

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[lowestsky27]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the mini-sheet suggestion. Here is the sample data

Columbus modified testdropball_COL.xlsx
	C	D	E	F																							AC	AD	AE	AF	AG
11	SHIFT	DATE	LUX ID	Job ID																							Eye	BRKG CODE	Lens #	Measured	Result
12	4	8/26/2022	524825	5269126																							L		1	1.92
13	4	8/26/2022	524825	5296138																							L		Out of Range	0.05	Too Thin
14	1	10/31/2022	601177	9817353																							L	24	1	2.28	Pass
15	1	10/31/2022	601177	9810704																							R	82	2	2.32	Pass
16	1	10/31/2022	601177	9787314																							L	15	3	1.74	Pass
17	1	10/31/2022	601177	9787314																							R	15	Out of Range	1.57	Too Thin
18	1	10/31/2022	601177	9792383																							R	14	Out of Range	2.37	Too Thick
19	1	10/31/2022	601177	9758905																							L	27	4	2.13	Pass
20	1	10/31/2022	601177	9810512																							L	27	5	2.11	Pass
21	1	10/31/2022	601177	9811087																							L	30	6	1.85	Pass
22	1	10/31/2022	601688	9813865																							R	29	7	1.91	Pass
23	1	10/31/2022	601688	9813865																							L	29	8	1.88	Pass
24	1	10/31/2022	601688	9803756																							L	24	9	2.21	Pass
25	1	10/31/2022	601688	9814480																							L	27	10	1.90	Pass
26	1	10/31/2022	601688	9814000																							L	14	11	1.88	Pass
27	1	10/31/2022	601688	9801319																							L	27	12	2.40	Pass
28	1	10/31/2022	601688	9822871																							R	15	13	2.38	Pass
29	1	10/31/2022	601688	9809476																							L	82	14	1.91	Pass
30	2	10/31/2022	601313	9772136																							R	30	1	1.75	Pass
31	2	10/31/2022	601313	9759044																							R	24	Out of Range	2.20	Too Thick
32	2	10/31/2022	601313	9759044																							L	24	Out of Range	2.11	Too Thick
33	2	10/31/2022	601313	9771544																							R	24	15	1.47	Pass
34	2	10/31/2022	601313	9771544																							L	24	16	1.55	Pass
35	2	10/31/2022	601313	9794444																							L	24	Out of Range	2.54	Too Thick
36	2	10/31/2022	601313	9753607																							L	24	17	2.06	Pass
37	2	10/31/2022	601313	9798500																							L	24	18	1.69	Pass
38	2	10/31/2022	601313	9788247																							L	24	19	1.73	Pass
39	2	10/31/2022	601313	9795754																							L	24	Out of Range	2.59	Too Thick
40	2	10/31/2022	601313	9794437																							L	24	20	1.92	Pass
41	2	10/31/2022	601313	9795107																							L	15	21	2.42	Pass
42	2	10/31/2022	601313	9752453																							L	82	22	2.05	Pass
43	2	10/31/2022	601313	9782970																							R	15	23	2.45	Pass
44	2	10/31/2022	601313	9798400																							R	32	24	1.88	Pass
45	2	10/31/2022	601313	9764675																							R	24	25	2.17	Pass
46	2	10/31/2022	601313	9764675																							L	24	26	2.36	Pass
47	2	10/31/2022	601313	9776024																							L	92	27	1.54	Pass
48	2	10/31/2022	601313	9800602																							L	24	28	1.93	Pass
49
50
51
Drop Ball Sheet

Cell Formulas
Range	Formula
AG12:AG13,AG49:AG51,AG39,AG35,AG31:AG32,AG17:AG18	AG12	=IF([@Measured]<[@MIN],"Too Thin",IF([@Measured]>[@MAX],"Too Thick",""))
AE12:AE13	AE12	=IFERROR(IF(C12="","",IF(OR(AG12="Too Thick",AG12="Too Thin"),"Out of Range",IF(OR([@DATE]<>D11,C12<>C11),1,IF(OR(AG11="Too Thick",AG11="Too Thin"),IF([@DATE]=D10,AE10+1,1),AE11+1)))),IF(OR(AG12={"Too thick","Too Thin"}),"Out of range",MAXIFS(AE11:AE$13,C12:C$14,C12,D12:D$14,D12)+1))
AE14:AE51	AE14	=IFERROR(IF(C14="","",IF(OR(AG14="Too Thick",AG14="Too Thin"),"Out of Range",IF(OR([@DATE]<>D13,C14<>C13),1,IF(OR(AG13="Too Thick",AG13="Too Thin"),IF([@DATE]=D12,AE12+1,1),AE13+1)))),IF(OR(AG14={"Too thick","Too Thin"}),"Out of range",MAXIFS(AE$13:AE13,C$14:C14,C14,D$14:D14,D14)+1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG12:AG1999	Cell Value	contains "Fail"	text	NO
AG12:AG1999	Cell Value	contains "Too"	text	NO
AF12:AF1999	Cell	contains a blank value	text	NO
AF12:AF1999	Cell Value	not between $H12 and $I12	text	NO