S sadasivan New Member Oct 23, 2022 #1 Hi Friends please help me how to change data from sheet 1 to sheet 2 like attachment Attachments OPTION CHAIN DATA.xlsx 12.2 KB · Views: 10
P Peter Bartholomew Well-Known Member Oct 23, 2022 #2 This is a 365 formula and is based on the assumption that there are always corresponding pairs CE and PE within the input. Code: = LET( reordered, CHOOSECOLS(input,1,2,3,4,5,7,8,6), optionType, CHOOSECOLS(input,7), CE, SORT(FILTER(reordered, optionType="CE")), PE, SORT(FILTER(reordered, optionType="PE")), DROP(HSTACK(CE, PE),,-1) ) Life will get a little more complicated if you have to look up the partner record rather than assuming SORT will align the pairs. Attachments OPTION CHAIN DATA.xlsx 14.1 KB · Views: 8
This is a 365 formula and is based on the assumption that there are always corresponding pairs CE and PE within the input. Code: = LET( reordered, CHOOSECOLS(input,1,2,3,4,5,7,8,6), optionType, CHOOSECOLS(input,7), CE, SORT(FILTER(reordered, optionType="CE")), PE, SORT(FILTER(reordered, optionType="PE")), DROP(HSTACK(CE, PE),,-1) ) Life will get a little more complicated if you have to look up the partner record rather than assuming SORT will align the pairs.
