Pivot Tables Help

caaronh85 · Jun 21, 2021

Hello everyone, I'm looking for help on how to get specific data into a pivot table and I can't seem to get it right. Basically, when a date/s are selected the specific repairs and their corresponding quantities are displayed. The ID and specialist columns aren't necessary. I'd also like to be able to group the repairs and quantities so for each date selected it shows the totals of all 3 repairs for each date, ie; if 2 weeks of dates were selected on the pivot table, it would show all asic, thermal reflows, unrepairable, etc for those specific dates.

severynm · Jun 22, 2021

What is the significance of having three different columns for repair + quantity 1 through 3? Does that just mean you can have up to three repairs for each ID?

Generally it makes visualization much easier if you have one column for each measure. In this case you would have one column for repair and one column for quantity - ID Ref 75 would then take up two lines in your table, for example. If it would be important to filter out repair #2 vs repair #1, you can add a column called repair # (if you need to). Give that a go and let us know how that turns out.

caaronh85 · Jun 22, 2021

severynm said:
What is the significance of having three different columns for repair + quantity 1 through 3? Does that just mean you can have up to three repairs for each ID?

Generally it makes visualization much easier if you have one column for each measure. In this case you would have one column for repair and one column for quantity - ID Ref 75 would then take up two lines in your table, for example. If it would be important to filter out repair #2 vs repair #1, you can add a column called repair # (if you need to). Give that a go and let us know how that turns out.

Yes, an ID might, and usually does, have more than one repair. When I add more than 1 ‘Repair #’ to the table, it messes the whole table up. I might have to count them all and then add it but idk.

severynm · Jun 25, 2021

caaronh85 said:
When I add more than 1 ‘Repair #’ to the table, it messes the whole table up

What do you mean?

Radoslaw Poprawski · Jun 25, 2021

we cannot work on pictures, please provide minisheet or file link so we cancheck it ourselves.
also why don't you follow columnar data base solutions?

caaronh85 · Jun 26, 2021

Radoslaw Poprawski said:
we cannot work on pictures, please provide minisheet or file link so we cancheck it ourselves.
also why don't you follow columnar data base solutions?

Master Repair Metrics.xlsx

A

B

C

D

E

F

G

H

I

1

ID

Specialist

Repair 1

Quantity 1

Repair 2

Quantity 2

Repair 3

Quantity 3

Date

2

Ref 55

Dakota

Thermal Reflow

1

6/2/2021

3

Ref 74

Jonathan

Unrepairable

1

6/2/2021

4

Ref 75

Jonathan

Thermal Reflow

1

ASIC

1

6/2/2021

5

Ref 102

Dakota

Thermal Reflow

1

ASIC

1

6/2/2021

6

Ref 204

Dakota

Thermal Reflow

1

6/2/2021

7

Ref 203

Dakota

Thermal Reflow

1

6/2/2021

8

Ref 202

Dakota

Thermal Reflow

1

6/3/2021

9

Ref 205

Jonathan

Chip Reflow

1

6/3/2021

10

Ref 210

Jonathan

Unrepairable

1

6/3/2021

11

Ref 81

Dakota

Unrepairable

1

6/3/2021

12

Ref 215

Dakota

Unrepairable

1

6/3/2021

13

Ref 208

Dakota

Unrepairable

1

6/3/2021

14

Ref 200

Jonathan

Thermal Reflow

1

6/3/2021

15

Ref 221

Dakota

Unrepairable

1

6/3/2021

16

Ref 220

Dakota

Unrepairable

1

6/3/2021

17

Ref 219

Jonathan

Unrepairable

1

6/3/2021

18

Ref 217

Dakota

Unrepairable

1

6/3/2021

19

Ref 209

Dakota

Thermal Reflow

1

ASIC

3

6/3/2021

20

Ref 86

Dakota

Unrepairable

1

6/3/2021

21

Ref 63

Dakota

Unrepairable

1

6/3/2021

HB Repairs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A283	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2:A21	List	=$A$2:$A$1048576

Radoslaw Poprawski · Jun 26, 2021

it would be hell of a better way to follow columnar database solution
instead of having Repair #1 as a header it should be a column to which u specify repair #1 or #2 or #3
the for quantity, this will be way simpler to accomplish in pivot table

here try this PT:DataModel.xlsx

DataModel.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

1

ID

Specialist

Repair 1

Quantity 1

Repair 2

Quantity 2

Repair 3

Quantity 3

Date

ID

Sum of Quantity

2

Ref 55

Dakota

Thermal Reflow

1

06.02.2021

Ref 102

2

3

Ref 74

Jonathan

Unrepairable

1

06.02.2021

Ref 200

1

4

Ref 75

Jonathan

Thermal Reflow

1

ASIC

1

06.02.2021

Ref 202

1

5

Ref 102

Dakota

Thermal Reflow

1

ASIC

1

06.02.2021

Ref 203

1

6

Ref 204

Dakota

Thermal Reflow

1

06.02.2021

Ref 204

1

7

Ref 203

Dakota

Thermal Reflow

1

06.02.2021

Ref 205

1

8

Ref 202

Dakota

Thermal Reflow

1

06.03.2021

Ref 208

1

9

Ref 205

Jonathan

Chip Reflow

1

06.03.2021

Ref 209

4

10

Ref 210

Jonathan

Unrepairable

1

06.03.2021

Ref 210

1

11

Ref 81

Dakota

Unrepairable

1

06.03.2021

Ref 215

1

12

Ref 215

Dakota

Unrepairable

1

06.03.2021

Ref 217

1

13

Ref 208

Dakota

Unrepairable

1

06.03.2021

Ref 219

1

14

Ref 200

Jonathan

Thermal Reflow

1

06.03.2021

Ref 220

1

15

Ref 221

Dakota

