Solution to: http://excelexperts.com/budgeting

Solution to: http://excelexperts.com/comparing-data-sets

Thanks "Mr Spreadsheet" John Walkenbach for this formula.

When using particular macro often, it is handy to have it on right mouse-click. In this example there is a simple macro "Show Date and Time", and it was added to right mouse-click menu.

Supposing you have a macro called "DateAndTime" in a "Module1", copy code from attached .TXT file to the "Workbook" part in VBE.

With slight modification you can add more commands to the right mouse-click menu. It was explained in the comments.

If you want custom commands available on right mouse-click, regardless of the file you work on, copy the code to your "Personal" macro file.

An excellent way to compare data sets with the same column headings is to list them under each other, and add a new column called "source", and populate that source.

Then create a pivot table, and add source as the column field.

Then you can easily spot differences. Here's an example for comparing Trade PVs

I want formuls for retail pick qty form store ,,,
find out greatere qty avalable store name what we want

Regards
Bhagwat Tambe

Hi,

Please try this to transpose the vertical values to horizontal Values.

=IFERROR(OFFSET(INDIRECT("A"&SMALL(IF((ISBLANK($A$1:$A$35))*ROW($A$1:$A$35)>0,ROW($A$1:$A$35)),ROW($A1))),COLUMN(A1),0),"")

with CRTL+SHIFT+ENTER

Thanks

I'm trying to transpose (A) to (B), but every time I do this it just strings the text across one row. I need each address to to be read horizontally in a row with a blank cell between each address in the column. I have 1155 address in the column I don't want to transpose one at a time. I want to copy all 1155 address and have them transposed in the format I need. See (B) Format Needed

(A) Existing

WASHINGTON STATE PRODUCER
RICHARD ANDERSON
8067 SW GLENWOOD RD
PORT ORCHARD
WA
98367

(Blank Cell)

AMERICANNA NATURALS
HARRIS & MOURE
600 STEWART ST STE 1200
SEATTLE
WA
98101

This is an example based on post at: http://excelexperts.com/brackets-in-formulas

Bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[meals])" means "SUM of 'Meals' column", where "Meals" is a column within Data Table.

Maybe "Sum" cell was copied away from a Data Table, and that is why it looked strange.

This is solution to: "Hyperlink Macro that converts a list of sheet names to hyperlinks to the sheets", at: http://excelexperts.com/hyperlink-macro-converts-list-sheet-names-hyperl....

To create an index page with hyperlinks to all sheets in a workbook:

1. First, insert a new sheet and name it "Index"
2. In VBE (Alt+F11), select "Index" sheet and paste the code from the attached .txt file into the right pane:
3. Run Macro "CreateIndex"

Tips:

1. Tips & Tricks on How to reduce Excel file size?
2. Are you thinking why my Excel file size is so bigand how can I reduce it?
3. Don’t Worry…..Here you will find great Tips on Reducing Excel File size
4. Tip 1. Zip the Excel file
5. Tip 2: Save the Excel file in .xlsbformat
6. Tip 3. Remove unused cellsFollow below steps to remove unused cells1. Select any cell and press F5.2. Click Special-> Blank radio button.3. Go to Edit-> Clear All.
7. Tip 4. Remove formattingFollow below steps to remove formatting1. Open your Excel sheet.2. Press CTRL+A3. Click Remove Formats.

CELLS Property....

This property can be used as an alternative to the absolute range property and is generally more flexible to work with, as variables are easier to pass into it.
There are two optional arguments:
Cells([row] [,column])
Leaving the arguments empty (no brackets), it will detect the current selection as the active range.
Adding an argument to either row or column with a number will refer to the co-ordination of the number passed.
Adding both arguments will explicitly locate the single cell’s co-ordinate.
'Examples of the Cells property
Sub CellsExample()

Hi if you want to be an expert in excel formulas then learn Cell Referencing.....

Thanks

Sub MoveFilesFolder2Folder()
Dim fso
Dim sfol As String, dfol As String

sfol = "c:\MyFolder" ' change to match the source folder path
dfol = "e:\MyFolder" ' change to match the destination folder path

Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next

If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else

A bunch of tips for good programming.

1) First analyze the problem clearly

2) Next step is to think twice about how to solve that problem

3) Gather complete requirements. Take the time to write down what goals the end product needs to achieve, and who your user base will be. Clarity of thought at this stage will save a lot of time down the line.

4) Write a thorough implementation plan (or model).
For something small and self-contained, this might be just a basic flowchart or a simple equation.

http://mrexcel4u.blogspot.in/2014/01/consolidate-sub-folder-files-with-c...

Thanks & regards,

Solution to count before change at: http://excelexperts.com/data-counting

I guess that you want to count number of occurences of "Yes" and "No" in a block of consequtive occurences (i.e. before it changes from "Yes" to "No" or from "No" to "Yes"), right?

Having your data in column A, enter 1 in B2 and this formula in B3: =IF(A3=A2,B2+1,1).

Thus, when "Yes" turns into "No" or vice versa, counter resets to 1. If it is same as the row above, it increments counter to + 1.

