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.