If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Friday, November 7, 2014

Using CountIf function along RANK.EQ for unique rank values

Using CountIf function along RANK.EQ to have unique rank values
RANK.EQ function
This function tells us the rank of a number in the list of numbers. If there are repetitive numbers in the list the top rank of that set of values is returned.
The below is the function syntax. Here number is the value whose rank we wanna know, ref is an array or reference of the list and order is option. 0 or omitted Order sorts in descending order and any other non-zero in ascending order.
RANK.EQ(number,ref,[order])

Marks
Rank
80
1
85
2
86
3
86
3
87
5
88
6
88
6
89
8


The function will give us rank in above way; we can see that for repetitive marks, the rank is top one and same.
RANK.EQ function combined with CountIF Function.
If we want unique rank results then by combining the Countif function, we can do that. Below is the formula.
=RANK.EQ(A2,$A$2:$A$9,1)+COUNTIF($A$2:$A2,A2)-1

Marks
Rank
Unique Ranks
80
1
1
85
2
2
86
3
3
86
3
4
87
5
5
88
6
6
88
6
7
89
8
8


Friday, October 10, 2014

Compare 2 lists, Easy Way (Excel2010) & a VB Macro

1)      Compare two lists, Easy Way (Excel 2010)
We have two lists/columns of data. Each column should have unique data, use Data->remove duplicate option to remove duplicates of each column.
Select both the lists/Columns
Go to Home Tab > Condition Formatting > Highlight cell rules> Duplicate values.
This will highlight the duplicates that are in both columns and selecting unique in the Bar’s dropdown will highlight the unique values.





2)      Macro to Find Matches between two columns and copying in third
Our data is in Column A and C, running this Macro will put the matches (which are in both columns A & C) in column B.
Sub Matches()
    Dim CRange As Variant, x As Variant, y As Variant
    Set CRange = Range("C1:C2500")
    For Each x In Selection
        For Each y In CRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub
   

Wednesday, September 24, 2014

Excel 2010: Fuzzy Lookup Add-In (Approximate Data Match)

Excel 2010: Fuzzy Lookup Add-In (Approximate Data Match)
We can use this Add-In for approximate data matching. Suppose we have two tables and there are spelling mistakes, abbreviations and short names used. So comparing such tables could be really a mess. But with this Ad-In we can do a lot, easily.
Download the Add-In: Download from the below link of Microsoft download center.
Once you have downloaded the Add-In, a Fuzzy Lookup tab would be visible on the Ribbon and that’s the command.



Our data should be in formatted tables, so If we want to compare the data of two tables, format them. For this select the data and press Ctrl L.
On the Ribbon click on Fuzzy Lookup command, this will open the pane/window.
Select the left and right tables to compare, if data of same table to be compared then select same as left, right.
The lookup will automatically join the same name columns; you can delete that join and add of your choice. For this select the column(s) (could be more than one) of both tables and click on the button which is in between the right & left columns window, this will add a join.
From the output columns, select the one which you want to see in results.
Leave the number of matches at the default of 1, or increase it to see more matches for each item in the left table.
Now Run the Lookup
For this, select a cell in an empty part of sheet, results will start populating from that cell. Click on Go button.
By Clicking on the configuration button, we can change the configuration as well to match our requirement.

Wednesday, September 17, 2014

Excel Macro: Highlight Every Other Row

Excel Macro: Highlight Every Other Row

Select the rows and run the below Macro (VB Code), this will highlight the every other row of your selection.
Saving Macro Code & assigning it to a button
For this first save the code, by going to Developer Tab and then clicking the Visual Basic. (If you don’t see Developer tab (Excel 2010) then go to the "File tab -> Options (under Help) -> Customize ribbon" and there tick the box of the Developer ribbon )
A new window will open now click on Insert > Module, paste the code there save and close the window. Now for putting a button in your sheet, go to Developer tab then Insert then Click on button (Form Control). Place it where you want.
Now right click on it and click on Assign Macro and assign this Highlight macro to that button. So now after you select your rows, clicking on this button will run the macro.

Sub Highlight()
Dim r As Integer
For r = 1 To Selection.Rows.Count
If r Mod 2 = 1 Then
Selection.Rows(r).Interior.ColorIndex = 37
End If
Next
End Sub



Tuesday, September 9, 2014

Unhide the very first column or row of a sheet

