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

[PT_AZ]

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 Adoption	Animal Up For Adoption 2	Adopted?	Owner
Austin	Dog	Cat	Yes, No	Adam
Austin	Cat		Yes	Suzy
Austin	Dog	Cat	Yes, No	Suzy
Austin	Dog	Cat	Yes, Yes	John
Austin	Dog		Yes	Lisa
Dallas	Cat		No
Dallas	Dog	Cat	Yes, Yes	Jack
Dallas	Cat		No
El Paso	Cat		No	Deborah
Fort Worth	Dog		No
Fort Worth	Dog		Yes	Nancy
Fort Worth	Dog		No
Houston	Dog		Yes	Fred
San Antonio	Cat		Yes	Barry
San Antonio	Dog		Yes	Barry

<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]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City[/TD]
[TD]Animal Up For Adoption[/TD]
[TD]Adopted?[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Dog[/TD]
[TD]Yes[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Cat[/TD]
[TD]No[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Cat[/TD]
[TD]Yes[/TD]
[TD]Suzy[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Dog[/TD]
[TD]Yes[/TD]
[TD]Suzy[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Cat[/TD]
[TD]No[/TD]
[TD]Suzy[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Dog[/TD]
[TD]Yes[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Cat[/TD]
[TD]Yes[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Dog[/TD]
[TD]Yes[/TD]
[TD]Lisa[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]Cat[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]Dog[/TD]
[TD]Yes[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]Cat[/TD]
[TD]Yes[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]Cat[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]City[/td][td=bgcolor:#5B9BD5]Animal Up For Adoption[/td][td=bgcolor:#5B9BD5]Animal Up For Adoption 2[/td][td=bgcolor:#5B9BD5]Adopted?[/td][td=bgcolor:#5B9BD5]Owner[/td][td][/td][td]Query - Table1[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Austin[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7]Cat[/td][td=bgcolor:#DDEBF7]Yes, No[/td][td=bgcolor:#DDEBF7]Adam[/td][td][/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]Animal Up For Adoption[/td][td=bgcolor:#70AD47]Adopted?[/td][td=bgcolor:#70AD47]Owner[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Austin[/td][td]Cat[/td][td][/td][td]Yes[/td][td]Suzy[/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Dog[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Adam[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Austin[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7]Cat[/td][td=bgcolor:#DDEBF7]Yes, No[/td][td=bgcolor:#DDEBF7]Suzy[/td][td][/td][td]Austin[/td][td]Cat[/td][td]Yes[/td][td]Adam[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Austin[/td][td]Dog[/td][td]Cat[/td][td]Yes, Yes[/td][td]John[/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Dog[/td][td=bgcolor:#E2EFDA]No[/td][td=bgcolor:#E2EFDA]Adam[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Austin[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Lisa[/td][td][/td][td]Austin[/td][td]Cat[/td][td]No[/td][td]Adam[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dallas[/td][td]Cat[/td][td][/td][td]No[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Suzy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Dallas[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7]Cat[/td][td=bgcolor:#DDEBF7]Yes, Yes[/td][td=bgcolor:#DDEBF7]Jack[/td][td][/td][td]Austin[/td][td]Dog[/td][td]Yes[/td][td]Suzy[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dallas[/td][td]Cat[/td][td][/td][td]No[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Suzy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]El Paso[/td][td=bgcolor:#DDEBF7]Cat[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]Deborah[/td][td][/td][td]Austin[/td][td]Dog[/td][td]No[/td][td]Suzy[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Fort Worth[/td][td]Dog[/td][td][/td][td]No[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]No[/td][td=bgcolor:#E2EFDA]Suzy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Fort Worth[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Nancy[/td][td][/td][td]Austin[/td][td]Dog[/td][td]Yes[/td][td]John[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Fort Worth[/td][td]Dog[/td][td][/td][td]No[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]John[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Houston[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Fred[/td][td][/td][td]Austin[/td][td]Dog[/td][td]Yes[/td][td]John[/td][/tr]

[tr=bgcolor:#FFFFFF][td]San Antonio[/td][td]Cat[/td][td][/td][td]Yes[/td][td]Barry[/td][td][/td][td=bgcolor:#E2EFDA]Austin[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]John[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]San Antonio[/td][td=bgcolor:#DDEBF7]Dog[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Barry[/td][td][/td][td]Austin[/td][td]Dog[/td][td]Yes[/td][td]Lisa[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Dallas[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]No[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Dallas[/td][td]Dog[/td][td]Yes[/td][td]Jack[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Dallas[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Jack[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Dallas[/td][td]Dog[/td][td]Yes[/td][td]Jack[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Dallas[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Jack[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Dallas[/td][td]Cat[/td][td]No[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]El Paso[/td][td=bgcolor:#E2EFDA]Cat[/td][td=bgcolor:#E2EFDA]No[/td][td=bgcolor:#E2EFDA]Deborah[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Fort Worth[/td][td]Dog[/td][td]No[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Fort Worth[/td][td=bgcolor:#E2EFDA]Dog[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Nancy[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Fort Worth[/td][td]Dog[/td][td]No[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Houston[/td][td=bgcolor:#E2EFDA]Dog[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Fred[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]San Antonio[/td][td]Cat[/td][td]Yes[/td][td]Barry[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]San Antonio[/td][td=bgcolor:#E2EFDA]Dog[/td][td=bgcolor:#E2EFDA]Yes[/td][td=bgcolor:#E2EFDA]Barry[/td][/tr]
[/table]

[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