Rearranging vertical data to horizontal

[mq1973]

Hi Guys,

I have a list of products and the equipment used to make these products based on the process.
The processes are arranged vertically on multiple rows. I need to rearrange them horizontally on one row.
The yellow highlighted cells is one product that has 4 processes and each process may take more than one equipment (columns E to L). So I need these equipment to be arranged in one row (on columns M to T)
In other words, I should get Saizoner, MX_DRUM, STX_32 and KLOCKNER_2 in M4 to T4

I am attaching the file as well.

Thank you guys

Table.jpg

[JeteMc]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Let us know if you have any questions.

[mq1973]

Thank you JeteMc,

I needed to add more columns for "Blistering" and I followed the same formula for "Compression" but I didn't get matching results.
Blistering.png

This is what I used (array formula)

Please Login or Register  to view this content.

[JeteMc]

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.

[mq1973]

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

That's what I have on AA4

Please Login or Register  to view this content.

Blistering.jpg

[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.

Please Login or Register  to view this content.

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.

[FlameRetired]

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

This calculates much faster than my previous formula.

[JeteMc]

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:

Please Login or Register  to view this content.

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

[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.

[mq1973]

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:

Please Login or Register  to view this content.

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

This works like charm

Thank you so much JeteMc

[mq1973]

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.

Please Login or Register  to view this content.

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)

[JeteMc]

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

[FlameRetired]

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.