**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi, I need your help understand what's the best way I can convert Column Data Fields to Row Data Fields
Please get back to me asap.

Solved by X. B. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
13/10/2018 - 04:20

Hi, welcome to Got it Pro-Excel!

Excelchat Expert
13/10/2018 - 04:20

Let me ask you a couple of quick questions to make sure I fully understand your problem.

Excelchat Expert
13/10/2018 - 04:20

Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. We also currently do not support VBA/Macro solutions.

User
13/10/2018 - 04:20

Can we talk or is this just a chat portal?

User
13/10/2018 - 04:20

It's my 1st time using your services

Excelchat Expert
13/10/2018 - 04:20

This is strictly chat as of the moment.

Excelchat Expert
13/10/2018 - 04:20

Do you have a file you can share?

User
13/10/2018 - 04:21

what you mean as of the moment?

User
13/10/2018 - 04:21

today or what?

Excelchat Expert
13/10/2018 - 04:21

We're not yet supporting other means of communication other than chat within the platform.

Excelchat Expert
13/10/2018 - 04:22

Do you have a file you can share?

User
13/10/2018 - 04:23

License State name Location Name Location Number# Net contents Depletions? 11/2017 12/2017 01/2018 02/2018 03/2018 04/2018 05/2018 06/2018 07/2018 08/2018 09/2018
CA ABC 900012770 300 N 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700187982 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700161725 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700163341 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910008341 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910008690 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910008209 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910007991 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910007989 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910008561 300 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 900012770 720 N 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700204934 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700204061 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700189155 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 700196134 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910007993 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
CA ABC 910008421 720 Y 10.00 12.00 14.00 16.00 18.00 20.00 22.00 24.00 26.00 28.00 30.00
just copy paste

Excelchat Expert
13/10/2018 - 04:24

Can you provide details about your requirement to can convert Column Data Fields to Row Data Fields

User
13/10/2018 - 04:24

so basically I want to be able to build a pivot table and manage data in an easy way

User
13/10/2018 - 04:25

I want months/years in rows

User
13/10/2018 - 04:25

in a tabular form

Excelchat Expert
13/10/2018 - 04:26

Are you using Excel or Google Sheets?

User
13/10/2018 - 04:26

excel 2016

Excelchat Expert
13/10/2018 - 04:27

Ok, what else do you need aside from using converting your months years to row?

User
13/10/2018 - 04:28

I will have several tabs with several states, wondering if there's a way to have a "summary" pivot table that pulls the data from different tabs

User
13/10/2018 - 04:28

and enables me to have all the data into 1 tab

Excelchat Expert
13/10/2018 - 04:29

Unfortunately, Pivot Table can only use 1 data reference. What we can do is consolidate your data first then create a pivot table.

User
13/10/2018 - 04:30

yes, show me how pl

User
13/10/2018 - 04:31

I've just copied the e.g. of format I am looking for but if you have any better to manipulate the data pl share

Excelchat Expert
13/10/2018 - 04:31

First question - Do you really require a pivot table? Because we can use formulas to consolidate your data even if they're on different sheets.

Excelchat Expert
13/10/2018 - 04:32

Will you be able to send your file? You can send it via the attachment button.

User
13/10/2018 - 04:32

this is the file

User
13/10/2018 - 04:33

is there anything that is not clear?

Excelchat Expert
13/10/2018 - 04:33

I thought you have more tabs with different states?

User
13/10/2018 - 04:33

above you have the raw data

User
13/10/2018 - 04:33

below you have the data in the format I am looking for

User
13/10/2018 - 04:34

there you go

User
13/10/2018 - 04:34

show me hoew

User
13/10/2018 - 04:34

how

Excelchat Expert
13/10/2018 - 04:35

I updated Sheet1 with 2nd row of data, is that the format that you need?

User
13/10/2018 - 04:35

yes

Excelchat Expert
13/10/2018 - 04:36

Let me just confirm.

User
13/10/2018 - 04:36

(what does time remaining mean?)

User
13/10/2018 - 04:36

will I have to start this allover with you or any of your colleagues?

Excelchat Expert
13/10/2018 - 04:36

Based on what you’ve shared, you need the table above to be converted like the table below, do you believe that will address your problem?

Excelchat Expert
13/10/2018 - 04:36

You can extend the session for another 40 minutes as needed.

User
13/10/2018 - 04:36

yes

Excelchat Expert
13/10/2018 - 04:37

I’m going to create that for you. This should take me a couple of minutes to finish.

Excelchat Expert
13/10/2018 - 04:37

Then, I'll walk you through how I did it.

User
13/10/2018 - 04:37

Can I see you performing it?

User
13/10/2018 - 04:37

can you share your screen?

Excelchat Expert
13/10/2018 - 04:38

I'm doing it in Excel from my end. Unfortunately, we have no screen sharing options unless you're working in Google Sheets.

User
13/10/2018 - 04:38

I have a google sheets account

User
13/10/2018 - 04:38

what do you need?

User
13/10/2018 - 04:38

or I need?

Excelchat Expert
13/10/2018 - 04:38

If you're working in Google Sheets, I'll work directly in our shared Google Sheet.

Excelchat Expert
13/10/2018 - 04:39

Which you can see me navigate

User
13/10/2018 - 04:40

great

Excelchat Expert
13/10/2018 - 04:40

