-2

I have the following data in a pivot table. To the left, is described a range and to the right the number of cells within that range. I would like to learn a code that correctly arranges the range in the left and color codes anything after "80-89". I have used the sort function but it doesn't seem to work. Please help.

Days Count of PR ID "10-19" 656 "0-9" 480 "20-29" 190 "30-39" 115 "40-49" 65 "50-59" 47 "70-79" 28 "60-69" 23 "80-89" 12 "110-119" 3 "120-129" 2 "130-139" 1 "100-109" 1 "180-189" 1

Community
  • 1
  • 1
Myriam Diaz Martinez
  • 131
  • 2
  • 10
  • Use a custom sort or a helper column. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days (you can use a vlookup to pull in the right rank). Then use this to sort on in your pivottable. Conditional formatting can then do your colouring as well. – QHarr Jun 03 '18 at 06:00

1 Answers1

0

Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days - you can use a vlookup to pull in the right rank for each days group. Then use this to sort on in your pivottable. Conditional formatting can then do your colouring as well based on the helper column value.

Data with helper column and vlookup table:

Data

Note that this would be your actual backing data before aggregating up that would have the helper column.

Pivot using helper column to order (note: you would use Count not Sum):

Pivot

Conditional formatting:

Add a conditional formatting rule (to meet your requirements) using the helper column value:

Helper column for conditional formatting

Result:

Sorted and conditional formatted

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Did you try this? – QHarr Jun 04 '18 at 06:41
  • @MyriamDiazMartinez Is doesn't need code. The full answer is shown. – QHarr Jun 06 '18 at 18:15
  • Yes, the answer is shown and will vote for it. I also needed the VBA code to add it to my MACRO. – Myriam Diaz Martinez Jun 06 '18 at 18:31
  • @MyriamDiazMartinez Thanks for getting back to me. There is no code given. I could have helped with that if I knew more specifically how you wanted this to slot in to any existing code. But yes, you can code with a custom sort order for example: https://stackoverflow.com/questions/6100944/code-an-excel-vba-sort-with-a-custom-order-and-a-value-containing-commas?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Jun 06 '18 at 18:34
  • Then to target the right part of the table for conditional formatting see https://peltiertech.com/referencing-pivot-table-ranges-in-vba/ And example of applying rules via vba https://stackoverflow.com/a/49548813/6241235 – QHarr Jun 06 '18 at 18:36