0

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..enter image description here

2
  • 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..
    – Anand
    Jun 8, 2023 at 4:34

1 Answer 1

1

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.