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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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>
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top