UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Possible To Pivot Data From An Array Of Values, Office 2010    
 
   
larai
post May 19 2017, 06:14 PM
Post#1



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hi,

I have a spreadsheet that contains a column titled "affected locations". This can contain an array of values or a single value. Is it possible to pivot and chart the count of the individual locations, whether the column contains an array or a single value?

DEVID Affected Locations
D-101 Alberta
D-102 Ontario, Charlotte
D-103 Alberta, Charlotte, GDL
D-104 GDL
D-105 Charlotte, MXI


In My pivot
Affected location Count of Affected Location
Alberta 2
Charlotte 3
GDL 2
MXI 1
Ontario 1


Thanks,
Lillianne
Go to the top of the page
 
dflak
post May 23 2017, 08:59 AM
Post#2


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


The easiest thing to do is to convert the data to an Excel Table. http://www.UtterAccess.com/wiki/Tables_in_Excel.

A more complicated method is to overlay the data with a named dynamic range: http://www.UtterAccess.com/wiki/Offset_and_Dynamic_Ranges.

Use either as the data source for the pivot table and the pivot table will always reference the exact amount of data it needs.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
larai
post May 26 2017, 05:10 PM
Post#3



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hi Dflak,

Sorry, my post may not have been clear in what I am trying to accomplish. My data is already in an Excel table. The data is coming from a database that allows array fields.

If I have multiple values in a single column, such as
"Alberta, Ontario, Montreal" in B2
"Ontario" in B3
"Alberta, Montreal" in B4
"Alberta, Vancouver" in B5

Is it possible to pivot and get a count of unique cities, such as
Alberta 3
Ontario 2
Montreal 2
Vancouver 1


Thanks.
Go to the top of the page
 
dflak
post May 30 2017, 08:44 AM
Post#4


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


The preferred solution would be to rewrite the query that delivers the data so it delivers it in normalized format.

If that can't be done, then we would have to expand the cities using text to columns and then take that data and normalize it. Then you can do almost anything you want with it with a pivot table.

Are you interested in such a solution? If so, attach a workbook with a dozen or so lines of sample data. The solution may be somewhat sensitive to the format of the data, so include all fields in the order they occur. Change names if you must to avoid revealing sensitive information.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 06:40 PM