Multiple replace 1

[LGMan]

I have a routine that utilises a pivot table to sum weekly spend against expense codes. I'm only interesed in 4 codes out of an ever increasing list of 100+. Whenever a new code gets created, I have to manually de-select the new codes from my pivot table. Not a great hardship I hear you say but annoying none the less.
Whilst thinking of ways around it, I wondered if I could perform a replace process that checked each expense code. If the active cell doesn't contain any of the 4 codes that I want it could replace the contents with a text string of 'Not Needed'. The end result would mean that the pivot table would never need to be amended no matter how many new codes are created.

Is such a thing possible, my experience is limited to replacing a single specific with another.

 

[ItIsHardToProgram]

You could easily automate a deselection / selection in pivot table, excel sees your "deselection" as .visible = false and selection as the opposite.

Im not sure why you want to replace content with "Not Needed" as this would inevitably delete the information in your record.

I would like you to explain somethings to be more specific:

[blue]1st: When new expense codes are created, are the older expense codes needed.

2ndly: Wich of the expense codes are required, the new ones or the older ones.

3rd: Do you need to keep archive of the older codes, even if they aren't required?[/blue]

Please explain your process more explicitly

Cordialy,

Julien Roy


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[LGMan]

Thanks for the reply.
to answer your questions

I only need 4 specific 'old' codes, I'm not interested in new codes. therefore I don't really need to keep any of the rows of data for those new codes since none of should figure in the pivot results.

 

[SkipVought]

Hi,

Make a table of the codes of interest. NAME the range.

Use the MACTH function and the ISNA function in a helper column in your source data, so that any row containing codes in your list returns a values like 1. Be sure to add a heading for this column.

Use this column as a PAGE FIELD in your PT.

VOLA, y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

I had not thought about the automation of the pivot table, must admit that I could do this on several tables.

The trusted record a macro and work out from there does not work for Pivot selections so am a bit stuck. Also am not finding the help very usefull probably becuase I don't know exactly where to look.
My pivot table is called Expense_pivot and the field that i want to restict to my 4 codes is called expense_code.

any help greatly appreciated.

 

[SkipVought]

Dod you try the helper column and the PAGE FIELD?

Works EVERY time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I have a routine that utilises a pivot table to sum weekly spend against expense codes[/code]

I only need 4 specific 'old' codes, I'm not interested in new codes. therefore I don't really need to keep any of the rows of data for those new codes since none of should figure in the pivot results.

Why sum up all the codes if you only need 4 specific ones each week ?

You could simply not sum up all the weekly spent that don't match your specific criteria.

By the way, Skip's suggestion is the easiest and cleanest way to do what you want to do, without changing anything in your process.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[LGMan]

hmm, I haven't tried Skips solution on account of not really understanding the match function. My data is not that big so calculating what I don't need doesn't really matter.

 

[ItIsHardToProgram]

It mathers since you calulate what you don't need and then remove it. It simply makes no sense.

skip's suggestion is using the MATCH function and ISNA function imbedded in excel.

If you need any help on that function it is available in alot of FAQ's and on microsoft's site.

You could also press fx next to the = sign, and simlpy look it up there, its prety clear how to use.

Cheers! work it out a bit and report back to him­ if you have any problems, don't forget to add a "star" if he helped you out with your problem.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

BTW, my suggestion is a simple SPREADSHEET solution -- no VBA rewuired.

1. Set up table sithe named range - approx 20 seconds

2. Write formula in adjacent helper column - approx 30 seconds

3. Use PivotTable Wizard to gen new PivotTable - less than 2 minutes

4. Set the PAGE FIELD to display values in your table - approx 5 seconds

So, in less than 5 minutes, you could have a working, repeatable solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks,
couldn't follow the Match thing to what I wanted, managed to get to desired end result by using vlookup to the helper table.

 

[SkipVought]

Post your VLOOKUP and I'll show you how MATCH might work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Skip,
for simplicity I cell D2 I have...
'=VLOOKUP(A2,G2:H5,2,FALSE)'

Cell A2 contains the code '1102' while my helper table in cells G2 and H2 both contain '1102'
The other codes that I'm interested in are 1103, 1104 and 1105 which complete the helper table.

I Then use filldown to populate the cells in Col D until the bottom say D2398, so Col D values are either one of my 4 codes or '#N/A'. The pivot table 'Page by' then filters out the '#N/A'

 

[SkipVought]

[tt]
=IF(ISNA(MATCH(A2,G2:G5,0)),0,1)
[/tt]
Select 1 in the page field.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

skip, this just puts a zero in all cells in col D, have i missed something?

 

[SkipVought]

EXACT Match the value in column A with the values in G2:G5.

No Match 0

Match 1.

Make sure your calculate.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OOPS...

I RARELY use range references.

Rather use NAMED RANGES!!!

With Range Ref...
[tt]
=IF(ISNA(MATCH(A2,$G$2:$G$5,0)),0,1)
[/tt]
As I previously posted,

Make a table of the codes of interest. NAME the range.

So if your column G range were named MyFavoriteValues then
[tt]
=IF(ISNA(MATCH(A2,MyFavoriteValues,0)),0,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}