Rearranging vertical data to horizontal

The results in the attached copy of your table were achieved by putting the following array entered formula* in M4 and, after activating, dragging across to T4 and down:

Formula:

=IFERROR(INDEX($I$4:$I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081=M$3)*($F$4:$F$1081=$F4)),FALSE),1)),"")

Next the 'Compression' columns were filled using the following modification of the array entered formula*:

Formula:

=IFERROR(INDEX(I$4:I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081="Compression")*($F$4:$F$1081=$F4)),FALSE),1)),"")

Let us know if you have any questions.

When I opened the file the formula in AA4 read: =IFERROR(INDEX(I$4:I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081="Blistering")*($F$4:$F$1081=$F325)),FALSE),1)),"")
After changing the formula as follows (and dragging across to column AC then down to row 10) it showed CH150_GF in AB10 as I would expect: =IFERROR(INDEX(I$4:I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081="Blistering")*($F$4:$F$1081=$F4)),FALSE),1)),"")
Let us know if you have any questions.

I am not sure what it is but something is missing
Look at row 439

That's what I have on AA4

=IFERROR(INDEX(I$4:I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081="Blistering")*($F$4:$F$1081=$F4)),FALSE),1)),"")

Blistering.jpg

This may be an overly ambitious (read complicated) approach. In M4:T4 non-array entered. Double click the fill handle. (Make sure calculation is set to Automatic.) It takes about 4-5 seconds to calculate.

I have not done a painstaking check of the returns. What I have checked works so far.

Formula:

=IFERROR(INDEX(INDEX($I$4:$L$1081,MATCH(1,INDEX(1/($E4=$E$4:$E$1081),0),-1),1):INDEX($I$4:$JL1081,MATCH(2,INDEX(1/($E4=$E$4:$E$1081),0),1),4),

MATCH(TRIM(LEFT(SUBSTITUTE(M$3," ",REPT(" ",99)),99)),INDEX($H$4:$H$1081,MATCH(1,INDEX(1/($E4=$E$4:$E$1081),0),-1),):

INDEX($H$4:$H$1081,MATCH(2,INDEX(1/($E4=$E$4:$E$1081),0),1),),0),COUNTIF($M$3:M$3,LEFT(M$3,5)&"*")),"")

PS I forgot to mention ... if you are willing to have 2-4 helper columns this formula can be made much smaller. It will likely speed up calculations as well. Just let me know.

Originally Posted by FlameRetired

I have not done a painstaking check of the returns. What I have checked works so far.

No it doesn't. Needs a rework.

Originally Posted by FlameRetired

This may be an overly ambitious (read complicated) approach. In M4:T4 non-array entered. Double click the fill handle. (Make sure calculation is set to Automatic.) It takes about 4-5 seconds to calculate.

I have not done a painstaking check of the returns. What I have checked works so far.

Formula:

=IFERROR(INDEX(INDEX($I$4:$L$1081,MATCH(1,INDEX(1/($E4=$E$4:$E$1081),0),-1),1):INDEX($I$4:$JL1081,MATCH(2,INDEX(1/($E4=$E$4:$E$1081),0),1),4),

MATCH(TRIM(LEFT(SUBSTITUTE(M$3," ",REPT(" ",99)),99)),INDEX($H$4:$H$1081,MATCH(1,INDEX(1/($E4=$E$4:$E$1081),0),-1),):

INDEX($H$4:$H$1081,MATCH(2,INDEX(1/($E4=$E$4:$E$1081),0),1),),0),COUNTIF($M$3:M$3,LEFT(M$3,5)&"*")),"")

PS I forgot to mention ... if you are willing to have 2-4 helper columns this formula can be made much smaller. It will likely speed up calculations as well. Just let me know.

Thank you FlameRetired, This actually works, expect one thing. Please see row 740
This is really interesting. I checked everything else and its perfect. Just this row 740 is not showing the matching data (where it says FILLING_CAPSULE)

Cap Filling.jpg

Please see the attached file (sheet 1)

Originally Posted by mq1973

Thank you FlameRetired, This actually works, expect one thing. Please see row 740
This is really interesting. I checked everything else and its perfect. Just this row 740 is not showing the matching data (where it says FILLING_CAPSULE)

Please see the attached file (sheet 1)

That is because the formula depends upon matching/near matching headers to 'Line Name' items. That's also true of my second formula/upload. I had never noticed FILLING_CAPSULE until now.

To make either formula work would require a redesign of the data layout.

This needs a different approach. I haven't looked at his(/her?) solution in detail, but apparently JeteMc cracked it.

I went ahead with the helper columns (2 of them) They can be moved out to XFC:XFD if you wish and re-hidden. The formula in U4:V4 and filled down calculates the lower and upper row boundaries of each 'Bulk' group.

Formula:

=INDEX(MATCH({1,2},1/($E4=$E$4:$E$1081),{-1,1}),COLUMNS($U:U))

The formula (still non-array) in M4 down and across is

Formula:

=IFERROR(INDEX(INDEX($I$4:$L$1081,$U4,1):INDEX($I$4:$JL1081,$V4,4),MATCH(TRIM(LEFT(SUBSTITUTE(M$3," ",REPT(" ",99)),99)),

INDEX($H$4:$H$1081,$U4,):INDEX($H$4:$H$1081,$V4,),0),COUNTIF($M$3:M$3,LEFT(M$3,5)&"*")),"")

This calculates much faster than my previous formula.

The formulas have been modified and hopefully now return expected results, however I would encourage continued testing/observation.
The modification to the formula for 'Blistering' has been highlighted, it now reads:

Formula:

=IFERROR(INDEX(I$4:I$1081,SMALL(IFERROR((ROW($4:$1081)-3)/(($H$4:$H$1081="Blistering")*($F$4:$F$1081=$F4)*($E$4:$E$1081=$E4)),FALSE),1)),"")

The other formulas have been similarly updated.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. I hope that you have a blessed day.