1

I have raw data in a specific format and I want to transform it into some other format. I tried using Index-Match function, it's working for Revenue but not for Profit. For ex: - Revenue of "ABC" in "01-2023" is 100 and Profit is 50 but when I run Index-Match, it's giving Profit as 100 because "Match" value for 01-2023 is 2. Can someone please help me with this?

1

2

vimuth
  • 5,064
  • 33
  • 79
  • 116
Ankit Sharma
  • 11
  • 2
  • Welcome to Stack Overflow. Normally you should tag spreadsheet questions as either google-sheets or excel, not both - can you clarify which one you are looking for please? – Tom Sharpe Mar 04 '23 at 15:06
  • Can you post the formula(s) that you've tried? – Kairu Mar 06 '23 at 04:05

1 Answers1

0

Unpivot (Excel Formula)

=LET(Data,A2:G6,
    dRows,ROWS(Data)-1,dCols,(COLUMNS(Data)-1)/2,Cells,dRows*dCols,
    rSeq,MOD(SEQUENCE(Cells,,0),dRows)+1,vSeq,ROUNDUP(SEQUENCE(Cells)/dRows,0),
    rd,INDEX(DROP(TAKE(Data,,1),1),rSeq),hd,INDEX(TOCOL(DROP(TAKE(Data,1),,dCols+1)),vSeq),
    vData,DROP(DROP(Data,1),,1),rev,TOCOL(TAKE(vData,,dCols)),pro,TOCOL(TAKE(vData,,-dCols)),
HSTACK(rd,hd,rev,pro))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28