Hey,
I have this formula that I've been using in Google Sheets but don't quite know how to do things in Excel world.
Example data
Capture.PNG
Google Sheets formula
=ARRAYFORMULA({HLOOKUP(VLOOKUP(F2,F2:2,FLOOR((ROW(INDIRECT("A1:A"&COUNTA(F3:F)*COUNTA(F2:2)))-1)/COUNTA(F3:F))+1,0),F2:H,{ROW(INDIRECT("A1:A"&COUNTA(F3:F)*COUNTA(F2:2)))^0,MOD(ROW(INDIRECT("A1:A"&COUNTA(F3:F)*COUNTA(F2:2)))-1,COUNTA(F3:F))+2},0),MOD(ROW(INDIRECT("A1:A"&COUNTA(F3:F)*COUNTA(F2:2)))-1,COUNTA(F3:F))+1})
Usually I'm not doing such complex things and then Google sheets is fine. This time as I need to have many rows their row limit will become a problem soon so I thought I'd transition now.
Let me know your solutions.
Bookmarks