0

Im currently trying to make an inventory system where users can check out multiple items at once, via a google form. As such, i want to get all of the inventory data into one column so that i can use vlookup. I am trying to find a way that I can transpose this data while keeping the name and timestamp linked to each item of data.

A link to what im trying to do can be found here, any suggestions would be greatly appreciated. https://docs.google.com/spreadsheets/d/1t8nEDrxK_dynBCWNksY4J1lIEU26AeLMh7UZ0qvkAis/edit?usp=sharing

I followed the advice of another answer posted but couldnt get it to work for many columns

tairann
  • 1

1 Answers1

0

You may try:

=let(rep_,A2:B, grp_,C2:L, size_,1,
     Σ,reduce(wraprows(,columns(rep_)+size_,),sequence(columns(grp_)/size_,1,1,size_),lambda(a,c,{a;{rep_,choosecols(grp_,sequence(size_,1,c))}})),
     sort(filter(Σ,index(Σ,,column(grp_))<>""),1,1))
  • Adjust the ranges in the first line of formula

enter image description here

Updated answer:

=let(rep_,A2:C, grp_,D2:W, size_,1,
     Σ,reduce(wraprows(,columns(rep_)+size_,),sequence(columns(grp_)/size_,1,1,size_),lambda(a,c,{a;{rep_,choosecols(grp_,sequence(size_,1,c))}})),
     sort(filter(Σ,index(Σ,,column(grp_))<>""),1,1))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • thank you very much! if possible, how do i extend this further? for example, if i want to make a new column for emails, that doesnt duplicate like the timestamps and names, is there a way to do so? so sorry for troubling you. i have updated the previous link with a “Updated Form Data” just for clearer explanation of what i mean. Also, if theres a way to extend this to allow for more items to be transposed, that would be appreciated too! – tairann Jun 16 '23 at 02:07
  • Try the updated solution posted in the answer. the repeating/duplicating columns `A2:C` needs to be listed under ***rep_*** and the item columns `D2:W` goes to ***grp_*** in the [let function](https://support.google.com/docs/answer/13190535?hl=en) – rockinfreakshow Jun 16 '23 at 06:06