Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc