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: 968
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: 5,972
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 Yesterday, 05:10 PM
Post#3



Posts: 968
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.
This post has been edited by larai: Yesterday, 05:11 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 01:06 AM