I have an excel sheet that looks like this:
1 A B C
2 D E F
How can I convert it to:
1 A
1 B
1 C
2 D
2 E
2 F
Thanks.
-
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.– Community BotJan 25, 2022 at 15:05
-
And how would you like to accomplish this, ie formula, vba, or power query? Also what version of Excel are you using?– Scott CranerJan 25, 2022 at 15:05
-
I am using Microsoft 365 Apps for enterprise. I do not have any knowledge of vba or power query. Formula would be perfect, but I am open to every solution.– david_carusoJan 25, 2022 at 15:10
Add a comment
|
1 Answer
First, create a header row if you don't already have one. The names are not important.
Press Alt+D+P to open the PivotTable and PivotChart Wizard (there are other ways to do this), select "Multiple Consolidation Ranges" and click next.
Select "I will create the page fields" and click next.
Select your data range, click Add, then click Finish.
It'll create a new worksheet with a pivot table in it. Remove the Column and Row fields from that pivot table so all you have is a grand total.
Double-click on the number in that grand total (6 in the example) and it will create a new worksheet with the data tabulated the way you want. Delete the "Column" field and you're good to go.
answered Jan 25, 2022 at 15:54
