Formula to transpose rows to column with specific criteria

[hern]

Hi community,

I have a sheet with rows which I need to put into columns for as many as features each item has.
The first feature must be next to the items, and the following ones all below.

I have this situation:
Screen Shot 2017-11-10 at 21.05.20.png

and I want to get to here:
Screen Shot 2017-11-10 at 21.05.41.png

Can you help with a formula rather than a VBA?

thanks
herns

[Jean.P28]

Can you post your sheet

[hern]

Can you post your sheet

Hi Jean.P28,

Here the attachment.

Let me know if you have any further question and thank you.


hern

[Jean.P28]

My solution

[hern]

Hi Jean.P28,

I have this error when I run the macro :

Run-time error '1004':

Application-defined or object-defined error

Is it maybe because my file has 205 rows to manage?

thanks
hern

[Jean.P28]

Can i see that files

[Jean.P28]

Is the result sheet protect than unprotect the sheet

[hern]

I only copy all the rows and I get that error.

thanks
hern

[Jean.P28]

is the name of the sheet correct in the macro

[hern]

I can't find the name of the sheet in the macro.

sorry!

[Jean.P28]

try this macro

[hern]

Unfortunately it doesn't work.
I attach the original file. It's a little bit more complex than the example but the logic is the same: there is a column of items which are the sum of the features after the sign "=".

Hope it helps.

Let me know if you have any questions.

thanks
hern

[Jean.P28]

I hope that this macro do the job

[hern]

Great job Jean. I almost got to the expected final results.
Here some things missing:

1) All items in column A must be in the "results" sheet > the macro is missing the ones with no features like A19.
2) There are items whose features are in brackets like A218. Here an example of how it's expected:


Is it possible?

[Jean.P28]

no, for me is not possible, to complicated.

[JohnTopley]

You need to clearly explain your logic because looking at last posted file I am none the wiser as how items are allocated to the columns.

[Glenn Kennedy]

Helper in A7:
=INDEX($D$1:$D$4,MATCH(0,INDEX(--(COUNTIF($A$6:A6,$D$1:$D$4)=$AB$1:$AB$4),0),0))

Formula in B7, copied across to D7 and down:
=IF(COUNTIF($A$7:$A7,$A7)=1,OFFSET(INDEX(D$1:D$4,MATCH(0,INDEX(--(COUNTIF($A$6:$A6,$D$1:$D$4)=$AB$1:$AB$4),0),0)),,-3+COLUMNS($B:$B)-1,,),"")

Formula in E7, copied across and down:
=OFFSET(INDEX(D$1:D$4,MATCH(0,INDEX(--(COUNTIF($A$6:$A6,$D$1:$D$4)=$AB$1:$AB$4),0),0)),,3*(COUNTIF(A$7:$A7,$A7)-1))

There night be a small Excel bug as (sometimes) the horizontal range locking drops off when dragging across. If it does, manually re-set.

[JohnTopley]

Glenn,

See file in Post #14.

[Glenn Kennedy]

I used the one in post 3....

[Glenn Kennedy]

I've now looked at the one in 14.... and now have no idea what is wanted.