It'll be better if I do it in Excel though using Pivot Tables.

User
13/10/2018 - 04:40

I'm just loggin in

User
13/10/2018 - 04:40

ok then

User
13/10/2018 - 04:40

do it

Excelchat Expert
13/10/2018 - 04:40

Pivot Table of Google Sheets and Excel works differently.

User
13/10/2018 - 04:40

and show me after

User
13/10/2018 - 04:40

got it

Excelchat Expert
13/10/2018 - 04:40

Yes, I will.

User
13/10/2018 - 04:40

I use excel

User
13/10/2018 - 04:41

just use google sheets for sharing elementary stuff

Excelchat Expert
13/10/2018 - 04:41

Got it.

Excelchat Expert
13/10/2018 - 04:45

How many rows do you usually have per tab?

User
13/10/2018 - 04:45

5000

Excelchat Expert
13/10/2018 - 04:46

Ok, that's a lot.

User
13/10/2018 - 04:55

how's it going?

Excelchat Expert
13/10/2018 - 04:55

Please take note that by doing this format you may have a lot of rows. Let me show you.

User
13/10/2018 - 04:55

yes I know

Excelchat Expert
13/10/2018 - 04:55

Look at this file.

[Uploaded an Excel file]

Excelchat Expert
13/10/2018 - 04:55

I did a pivot table and formulas to achieve your requirement.

Excelchat Expert
13/10/2018 - 04:55

On Sheet4.

Excelchat Expert
13/10/2018 - 04:56

The data I used was from Sheet1 which only contains 18 rows of data but once converted to your desired output it's now 187 rows.

Excelchat Expert
13/10/2018 - 04:57

It's basically number of rows (17) multiplied by number of dates (11). 11 x 17 = 187

User
13/10/2018 - 04:58

Since when is this a consolidation?

Excelchat Expert
13/10/2018 - 04:58

If you have 5,000 rows with 11 dates it will be 55,000 rows with this sheet alone.

User
13/10/2018 - 04:58

are you saying that the only way to do it is adding 1by1 (column items) in the pivot table?

Excelchat Expert
13/10/2018 - 05:00

Yes, if we don't use VBA. But, we haven't launched our VBA support yet.

Excelchat Expert
13/10/2018 - 05:00

This will be easy if we use VBA.

Excelchat Expert
13/10/2018 - 05:00

But since we're not yet supporting it, we'll stick with adding it 1 by 1 if we'll use pivot.

User
13/10/2018 - 05:01

this doesn't make sense

User
13/10/2018 - 05:01

I am sure you have other ways to do effectively without VBA

User
13/10/2018 - 05:02

if you don't know can you ask pl?

User
13/10/2018 - 05:02

I will work with multiple years data

User
13/10/2018 - 05:02

don't want to add every single date to the PTable!

Excelchat Expert
13/10/2018 - 05:02

I understand, let me create a formula for you.

User
13/10/2018 - 05:03

and also how can I build the summary PTable

User
13/10/2018 - 05:03

with the several tabs data

Excelchat Expert
13/10/2018 - 05:04

As mentioned earlier, we have a policy of 1 question per session. I hope you understand. I can only cover your data conversion from the table above to the table below.

User
13/10/2018 - 05:05

what you mean 1question per session

User
13/10/2018 - 05:05

?!

User
13/10/2018 - 05:05

this is the issue I want to solve

User
13/10/2018 - 05:05

I have just bought unlimed sessions!

Excelchat Expert
13/10/2018 - 05:06

I understand. The Unlimited plan allows for an unlimited number of sessions. Our policy helps to ensure that your individual problem is routed to the best Expert for that problem and that
it can be completed within the time limit. If you have multiple problems, just begin a new session for each one.

User
13/10/2018 - 05:07

does your next colleague gets this file you working on so that he works out of your data?

User
13/10/2018 - 05:07

this seems to be waste of my time

Excelchat Expert
13/10/2018 - 05:07

You can upload the file that I sent you to the next expert and post another question with specific details so it can be routed to the best expert of that question.

User
13/10/2018 - 05:08

this is not an effective way of doing what I requested!

User
13/10/2018 - 05:08

where are the consolidation pivot tables ?

Excelchat Expert
13/10/2018 - 05:08

I understand, I'm currently trying to find a different solution for you.

User
13/10/2018 - 05:08

Ok go ahead

Excelchat Expert
13/10/2018 - 05:12

Question for you, for the table below. Do you want it to be always sorted when you enter a new date?

User
13/10/2018 - 05:12

yes please

User
13/10/2018 - 05:12

descendent

User
13/10/2018 - 05:13

most recent above

User
13/10/2018 - 05:13

1st

Excelchat Expert
13/10/2018 - 05:18

The only way that I can think of to do this is by using array formulas but I wouldn't suggest it due to the size of your data set. Using array formulas with more than 100,000 rows will take a long time to calculate.

User
13/10/2018 - 05:19

can you just use it? Maybe it may be useful for me for downsized dta

User
13/10/2018 - 05:19

data sets

Excelchat Expert
13/10/2018 - 05:19

I will reach out to my supervisor and ask if we can do something with your requirement.

User
13/10/2018 - 05:19

great

User
13/10/2018 - 05:19

appreciated

Excelchat Expert
13/10/2018 - 05:19

Since you are subscribed with unlimited sessions.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*