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.
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
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
3,760
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:

Forum statistics

Threads
1,081,708
Messages
5,360,777
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top