Complex TRANSPOSE problem

Hello,

I am trying to transpose a table. The table consists of rows of items and columns showing the ordered quantity. The cells then show the unit price that is paid for each quantity. I want to transpose this table into a 3 column table with the item, quantity and price, omitting the blanks.

Example table before:

Item	1	2	3	4	5
aaa	10	9	8
bbb	15
ccc	20	18	16	15	14
ddd	10	8	6	5	4
eee	30	25	22

Desired result:

Item	Qty	Price
aaa	1	10
aaa	2	9
aaa	3	8
bbb	1	15
ccc	1	20
ccc	2	18
ccc	3	16
ccc	4	15
ccc	5	14
ddd	1	10
ddd	2	8
ddd	3	6
ddd	4	5
ddd	5	4
eee	1	30
eee	2	25
eee	3	22

I know I can use the Excel function to transpose, but it's a lot of lines, so this is a no go. I can do this in VBA. But I prefer to do this in a formula, partly because it's dynamic. I am quite familiar with ARRAY and TRANSPOSE formula's, but I haven't figured this one out yet. Any ideas?

Example report.xlsx

Welcome to the forum.

TRANSPOSE on its own would not work with this data, anyway. But any formula may be slow with big data - how 'big' are we talking?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Hi
this can be achieved using Power Query. You don't need to do anything, just download the attached file, add data if needed and click " Refresh all" (gemakkelijk gaat ook :-) )

Please Login or Register  to view this content.

350 rows is nothing!

Just looking at a formula solution. I've got it as far as two columns so far:

=LET(t,TOCOL(SUBSTITUTE(B2:F6,"",0)),r,DROP(REDUCE("",A2:A6,LAMBDA(x,y,HSTACK(x,EXPAND(y,,COUNTIF(B1:F1,"<>"),y),""))),,1),HSTACK(TRANSPOSE(FILTER(r,r<>"")),t))

Just got to add the count column and then filter out blanks.

This seems to work:

Please Login or Register  to view this content.

You've marked this as solved.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.