Re: Rearranging data in Excel
Barb,
Your effort was surely appreciated by many other community members.
I included your fine macro in my latest file:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
I count between 300 to 400 downloads a day.
Surely the browsers should be our main target.
Re: Rearranging data in Excel
Click on "Count of Rank" in the PivotTable and
change the setting to "Sum".
The same thing can be done in the PivotTable Field List
or by right clicking the PT and picking "Summarize Values by" "Sum".
The "1"s in Step 2 are OK.
Here is an automated version with VBA for the greater community:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Rearranging data in Excel
Excel 2010 PivotTables
No formulas, no code needed.
With detailed notes and pictures.
http://c3017412.r12.cf0.rackcdn.com/04_22_11b.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
http://www.mediafire.com/file/v4pmps7mhcccw9a/04_23_11.pdf
Re: Macro needed to transpose data
Excel 2010 PivotTable
No need to transpose
No formulas, no code used.
Method can be applied to earlier Excel versions.
http://c3017412.r12.cf0.rackcdn.com/04_22_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
http://www.mediafire.com/file/ax8yalntrgcg3lr/04_22_11.pdf
Re: Link two related tables accounting for changing associations in data
Excel 2010, Tables, PivotTables
Reconcile two dynamic, related Tables to each other.
With macro.
http://c3017412.r12.cf0.rackcdn.com/04_19_11a.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Look up nearest value with conditions
Excel 2010 Tables
Conditional nearest number.
http://c3017412.r12.cf0.rackcdn.com/04_12_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
Re: Advanced Formulas for Pivot Table
Excel 2010 PivotTable
Percentile
Two ways: With aux Tables and macro.
http://c3017412.r12.cf0.rackcdn.com/02_21_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Excel 2010 VBA table references & objects
Excel 2010 Tables
Macro with Structured References.
No R1C1, $A$1 or Defined Names.
http://c3017412.r12.cf0.rackcdn.com/04_08_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Time series duration and peaks
Excel 2010 (only)
Find peaks start, end, max level, max time
above a given level, lasting longer than a given time,
from a set of recording devices.
No code.
http://c3017412.r12.cf0.rackcdn.com/04_06_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
Re: column duplicates / merge data formula and/or macro???
Excel 2010 PivotTable, Tables
Pseudo text PT.
With macro, no formulas.
http://c3017412.r12.cf0.rackcdn.com/04_05_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Return Row value in a 2D array with column and value input
Excel 2010 Tables
Monotonicity is required.
http://c3017412.r12.cf0.rackcdn.com/04_01_11b.xlsx
Re: Vlookup with addition across multiple sheets
Excel 2010 PivotTable
Consolidate and sum BOMs on multiple sheets.
With macro.
Stepping thru the macro will illustrate a method without formulas or macro.
http://c3017412.r12.cf0.rackcdn.com/04_03_11a.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: VLookup across Multiple Sheets - OZGrid UDF not working
Excel 2010 (only)
Consolidate multiple tabs with PivotTable
With macro and SumProduct()
http://c3017412.r12.cf0.rackcdn.com/04_03_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: Return Row value in a 2D array with column and value input
Excel 2010 Tables
VLookup() next largest.
With kudos to Harlan Grove.
http://c3017412.r12.cf0.rackcdn.com/04_01_11a.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
Re: Use formula to combine data from several columns
Excel 2010 PivotTable
Added Date and Notes in RowField,
added sort macro, accommodated zero fields.
Other improvements are left as an exercise for the gentle reader.
http://c3017412.r12.cf0.rackcdn.com/03_28_11a.xlsm
Re: Perform Action on 2nd instance count of For Statement
N = WorksheetFunction.Even(N)
Re: Use formula to combine data from several columns
Excel 2007/2010 PivotTable
With macro, but no formulas.
Macro needs to be edited to your application.
Step through macro to see how it could be done manually.
http://c3017412.r12.cf0.rackcdn.com/03_28_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm
Re: how to make a scale-like plot of job positions
Excel 2010 Tables, Charts
Step curve and Gantt
http://c3017412.r12.cf0.rackcdn.com/03_24_11a.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
Re: Pivot table to count dates from two columns
Excel 2010 PivotTables
Count dates.
Same as Glenn's (nice work!)
but macro-nized.
http://c3017412.r12.cf0.rackcdn.com/02_07_11b.xlsm
Re: Pivot table to count dates from two columns
Excel 2010 PivotTable
Count dates.
Added notes and pictures.
http://c3017412.r12.cf0.rackcdn.com/02_07_11.xlsm
