Transpose Product if quantity greater than zero

[Thomas Kuriakose]

Respected Sirs,

Kindly find attached workbook sample data for products and the quantity. The data is very long.

How can we transpose the product names in columns if the quantity is greater than zero.

Thank you very much,

with regards,
thomas

 

[Khalid NGO]

Hi Thomas, Good Day...

May be this {array} formula:

=INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8>0,ROW($A$2:$A$8)-1),COLUMN(A1)))

{array formula needs to be entered with
a key combination of Ctrl + Shift + Enter}

Enter in D9 and copy across.

Regards,

 

[bines53]

Hi,

=INDEX($A$2:$A$8,AGGREGATE(15,6,ROW($A$2:$A$8)-1/($B$2:$B$8>0),COLUMN(A1)))

David

 

[Thomas Kuriakose]

Respected Sirs,

Thank you so much for the solution provided.

Much appreciated,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sirs,

The above is working with the sample data, but there is shift in the columns in the actual data which is giving #NUM!

The start of the data is in column D.

Kindly check the attached.

with regards,
thomas

 

[bines53]

Hi Thomas,

=INDEX($D$2:$D$8,AGGREGATE(15,6,ROW($D$2:$D$8)-1/($E$2:$E$8>0),COLUMN(A$1)))

David

 

[Khalid NGO]

Hi Thomas,

Just replace the COLUMN(D1) with COLUMN(A1)

Column(D1) gives you 4
but we need to start with 1.

Regards,

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for this information.

Thanks once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sirs,

One more clarification on this, if the row value shifts for multiple data, what should be the reference. I have now plotted the complete data as I was not able to use the formula to change the references.

Thank you very much,

with regards,
thomas

 

[bines53]

Hi Thomas,

See if it's okay .
This horizontally,
INDEX($D$6:$D$100,AGGREGATE(15,6,(ROW($D$6:$D$100)-MIN(ROW($D$6))+1)/($E$6:$E$100>0),COLUMNS($A1:A1)))

This vertical,
=INDEX($D$6:$D$100,AGGREGATE(15,6,(ROW($D$6:$D$100)-MIN(ROW($D$6))+1)/($E$6:$E$100>0),ROWS($A$1:A1)))

David

 

[bosco_yip]

Or this in horizontally without using the COLUMN/ROW function.

In M53, non-CSE formula copy across :

=IFERROR(INDEX($D$6:$D$100,MATCH(0,MMULT(COUNTIF($L53:L53,$D$6:$D$100)+($D$6:$D$100="Total")+($E$6:$E$100=0),1),0)),"")

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

On request:
Can the Total row be eliminated, we would like only the product values.

Thank you very much once again,

with regards,
thomas

 

[bines53]

Hi Thomas,

This horizontally,
=INDEX($D$6:$D$100,AGGREGATE(15,6,(ROW($D$6:$D$100)-MIN(ROW($D$6))+1)/($E$6:$E$100>0)/($D$6:$D$100<>"total"),COLUMNS($A1:A1)))

David

 

[Thomas Kuriakose]

Respected Bosco Sir,

Amazing, this works perfectly.

Kindly let me know whether the values will change if the rows or columns will change.

Thank you very much once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected David Sir,

Thank you very much for the revised formula, this works perfectly.

Thanks once again,

with regards,
thomas