CODEWITHSUNDEEP
excel-formula
MD40
MD40

Reputation: 325

Fill data into other blank cells within predefined group of data with data from that defined group

I am developing some data sheet to check process control limits. I've could develop the system as required. But I like to increase the performance in there. Part of the data sheet that I've developed will described below.

Base Raw Data enter image description here

LSL & USL Limits are as mention in above the table for ID. First I've extract the data into Help Col 01(Col V) which are within limits using below formula.

=IF(AND(U15>=950,U15<=1150),U15,"")

After then I've inserted

=IFERROR(INDEX($V$15:$V$30,SMALL(IF($V$15:$V$30<>"",ROW($V$15:$V$30)-ROW($V$15)+1),T15)),"")

formula into Help Col 02(Col W), I've removed blank cells. Next task was to add filtered Help Col 2 data into each cell into Final data list column according to the group. Groups are described here.
Group 1 = 1-4,Group 2 = 5-8,Group 3 = 9-12,Group 4 = 13-16, I could approached that task using below formula.

=INDEX($W$15:$W$30,MATCH(INT(T15/4.25)+1,$T$15:$T$30,0))

My problem is, not possible to merge these formula together due to second formula. If anybody have an idea to merge 1st & 2nd formula into 3rd formula. Any answers are welcome here.

Upvotes: 0

Views: 113

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

In V4, formula copied down :

=IFERROR(INDEX(AGGREGATE(14,6,U$4:U$99/(U$4:U$99<>"")/(U$4:U$99>=V$1)/(U$4:$U99<=V$2),ROW(INDIRECT("1:"&COUNTIFS(U:U,">="&V$1,U:U,"<="&V$2)))),INT((ROW(A1)-1)/4)+1),"")

Or, this shorter :

=IFERROR(INDEX(AGGREGATE(14,6,U$4:U$99/(U$4:U$99<>"")/(U$4:U$99>=V$1)/(U$4:$U99<=V$2),ROW($1:$99)),INT((ROW(A1)-1)/4)+1),"")

enter image description here

Upvotes: 1

Related Questions