Transpose Customer details

enter link description hereIn the below pic column A represents the Customer number and column B represents the customer details. I need to transpose the customer number and customer details as same as the below pic. The number of rows between two customers may vary. Also database is so large. Please find a solution for this..

• Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jun 7, 2023 at 20:55
• @doubleunary As you said, the sheet has been shared. Please check.. Jun 8, 2023 at 4:34

First fill in customer numbers to rows where they are missing like this:

``````=let(
fillDown_,lambda(t,let(ø,iferror(ø),l,max(index(row(t)*iferror(t<>""))),bycol(t,lambda(c,scan(ø,c,lambda(p,u,if(row(u)>l,ø,if(len(u),u,p)))))))),
hstack(choosecols(fillDown_(A1:B), 1), B1:B)
)
``````

Then insert ordinals like this:

``````=arrayformula( if( len(E2:E), "Detail " & countifs(E2:E, E2:E, row(E2:E), "<=" & row(E2:E)), iferror(ø) ))
``````

Finally, pivot by ordinals:

``````=query(
'Sample sheet'!E1:G,
"select E, max(F)
where E is not null
group by E
pivot G",
1
)
``````

See the sample spreadsheet, let(), lambda(), bycol(), scan() and hstack().