Create Random Data List based on Existing Data

How to create a Random data list based on an existing Data list. Let say we have a Name list in Range (A1:A5).

 

Get Unique Values from Data Range - Part 2 (Using Function)

In our last post Get Unique Values from Data Range - Part 1 (Using default option), we have learn 'Remove Duplicate' & 'Advanced Filter' to get Unique records from a data range. Today we will learn two different function to get Unique Records from a range. We have same example, we have used in Part 1.

1. Using Function (Normal Function)

Step 1: Add a helping column & put this formula and drag it down "=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)". See below snap-shot:

Get Unique Values from Data Range - Part 1 (Using default option)

In this post, we will learn how to get Unique values from a Data range.  We will cover 2 ways in this post, next 2 ways we will cover in our next post Get Unique Values from Data Range - Part 2 (Using Function). As a bonus point, we will learn how to get Duplicate values as well in next post.

Scenario: We have a name list in Column A, we want to know how many duplicate values are there in the range. See below image:

Range (A1:A30)

How to remove Page Break lines

Today we will learn, how to remove page break lines today!

Scenario: Most of the time we mistakenly pressed wrong key and as a result page shows in page break view. Whether we change it to Normal view, still we see page break lines. Most of the people close the workbook and re-open it to remove page break view. We will learn how to remove these lines without closing the file.

1. Using Options

• Go to Option by Pressing Alt+T+O or Alt+F+T or File > Options
• Click on Advanced
• Scroll down and uncheck 'Show Page Breaks' under "Display Options for this Worksheet:" then Click OK. See below snapshot:

Fill blank cells in Excel with value in above cell

When we setup data in Excel, we generally leave blank if the same value repeating multiple times or we fetch data from other sources, data have some blank cells which should have values in the above cell. See below snapshot:

Raw Data

Now, we have to fill blank with the values in above cell e.g., Anu Gupta should be in Range(A2:A7), Delhi should be in Range(B2:B4) and so on.

For doing this, first of all we have to select all blank cells. We will do this step-by-step.

Step1. Select the range where you want to fill the data

Select the Range

Step2. Press F5 or Ctrl+G or Click on Home > Find & Select > Go to Special

Go to Special

How to Count Worksheets in a Workbook and its name

In this post, we will learn how to know how many worksheets are there in the workbook and those names.

Many of you have faced this problem sometimes, that you want to know how many sheets are there in the workbook. Few of them are hidden or very hidden as well. It is difficult to know such files as they are hidden/Very hidden. 

 

1. Using Name Manager

 

Step1.  Go to Name Manager (Press Ctrl+F3 then Click on New) or

Go to Formulas > Name Manager. See below snapshot:

Query3. Remove Special Characters from Name/String

Today we have received a Query: How to remove special characters from a Name or String. Will do the same by two ways.

We have a list, which contains special characters.

We will remove these special characters by 2 ways.

3 Easy ways to get First Name from Name!

Let's start with a simple example:

Here we have a name list.

We need First name as output in Column E.

1. Using Text-to-Columns

Step 1: First of all we will select the data, which we want to delimit.

Step 2: Then, go to Data tab in excel and select Text to Columns.

You will this pop-up window.

Find last lookup value

Today we will find last lookup value from a range.

We will work on this sales summary report

Scenario: Here we have to find last lookup value from the Sales Summary report.

Step 1: In cell L2, Type the formula:

=INDEX($G:$G,MAX(IF($B$3:$B$14=J2,ROW($B$3:$B$14))))

Query4. Ramji - Concatenate Unique assignments as per the Name

Concatenate unique assignments as per the Name!

Today Ramji asked a question: He has a table, in this table Column B has few names and Column A has their respective assignments. Now he wants the output as in Cell C1. See below image:

Query2. Find out Oldest Date based on a Condition

We have to find out oldest date with least time for a particular ID.

Scenario: We have a list which have some User IDs and their Sales time with dates. We have to find out oldest time for each User IDs which are highlighted.

See below image: I Have typed this formula:

"=MIN(IF($A$2:$A$13=A18,$D$2:$D$13))"

Press Ctrl+Shift+Enter, as this is an array function

The Sum, SumIf, SumIfs Functions

Sum | SumIf | SumIfs

In this post we will learn Sum function.

Sum

The Sum function sums the Numeric values. See below snapshot

The Count, CountIf & CountIfs Function

Count | CountIf | CountIfs

In this post we will learn Count functions.

Count

The Count function counts the range of cells or occurrence of a value which contains Numeric values. See below snapshot:

Mathematical Functions

Count | Sum | Average | Max | Min | Large | Small

1. Count/CountA/CountBlank

The Count function counts the Numeric values in a range. In below example Count function (Cell B10) returns the value 5, which is count of numeric values in range B1:B8.

The CountA function counts the Numeric & Alphabetical values in a range. CountA function (Cell D10) returns the value 7, which is count of numeric and alphabetical values in range D1:D8.

The CountBlank function counts the Blank cells in a range. CountBlank function (Cell F10) returns the value 1, which is count of blank cells in range F1:F8.

Query1. Add 1 Row Between Each Name

This is the Query asked by Naren today...

Query: This is the name list, we have to insert 1 row between each Name 

 

 

Text Functions

Concatenate | Find/Search | Left/Right/Mid | Proper/Lower/Upper | Trim | Len | Replace/Substitute

Concatenate

The concatenate function is used to join two or more words or text strings together. 

Syntax: =CONCATENATE(text1, [text2], ...)  

 

Same way we can use "&" operator to concatenate two or more text strings.

 Copy the formula from here : 

• =CONCATENATE(A2," ",B2)
• =A2&" "&B2

Note: " " = Blank

Vlookup for 1st,2nd,3rd,...Nth Value

Today we will find 1st,2nd,3rd,...Nth value from a range.

We will work on this sales summary report

Scenario: Here we have to find 2nd lookup value from the Sales Summary report.

Step 1: In cell M2, Type the formula:

=IFERROR(INDEX($G:$G,SMALL(IF($B$3:$B$14=K2,ROW($B$3:$B$14)),$M$1)),"Do not exist")