Macro to Make A Pivot then group Cells by Value

[KingJohn]

Hey guys,

I have a full sheet of data that i've macro'd in to a pivot table. However, i cant work out how to then tell the macro to group Rows by a cell value that is below or equal to a few set values.

Here's a screenshot of what i'm trying to group:

The values 0.01, 0.09, 0.11 continue infinitely but rarely pass 161.99.
Here is what i'm trying to achieve:

The groups i'd like to put the values in are:
0.00 - 2.49 (Group 1 in diagram)
2.50 - 4.99 (group 2)
5.00 - 9.99 (group 3)
10.00 - 14.99 (group 4)
15.00 - 24.99 (group 5)
25.00 - 49.99 (Group 6)
50.00 + (not in above pic as no values above 49.99, but would presumably be group 7)

The groups dont need to be renamed, i'm just going to copy and past the data out of the pivot table into a final spreadsheet anyways.

Any idea's guys? i'm truly at a loss for this one.

any help appreciated.

 

[Andrew Poulsom]

It may be easier to add a column to your source data and use LOOKUP on a table to get the Groups. Then put that column in the row field in your pivot table.

 

[KingJohn]

Hi Andrew;

I've not used LOOKUPs before, but i think i know what they do, and there might be a snag;

This is what my raw data looks like:

Now the issue is, i think, that i'm only looking to display the data where the value in Column C is equal to "ERS" (ie in my last post this is shown in "Page" on the pivot), and when it is, i want to display the data from Column M (these are the values that make up the Row headings as in the pics in my previous post). The problem here is that the Column headings for Columns G and M (and in fact, 8 more columns) are identical. Would this hinder the LOOKUP you've suggested?

Could you be a gent and show me an example of what you would do in this scenario?

Many thanks

 

[Andrew Poulsom]

Make a table like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>0.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Group1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2.50</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>10.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>15.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>25.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>50.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Group7</TD></TR></TBODY></TABLE>

and name it Table using Insert|Name|Define. Then give row 1 of the first column to the right of your data a meaningful heading, in row 2 enter the formula:

=LOOKUP(M2,Table)

and copy down.

 

[KingJohn]

You are a gentleman and a scholar.

This is precisely what i needed, your idea was far more efficient than my initial plans. Now just some more fiddling to insert all headings even with zero results. easy enough

Thanks a tonne!