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

P

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 to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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]
 
Upvote 0
PT_AZ said:
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.
Click to expand...

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]

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
You must log in or register to reply here.

Similar threads

T
  • Question Question
Pivot Table - Conditional Formatting (or selecting) from a Data Table
Replies
0
Views
729
Timbucktoo
T
H
  • Question Question
Calculating avg. prices across specific hours of the year from a dataset using PivotTables?
Replies
0
Views
539
helnuma2k
H
X
  • Solved
Dashboard Objects are Randomly Resizing on a Data Refresh!
2
Replies
13
Views
4K
yrpsoa_
Y
O
  • Solved
Copy data set to another sheet and duplicate certain rows based on cell value from table
Replies
4
Views
886
onedayatatime
O
O
  • Solved
VBA for Data w/ Repeating Headers
Replies
9
Views
2K
jolivanes
jolivanes

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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