URGENT: Pivot table from set with repeat data, blank cells

PT_AZ

New Member
Joined
Dec 14, 2018
Messages
1
Attached is a sample of the data set I am working with.

I am trying to figure out how to create a pivot table so I can easily filter and see numbers like:

-How many dogs vs cats were adopted
-Who adopted the most number pets
-Which city had most number of pets up for adoption
-Of those, how many they were adopted
City
Animal Up For AdoptionAnimal Up For Adoption 2Adopted?Owner
AustinDogCatYes, NoAdam
AustinCatYesSuzy
AustinDogCatYes, NoSuzy
AustinDogCatYes, YesJohn
AustinDogYesLisa
DallasCatNo
DallasDogCatYes, YesJack
DallasCatNo
El PasoCatNoDeborah
Fort WorthDogNo
Fort WorthDogYesNancy
Fort WorthDogNo
HoustonDogYesFred
San AntonioCatYesBarry
San AntonioDogYesBarry

<tbody>
</tbody>
My questions are:
1. How do I arrange the data I have in a way where I could create such a pivot table?
2. What is the best way to organize columns and rows?

I have looked up step by step tutorials, but I feel dumb because I can't figure it out and have to do so by Monday. I use Office 365.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
360
I feel like I am stating the obvious so please forgive me if I am misunderstanding. I think if you just drop the "Animal Up For Adoption 2" and move it to its own row it would allow you to create the pivot table.

By creating the table below it would allow you to create a pivot table based on location animal type adoption status and or owner.

CityAnimal Up For AdoptionAdopted?Owner
AustinDogYesAdam
AustinCatNoAdam
AustinCatYesSuzy
AustinDogYesSuzy
AustinCatNoSuzy
AustinDogYesJohn
AustinCatYesJohn
AustinDogYesLisa
DallasCatNo
DallasDogYesJack
DallasCatYesJack
DallasCatNo
Etc...
Etc...

<tbody>
</tbody>
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,379
1. How do I arrange the data I have in a way where I could create such a pivot table?
2. What is the best way to organize columns and rows?

I use Office 365.

with PowerQuery aka Get&Transform

CityAnimal Up For AdoptionAnimal Up For Adoption 2Adopted?OwnerQuery - Table1
AustinDogCatYes, NoAdamCityAnimal Up For AdoptionAdopted?Owner
AustinCatYesSuzyAustinDogYesAdam
AustinDogCatYes, NoSuzyAustinCatYesAdam
AustinDogCatYes, YesJohnAustinDogNoAdam
AustinDogYesLisaAustinCatNoAdam
DallasCatNoAustinCatYesSuzy
DallasDogCatYes, YesJackAustinDogYesSuzy
DallasCatNoAustinCatYesSuzy
El PasoCatNoDeborahAustinDogNoSuzy
Fort WorthDogNoAustinCatNoSuzy
Fort WorthDogYesNancyAustinDogYesJohn
Fort WorthDogNoAustinCatYesJohn
HoustonDogYesFredAustinDogYesJohn
San AntonioCatYesBarryAustinCatYesJohn
San AntonioDogYesBarryAustinDogYesLisa
DallasCatNo
DallasDogYesJack
DallasCatYesJack
DallasDogYesJack
DallasCatYesJack
DallasCatNo
El PasoCatNoDeborah
Fort WorthDogNo
Fort WorthDogYesNancy
Fort WorthDogNo
HoustonDogYesFred
San AntonioCatYesBarry
San AntonioDogYesBarry

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Adopted?"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Adopted?", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Adopted?"),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Animal Up For Adoption", "Animal Up For Adoption 2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Animal Up For Adoption.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Animal Up For Adoption.1", "Animal Up For Adoption"}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Animal Up For Adoption", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animal Up For Adoption"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([Animal Up For Adoption] <> ""))
in
    #"Filtered Rows"[/SIZE]

then insert PivotTable with Use an external data source (Query - Table1) and organize fields as you wish

btw. you don't need load QueryTable to the sheet here is just for example

example excel file
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,406
Messages
5,571,922
Members
412,426
Latest member
DeficientOptimism
Top