Unhide the very first column or row of a sheet
If the first row (Row1) or column (A Column) is hidden in the sheet then un-hiding it could be a bit tricky. The reason is that it’s hard to select that row or column. Though we can select the entire sheet and then unhide rows/columns but that will unhide all columns/rows that we might not want.
Below is an easy way mentioned to do this.
In the Name box which is next to formula bar, write A1 and then press enter button.
Now go to Home tab, in the cells group and Click on Format.
Now under Visibility, click Hide & unhide and then click on unhide rows or unhide columns.
This will unhide the column/row.


Monday, September 8, 2014

Lock Specified Area of a Protected Sheet

Lock Specified Area of a Protected Sheet
When we protect a sheet, all cells are locked by default for editing. For making editable all cells and leaving a few locked, we need to first unlock all cells and then lock specified cells before protecting a sheet.
v  If a sheet is already protected then unprotect it. (Review Tab)
v  Select the whole sheet by Selecting All Button or pressing Ctrl A
v  On the Home tab Click the Format Cells launcher, alternate way is through right click or Ctrl 1.    
v  Got to protection tab and clear the locked check box and click ok. This will unlock all the cells on the sheet, once protected.
v  In your worksheet select only those cells that you want to lock.
v  Again open Format Cells Launcher, same we did earlier.
v  On the protection tab, select the Locked check box and click Ok.
v  Got to Review tab and click Protect Sheet.
v  In the Allow users of this worksheet to list, select the components that we want user to be able to change.
v  In the password to unprotect sheet box, type a password for sheet. Please note the password step is optional, if we don’t provide then any user can unprotect by just clicking on unprotect option.





Thursday, September 4, 2014

Viewing two sheets of the same workbook side by side

Viewing two sheets of the same workbook side by side
ü  Go to View tab and in the Window group, click on New Window. This will open a copy of your workbook and also activate the View Side by Side option.

ü  Now click on View Side by Side. (View Tab & Window Group)

ü  Now in a Window you have two views side by side, click the worksheets that you want to compare i.e. sheet1 in first view and sheet2 in second view.

ü  To scroll both worksheets at the same time, click Synchronous Scrolling in the Window group on the View tab.


NOTE: This option is available only when View Side by Side is turned on.






To view two sheets next to each other or in a tiled layout
For this just stay in the View Tab and click on the Arrange All button. This will give multiple display options i.e. Tiles, Horizontal, Vertical & Cascade. Please try the one of your choice.

Tuesday, September 2, 2014

Interactive Image in Excel

We can make an interactive image in MS Excel. This is explained below step by step.
Interactive Image means, changing of image by changing data of a particular cell.
Suppose If I select or write person A’s name in cell then his/her picture should show up in the corresponding cell and if type the name of Person B then picture should change to of that person.

We will use the options of Camera, Data Validation & Define Name.
We will use the formulas of Vlookup, Indirect & Concatenate.

Add Camera Option to the Quick Access Toolbar
Go to File Menu bas (Excel 2010)
Click Options
Click/Select Quick Access Toolbar from left bar
Choose All Commands from the drop down for Choose Commands form
Select Camera option from the list under All Commands
Now Add the Camera option to the Quick Access Toolbar
Click Ok
Now, we can find the Camera option in the Quick Access Toolbar





Insert/Export the required Images to Excel
We should have the images with us on our system that we want to use in our Excel file. Please follow the below steps to insert images.
Click on Insert Button and then Picture
Locate the Picture one by one and insert in Excel
Re-size the width and height of all the pictures by dragging the edge of image.
The first picture should be in Cell A1 and the second in Cell A2
We need to ensure that image fit in one cell, by setting the column width & row height of the cell.
Rename the sheet as Image.
Now making the Interactive Images
Go to second/next sheet and name it as Interactive Image.
Go to Cell A1
Click on Data tab & then Data Validation.
Select List from the Allow drop down
Type Pizza & Bread in source box & Click OK
Now we have drop won option in Cell A1, select there the first one ‘Pizza’.




In the cell Y1 type Pizza & in Z1 1 and in Cell Y2 Bread & in Z2 2. We will use this data in our formula.



Use shortcut Ctrl+F3 (then name manager box appears)
Click New (then New Name window appears)
Type Name as "Image"
Enter the below formula in the Refers to box 

=INDIRECT("Image!A"&VLOOKUP('Interactive Image'!$A$1,'Interactive Image'!$Y$1:$Z$4,2,0))


Go to Cell C1
Click Camera option from the Quick Access Toolbar (then you get a cross symbol as cursor)
Drop that cross symbol somewhere in the visible Cell (then excel displays image showing G1 Cell content)
Change the formula to =image from =$C$1 (once you select the image you can find the formula in the formula bar)

Now check that changing the Input in Cell A1 (Where Data Validation was done) will change the image that we have placed in/near cell C1.