Formula to format source data correctly for Pivot table
Hello,
I have a table which is not formatted correctly for applying pivot.
Can you please share the formula for converting the table to the correct format so that I can use it as a source table for pivot analysis?
Sample data and the expected format are attached.
Thanks in advance for your help!
Last edited by nachiket911; 04-22-2023 at 01:04 PM.
Re: Formula to format source data correctly for Pivot table
is that what you want?
Thank you but I want the Excel formula to do it.
This is sample data but I have a lot more rows and columns.
How to use =INDEX formula to arrange the data in this format?
If you want formula you'll need to wait for someone else
Have a nice day
Please manually mock up what you want.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
A15
=IF(ROWS($A$15:A15)>12*ROWS($A$2:$A$11)/2,"",INDEX($1:$1,,(ROWS($A$15:A15)-1)*2/ROWS($A$2:$A$11)+3))
B15
=IF($A15="","",INDEX($A:$A,MOD((ROWS($A$15:A15)-1)*2,ROWS($A$2:$A$11))+2))
C15
=IF($A15="","",INDEX($1:$100,MOD((ROWS($A$15:A15)-1)*2,ROWS($A$2:$A$11))+2,(ROWS($A$15:A15)-1)*2/ROWS($A$2:$A$11)+3))
D15
=IF($A15="","",INDEX($1:$100,MOD((ROWS($A$15:B15)-1)*2,ROWS($A$2:$A$11))+3,(ROWS($A$15:B15)-1)*2/ROWS($A$2:$A$11)+3))
copied down.
nachiket911,
Hi. Before we get going on this one...
Is your forum profile showing the Excel PRODUCT that you need this request to work with? 16.72 >>>might<<< be something for a Mac. I don't know.
The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.
Cheers,
Glenn.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I have a vague idea that 16.72 is O365 for Mac...
If so, try this. Delete ALL expected results.
In any cell:
Formula:
Please Login or Register to view this content.
No copy/paste or dragging required. Results spill across and down.
It worked!!!
Thank you all, especially windknife and glen! Really appreciate it!
You're welcome.
But...
Please update your profile, as requested.
You are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks