Originally Posted by
JohnTopley
Another version using multiple "helper" columns:
Column E
=SUBSTITUTE($D2,"YJK","")+0
Column G
=IFERROR(VLOOKUP(E2,$H$2:$I$36,2,1),"")
column H
=LEFT(J2,4)+0
Column I
numbers 1 to N
Columns H:I are a "lookup" table used by formula in column G to assign data to its appropriate range
Columns A:E are SORTED by column E so all ranges are sequential
in Sheet2
A4
=IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$A$2:$A$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")
B4
=IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$B$2:$B$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")
C4
=IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$C$2:$C$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")
Higlighted refrence needs to changed for each range (see D4 entry)
OR change above to use values in Row 2 (=INT((COLUMNS($A:A)-1)/3)+1)
RESULT: no array formulae used.
but
Bookmarks