Example file attached.

Please, let us know if this is what you need.

This is solution to request posted at: http://excelexperts.com/custom-date.

Hi,
1. Enter 2 Mar and 8 Mar 2014 into A1 and B1 as dates
2. Format them like: [$-409]Ddd d mmm yyyy"
2. Enter this formula in A2 and fill it down:

=PROPER(TEXT($A$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy")) &" to " & PROPER(TEXT($B$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy"))

Example file attached.

If you need a list of distinct/unique items from the range (column A) where items appear more than once, select a range of the same size (column C) and enter this as an array formula (press CTRL+SHIFT+ENTER):

=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:" & ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:" & ROWS(Data))))),"")

Where "Data" is a named range containing original list (column A).

Named range is not mandatory, but I recommend you create it, so formula is easier to handle.

Solution to How to display values of 2 variables which range from 4 to 15 in 1 message window using MsgBox? at: http://excelexperts.com/msgbox-question. This generates a pair of random numbers from defined range, so two numbers in a pair are always different. Thanks to J. E. McGimpsey for this code.

Check example file attached.

Regarding: "http://excelexperts.com/listbox-and-list" and: "http://excelexperts.com/more-macros-listbox-items": How change in listbox value calls a different macro.

Create ActiveX ListBox and create a list of macros, like in the example file attached.

Put this code into your worksheet part:

Private Sub ListBox1_Click()

If ListBox1 = "Macro1" Then
Call Macro1
End If

If ListBox1 = "Macro2" Then
Call Macro2
End If

If ListBox1 = "Macro3" Then
Call Macro3
End If

If ListBox1 = "Macro4" Then

i have to maintain accounts on excel like in the picture 1 there are accounts name like steel, cement, plinth, kesc, blocks i have total 40 accounts and want to enter data in single sheet then its automatically transfer data in others sheet on given criteria like cement entries moves in cement sheet like in picture 2 and steel entries move steel sheet.

i try advance filter but after insert new row in sheet then in another sheet did not update.

Please tell me how i maintain this type of accounts.

This is reply to the post: http://excelexperts.com/add-incremental-numbers

Having your column filtered, you probably insert incremental numbers in different column, right?
1. Filter your data by column A
2. Enter the first value in column B, for example: 100
2. Select the remaining cells in column B to be filled
3. Press F5 and choose "Special", select "Visible Cells Only"
4. In the first empty cell in column B type = (select cell with 100 entered ) + 1
5. Press CTRL+ENTER

You will get 100, 101, 102 etc. in column B, only in visible rows.

Screenshot attached.

If you need information on computer name, username logged on, Operating System , domain name, "Windows" folder, "Application Data" folder, number and type of processors or alike, this is for you. This macro lists all environment variables in column A of active sheet, starting from cell A1.

Example file attached.

I wish to have the following conditional formatting, but I am not able to correctly put the formula. Can anyone help me with this?

Cell F39:
Color code "Green" if F39 contains text "N/A" or "Yes"
Color code "Amber" if F39 contains text "No" & F38>=1<=50000
Color code "Red" if F39 contains text "No" & F38>50000

Cell F38:
Color code "Green" if F39 contains text "N/A" or "Yes"
Color code "Amber" if F39 contains text "No" & F38>=1<=50000
Color code "Red" if F39 contains text "No" & F38>50000

Cell F37:
Color code "Green" if F39 contains text "N/A" or "Yes"

Solution for: http://excelexperts.com/return-top-5-values-based-criterion

In brief: you need Category List re-arranged, additional column in "Average Rank" sheet and named ranges for newly inserted column and all months respectively.

In detail: I re-arranged Category List and made a new one on sheet "Category_New".
Then I inserted a column on sheet "Average Rank" containing type (Composite, Standalone etc.).
Named ranges were created for march and april 2013 (containing values).

Finally, array formula calculates Top 5. Example for February 2013 - "Composite":

You might get a list with names and surnames stored in the same cell, and you need to reverse their order (from "Name Surname" to "Surname Name"). Most common way to achieve this is through MID/LEFT/RIGHT functions or "Text to Columns" command. But that is only the first step. Next step is to concatenate cells in reverse order.

Here is a formula to get it at once, and it also works with two surnames and two names, separated by space (Caetano Gonzalez) or dash (Mary-Jane).

Here is a macro to insert image files into respective cells, according to image filenames or full filepaths.
File names/paths are stored in "D" column, and pictures are inserted in "A" column, in the same row.

If you have only the list of file names, use the first macro ("Short Name"). Change the macro so it refers to the folder where image files are stored.

If you have a list of image files as full path+filename, use the second macro ("Full Path").

I recommend using the second method.

Although there is a more elegant way created by Nick at: http://www.excelexperts.com/Compare-2-Lists, here is one more way.

Let's suppose there are two ranges with some common values. In order to find what values from one range exist in another range and vice versa, use the following formula in the cell beside A2 cell in the first range:

=IF(COUNTIF($D$2:$D$10;A2)>0;"Exists in Range Two";"Nope")