Unrepairable

1

06.03.2021

Ref 221

1

16

Ref 220

Dakota

Unrepairable

1

06.03.2021

Ref 55

1

17

Ref 219

Jonathan

Unrepairable

1

06.03.2021

Ref 63

1

18

Ref 217

Dakota

Unrepairable

1

06.03.2021

Ref 74

1

19

Ref 209

Dakota

Thermal Reflow

1

ASIC

3

06.03.2021

Ref 75

2

20

Ref 86

Dakota

Unrepairable

1

06.03.2021

Ref 81

1

21

Ref 63

Dakota

Unrepairable

1

06.03.2021

Ref 86

1

22

Grand Total

25

23

Sheet1

PQ Code:

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Specialist","Date"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute], "Repair") then [Value] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if Text.Contains([Attribute], "Quantity") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom", "Repair"}, {"Custom.1", "Quantity"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Repair] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID", "Specialist", "Date", "Repair", "Quantity"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}, {"ID", type text}, {"Specialist", type text}, {"Repair", type text}, {"Quantity", Int64.Type}})
in
    #"Changed Type"

Result table is loaded str8 to data model.

caaronh85 · Jun 26, 2021

I

Radoslaw Poprawski said:
it would be hell of a better way to follow columnar database solution
instead of having Repair #1 as a header it should be a column to which u specify repair #1 or #2 or #3
the for quantity, this will be way simpler to accomplish in pivot table

I don't understand what you mean by 'columnar database solution'. My overall goal is to be able to select a date, or range of dates, and show all the specific repairs for the dates selected. The ID and specialist column isn't important to add. If you know a better way to accomplish this, I'm open to it.

Radoslaw Poprawski · Jun 26, 2021

you can download that file and see what I have done.
you also have office 365 which means if PT is not solution for you then you might want do sth like I proposed here:

Search and sum values in multiple workbooks for accounting

Dear Excel Gurus, I looked through the forums to try to find a solution for this problem but was not lucky. I'm trying to put together a search box that can loop through all the workbooks in a folder and all the subfolders and find all occurences of that search string and copy them over to a...

www.mrexcel.com

it does filtering data based on multiple criteria.
this also would be best if done on properly set up table.

caaronh85 · Jun 26, 2021

Th

Radoslaw Poprawski said:
it would be hell of a better way to follow columnar database solution
instead of having Repair #1 as a header it should be a column to which u specify repair #1 or #2 or #3
the for quantity, this will be way simpler to accomplish in pivot table

here try this PT:DataModel.xlsx

DataModel.xlsx
A B C D E F G H I J K L M
1 ID Specialist Repair 1 Quantity 1 Repair 2 Quantity 2 Repair 3 Quantity 3 Date ID Sum of Quantity
2 Ref 55 Dakota Thermal Reflow 1 06.02.2021 Ref 102 2
3 Ref 74 Jonathan Unrepairable 1 06.02.2021 Ref 200 1
4 Ref 75 Jonathan Thermal Reflow 1 ASIC 1 06.02.2021 Ref 202 1
5 Ref 102 Dakota Thermal Reflow 1 ASIC 1 06.02.2021 Ref 203 1
6 Ref 204 Dakota Thermal Reflow 1 06.02.2021 Ref 204 1
7 Ref 203 Dakota Thermal Reflow 1 06.02.2021 Ref 205 1
8 Ref 202 Dakota Thermal Reflow 1 06.03.2021 Ref 208 1
9 Ref 205 Jonathan Chip Reflow 1 06.03.2021 Ref 209 4
10 Ref 210 Jonathan Unrepairable 1 06.03.2021 Ref 210 1
11 Ref 81 Dakota Unrepairable 1 06.03.2021 Ref 215 1
12 Ref 215 Dakota Unrepairable 1 06.03.2021 Ref 217 1
13 Ref 208 Dakota Unrepairable 1 06.03.2021 Ref 219 1
14 Ref 200 Jonathan Thermal Reflow 1 06.03.2021 Ref 220 1
15 Ref 221 Dakota Unrepairable 1 06.03.2021 Ref 221 1
16 Ref 220 Dakota Unrepairable 1 06.03.2021 Ref 55 1
17 Ref 219 Jonathan Unrepairable 1 06.03.2021 Ref 63 1
18 Ref 217 Dakota Unrepairable 1 06.03.2021 Ref 74 1
19 Ref 209 Dakota Thermal Reflow 1 ASIC 3 06.03.2021 Ref 75 2
20 Ref 86 Dakota Unrepairable 1 06.03.2021 Ref 81 1
21 Ref 63 Dakota Unrepairable 1 06.03.2021 Ref 86 1
22 Grand Total 25
23
Sheet1

PQ Code:

Power Query:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Specialist","Date"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute], "Repair") then [Value] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if Text.Contains([Attribute], "Quantity") then [Value] else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Custom.1"}), #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom", "Repair"}, {"Custom.1", "Quantity"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Repair] <> null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID", "Specialist", "Date", "Repair", "Quantity"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}, {"ID", type text}, {"Specialist", type text}, {"Repair", type text}, {"Quantity", Int64.Type}}) in #"Changed Type"

Result table is loaded str8 to data model.

This works but it doesn't seem to update when new data is added?

Pivot Tables Help

caaronh85

New Member

Attachments

Excel Facts

severynm

Active Member

caaronh85

New Member

severynm

Active Member

Radoslaw Poprawski

Active Member

caaronh85

New Member

Radoslaw Poprawski

Active Member

caaronh85

New Member

Radoslaw Poprawski

Active Member

Search and sum values in multiple workbooks for accounting

caaronh85

New Member

Similar threads

Forum statistics

Share this page

Pivot Tables Help

New Member

Attachments

Excel Facts

Active Member

New Member

Active Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock