# Trending February 2024 # Excel Sorted Dynamic Unique List # Suggested March 2024 # Top 8 Popular

You are reading the article Excel Sorted Dynamic Unique List updated in February 2024 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Excel Sorted Dynamic Unique List

I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but I didn’t want to brag 😉

It has never been so easy to extract a unique or distinct list of values in Excel than it is now that we have Dynamic Array formulas*.

*Dynamic Array formulas are only available in Office 365. I’ll provide links to an alternative for Excel 2023 and earlier versions at the bottom of this post.

Note: At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. We don’t have an ETA for when they will be available to all Office 365 users yet. And to be clear, Excel 2023 does not come with Dynamic Arrays. The only way to get them is with Office 365 …or wait until Excel 2023 (?) comes out.

Excel Sorted Dynamic Unique List Formula

The formula for extracting a list of sorted unique values that ignore errors and blanks is super easy.

Step 1: Format the source data in an Excel Table. That way when new rows are added, or rows removed, the formula will automatically pick up the changes.

Step 2: The formula.

The most complicated part of the formula, which isn’t really that complicated, is the FILTER function, that enables us to return the list excluding errors and blank cells, among other things. The UNIQUE and SORT functions then enclose FILTER.

The FILTER function takes the following arguments:

=FILTER(array , include, [if_empty])

The array argument is the table or range of cells you want to filter.

The include argument allows you to insert a logical test specifying which values to include.

The if_empty argument is an optional value to return if there are no records that match our ‘include’ criteria. We don’t need it in this example.

The formula is:

=

SORT(

UNIQUE(

FILTER(Table1[Names]

,

NOT(ISBLANK(Table1[Names]))

*

NOT(ISERROR(Table1[Names]))

)

)

)

FILTER the Names column of Table1 and return a list of values, where the Names are not blank AND the Names are not errors.

The NOT(ISBLANK(Table1[Names])) and NOT(ISERROR(Table1[Names])) formulas return a list of TRUE and FALSE Boolean values as shown below:

=SORT( UNIQUE( FILTER(Table1[Names], {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}* {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}) ) )

The FALSE values are discarded before passing the list to the UNIQUE function. The UNIQUE function then removes the duplicate names before passing the list to the SORT function, which sorts it in ascending order.

If you prefer the list sorted in descending order you can add  ‘,,-1’ to the SORT formula like so:

=SORT( UNIQUE( FILTER(Table1[Names], NOT(ISBLANK(Table1[Names]))*NOT(ISERROR(Table1[Names])) ) )

, ,-1

)

Dynamically Update

And because I formatted the source for my Excel Sorted Dynamic Unique list in an Excel Table, when I add new names it automatically updates:

Notice how the results automatically ‘spill’ to the cells below? This is the new Dynamic Array formula functionality available to Office 365 users.

Unique Lists in Earlier Versions of Excel

If you’re not fortunate enough to have Office 365 and these handy dynamic array formulas, you can use one of the techniques described here.

Related Lessons

SORT Function – there’s more to the SORT function than I’ve demonstrated here. You can also sort multiple columns and choose which column to sort by.

UNIQUE Function – the UNIQUE function can also handle multiple columns and differentiate between unique and distinct values.

FILTER Function  - you can filter more than one column and FILTER can handle OR criteria as well as AND criteria.

NOT Function – Not enables us to check if a logical test doesn’t exist.

ISBLANK Function and ISERROR Function – the IS functions check to see if a condition exists and return a TRUE FALSE, depending on the outcome. There are more IS functions at the link above.

You're reading Excel Sorted Dynamic Unique List

## Excel Vlookup – Sorted List Explained

In my previous Excel VLOOKUP formula tutorial I mentioned that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both.

As promised here’s the second way to use it, and I call it the Sorted List version as it relies on the data in the table you are referencing being sorted.

Note: If you haven’t read the first tutorial, then I recommend you first watch the video below from the beginning to get an understanding of how VLOOKUP works.

Excel VLOOKUP Formula Video Tutorial

First let’s set the scene:

In the image below we want to lookup the Commission Rates table in cells G6:I13, and find the rate in column I based on the sales values in column D, and return the result to column E.

Excel VLOOKUP Function syntax:

=VLOOKUP(

lookup_value

,

table_array

,

col_index_num,

range_lookup

)

And to translate it into English it would read:

=VLOOKUP(

find this value

,

in that table

,

return the value in the nth column of the table

,

find an exact match if you can, but if not, find the next lowest match

)

Note: with the Sorted List version we want Excel to find the next closest option in our table, i.e. an approximate match. To specify this we can leave the ‘range_lookup’ argument blank, or enter TRUE, or 1.

Excel VLOOKUP approximate match formula example:

Remember we want Excel to find the Commission % Rate and enter it in cell E6, so in English our formula will read:

=VLOOKUP(

find where the Sales amount \$3,112

,

falls in the Commission Rates table G6:I13

,

return the value in column 3 of the table

,

if there isn't an exact match, find the next closest value

)

=VLOOKUP(

D6

,

\$G\$6:\$I\$13

,

3

,

TRUE

)

Let me clarify some points:

1)      ‘find where Sales amount \$3,112, falls in the Commission Rates table’ – Excel doesn’t actually take into consideration column H in our table. I have simply put it there to help understand the commission ranges. Excel is in fact looking for the exact amount \$3,112 in our Commission Rates table, and when it can’t find it, it finds the next best lower amount and returns the value in column 3.

3)      If we had consecutive duplicates in our Commission Rates table Excel will find the last instance of the value and return the result in column 3.  For example, if instead of the amount \$4001 in cell G11, you had \$3001 again.  Excel would return the value of 6% as it’s finding the last best match for our amount.  The tip here is to remove any duplicates or you’ll end up with erroneous results.

4)      Unlike the VLOOKUP Exact Match version of the formula, this version requires the list to be sorted in ascending order. Just like with duplicates explained above, if it’s not sorted you will end up with erroneous results.

You’ll notice in the formula bar above there are ‘\$’ signs around the reference to the table. This is called an absolute reference and it allows us to copy the formula down column E without Excel dynamically updating the table range as we copy.

Want More?

Check out my previous tutorial for VLOOKUP Rules & Common Mistakes!

## Python Program To Print Unique Values From A List

List is a built-in data structure in python which functions like a dynamically sized array. Lists are created by putting elements in square brackets ‘[element 1, element 2,….]’. Elements present in list are indexed and the indexing starts from [0].

List has the following properties −

List is mutable meaning that elements can be added, removed or changed from a list after its creation.

List is ordered, so adding new elements to the list will not change the order of existing elements.

A single list can contain multiple data types.

Now, let us see various ways to print unique values from a list using python program −

Using set() method to get unique values

In python the set() method stores only a single copy of all elements present in it. When a list with duplicate elements is converted to set, only a single copy of the duplicate values is stored. The set can then be converted back to a list to create a list with only unique values.

Example

Following is an example of set() method to print unique values from a list −

unique_numbers_set

=

set

(

duplicate

)

unique_numbers_list

=

list

(

unique_numbers_set

)

return

unique_numbers_list duplicate_list

=

[

1

,

2

,

3

,

4

,

1

,

2

,

5

,

6

,

6

,

6

]

print

(

unique_numbers

(

duplicate_list

)

)

Output

Following is an output of the above code −s

[1, 2, 3, 4, 5, 6] Using list.append() with for loop to get unique values

The append() method in python is used to add elements to the last index of the list. First an empty list is created to store the unique elements of the list containing duplicate values.

A for loop is used with an if condition to check if element at ith position is present in the empty list. If the element is not present, then it is appended to the list. Hence, only unique elements are present because if duplicate is encountered the if condition fails and the duplicate element is not appended.

Example

Following is an example of list.append() with for loop to get unique values from a list −

unique_fruit

=

[

]

for

traverse

in

fruit

:

if

traverse

not

in

unique_fruit

:

unique_fruit

.

append

(

traverse

)

return

unique_fruit

fruits

=

[

‘apple’

,

‘orange’

,

‘mango’

,

‘apple’

,

‘orange’

,

‘mango’

]

print

(

unique_fruits

(

fruits

)

)

Output

Following is an output of he above code

['apple', 'orange', 'mango'] Using numpy.unique() method to get unique values

The numpy.unique() method is a built-in method of NumPy library that is used to return unique values from an array in sorted order. After converting the duplicate list to an array, the unique() method is used to remove duplicates and then the array can be converted back to a list.

Example

Following is an example of numpy.unique() method to get unique values from a list −

def

unique

(

duplicate

)

:

array

=

np

.

array

(

duplicate

)

unique_elements

=

np

.

unique

(

array

)

sorted_unique_list

=

list

(

unique_elements

)

return

sorted_unique_list

duplicate_list

=

[

8

,

4

,

2

,

4

,

2

,

8

,

6

,

6

]

print

(

unique

(

duplicate_list

)

)

Output

Following is an output of the above code −

[2, 4, 6, 8]

## How To Create A Dynamic Chart Range In Excel

When you create a chart in Excel and the source data changes, you need to update the chart’s data source to make sure it reflects the new data.

In case you work with charts that are frequently updated, it’s better to create a dynamic chart range.

A dynamic chart range is a data range that updates automatically when you change the data source.

Below is an example of a chart that uses a dynamic chart range.

Note that the chart updates with the new data points for May and June as soon as the data in entered.

There are two ways to create a dynamic chart range in Excel:

Using Excel Table

Using Formulas

In most of the cases, using Excel Table is the best way to create dynamic ranges in Excel.

Let’s see how each of these methods work.

Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it.

Excel Table feature was introduced in Excel 2007 version of Windows and if you’re versions prior to it, you won’t be able to use it (see the next section on creating dynamic chart range using formulas).

Pro Tip: To convert a range of cells to an Excel Table, select the cells and use the

To convert a range of cells to an Excel Table, select the cells and use the keyboard shortcut – Control + T (hold the Control key and press the T key).

In the example below, you can see that as soon as I add new data, the Excel Table expands to include this data as a part of the table (note that the border and formatting expand to include it in the table).

Now, we need to use this Excel table while creating the charts.

Here are the exact steps to create a dynamic line chart using the Excel table:

Select the entire Excel table.

Go to the Insert tab.

In the Charts Group, select ‘Line with Markers’ chart.

That’s it!

The above steps would insert a line chart which would automatically update when you add more data to the Excel table.

Note that while adding new data automatically updates the chart, deleting data would not completely remove the data points. For example, if you remove 2 data points, the chart will show some empty space on the right. To correct this, drag the blue mark at the bottom right of the Excel table to remove the deleted data points from the table (as shown below).

While I have taken the example of a line chart, you can also create other chart types such as column/bar charts using this technique.

As I mentioned, using Excel table is the best way to create dynamic chart ranges.

However, if you can’t use Excel table for some reason (possibly if you are using Excel 2003), there is another (slightly complicated) way to create dynamic chart ranges using Excel formulas and named ranges.

Suppose you have the data set as shown below:

To create a dynamic chart range from this data, we need to:

Create two dynamic named ranges using the OFFSET formula (one each for ‘Values’ and ‘Months’ column). Adding/deleting a data point would automatically update these named ranges.

Insert a chart that uses the named ranges as a data source.

Let me explain each step in detail now.

Below are the steps to create dynamic named ranges:

Go to the ‘Formulas’ Tab.

The above steps have created two named ranges in the Workbook – ChartValue and ChartMonth (these refer to the values and months range in the data set respectively).

If you go and update the value column by adding one more data point, the ChartValue named range would now automatically update to show the additional data point in it.

The magic is done by the OFFSET function here.

In the ‘ChartValue’ named range formula, we have specified B2 as the reference point. OFFSET formula starts there and extends to cover all the filled cells in the column.

The Same logic works in the ChartMonth named range formula as well.

Now all you need to do is insert a chart that will use the named ranges as the data source.

Here are the steps to insert a chart and use dynamic chart ranges:

Go to the Insert tab.

With the chart selected, go to the Design tab.

In the Series value field, enter

=Formula!ChartValues

(note that you need to specify the worksheet name before the named range for this to work).

In the ‘Axis Labels’ dialog box, enter

=Formula!ChartMonths

That’s it! Now your chart is using a dynamic range and will update when you add/delete data points in the chart.

A few important things to know when using named ranges with charts:

There should not be any blank cells in the chart data. If there is a blank, named range would not refer to the correct dataset (as the total count would lead to it referring to less number of cells).

You need to follow the naming convention when using the sheet name in chart source. For example, if the sheet name is a single word, such as Formula, then you can use =Formula!ChartValue. But if there is more than one word, such as Formula Chart, then you need to use =’Formula Chart’!ChartValue.

You May Also Like the Following Excel Tutorials:

## How Does Mongodb Unique Works With Examples

Introduction to MongoDB Unique

MongoDB’s Unique Constraint makes certain that the fields indexed in it, do not store duplicate values for the same field, i.e., making sure the uniqueness of fields. By default, MongoDB enforces this unique constraint on the “_id” field, while inserting new data. One of the major responsibilities is to ensure no duplicate data has been stored in a single key. We also have restrictions here, like MongoDB will be unable to establish a unique index on any specific field, in case if the collection somehow has already stored any data that would breach the feature of unique constraint for the index.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Now that we have understood what Unique Constraint in MongoDB, let us learn the standard syntax to write this Unique method.

db.collection_name.createIndex( {field_name : 1} , {unqiue : true} )

How UNIQUE Constraint works in MongoDB?

The Unique Index allows the insertion and storing of the values in a document into a collection IF there is no other document in the same collection with the same index key and value. The unique constraint can be implemented on compound indexes. This helps MongoDB to implement uniqueness when combining the index key values.  Unique Constraint over separate multiple documents is possible in MongoDB, and it is an essential function in avoiding storing of same values for the indexed key in a different document.

Unique Constraint in MongoDB will allow only a single document with the same value for the indexed key. If we attempt to insert the same value for a single indexed key, it will result in an error.

Query #1

db.code.find()

This returns every document in the collection.

Query #2

db.code.insert( { name:”kumar”, city:“Pune”, code:19 } )

This is an attempt to insert a record with the same code.

Code:

db.code.find()

Output:

Explanation: Here, we have implemented the unique index for “code” key, and so when we try to store a document with existing code, it results in “duplicate key error”.

Unique Constraint for Null Value

When we store any document, we store values for the respective keys, and it is stored as per indexing. But in case, if we attempt to store a document that has no specific value or data for a key, for any uncertain key, the document will be stored, and the empty value will be stored as a null value in that document.  In MongoDB, only on the document is allowed to be stored with a single index field missing.

Examples to Implement UNIQUE Constraint in MongoDB

Implementing Unique Contraint on separate documents to not store the same value for the indexes key, in two different documents.

Example #1

We will attempt to insert and store documents with duplicate values. We have a collection named educba, which we will check with find method: db.educba.find()

Code:

db.educba.find()

Output:

As you can see in the above image, we have a collection named educba, and the document has one default unique “_id” and two more keys. We will now implement the unique constraint on the “Name”, which will make it unique, duplicate insertion proof.

Code:

db.educba.createIndex( { “Name” : 1 } , { unique : true } )

Output:

Code:

db.educba.insert({ Name : "Sulaksh" ,  City : "Pune" })

Output:

First Query:db.educba.insert({ Name : "Sulaksh" ,  City : "Pune" })

The above query will be successfully inserted.  Then we attempted to insert the same query, i.e. same document with the same values, but it resulted in an error that states, “terms”: “E11000 duplicate key error collection: test.educba index: Name_1 dup key: { : ”Sulaksh” }””. This makes our unique constraint applied over the Name field successfully.

Example #2

Code:

db.educba.createIndex( {email : 1},{unique : true} )

Output:

We will not attempt to insert a document with a different name but an email id that already exists in another document. Expectations are that the insertion operation will not work and through us an error of duplicate value. We have another document with a slightly different name: Sanket1, for the test purpose, and now we will attempt to update the document with an email: [email protected]

Code:

db.educba.update( { “Name” : ”Sanket1” } , { \$set : {email : “[email protected]” }})

Above query will aim to search a record with Name: Sanket1 and will update the record with adding an email id to the document, here \$set will add a field for the document.

Output:

As you can see in the above screenshot, when we attempted to update a record with the different Name field, the email was the same as one existing document. It threw us a write error, which means an error occurred while writing the document. The error states “duplicate key error collection: test. educba index: email_1 dup key”. The error points to the issue and makes things clear for us to understand.  Like in our case, the email with the value of “[email protected]” is duplicated, already stored, so it will not store again due to unique indexing on the email field. In case you want to know, I’ve used the update with \$get to add another field for existing records.

Use Case for this example: Is the New User Registration where we have to ensure that a single mail id is not used to create multiple accounts.

Conclusion

Implementing Unique Constraint will restrict the database from storing duplicate values for the same indexed key to wrap it up. This feature helps in storing documents with uniqueness. We learned and understood the syntax followed by the working of it. Then we implemented the unique constraint with example and understood the same along with screenshots, respectively.

Recommended Articles

This is a guide to MongoDB Unique. Here we discuss an introduction, how does Unique works and examples for better understanding. You can also go through our other related articles to learn more –

## Routing Protocols Types: Static, Dynamic, Ip, Cisco

What is Routing Protocols?

Routing Protocols are the set of defined rules used by the routers to communicate between source & destination. They do not move the information to the source to a destination, but only update the routing table that contains the information.

Network Router protocols helps you to specify way routers communicate with each other. It allows the network to select routes between any two nodes on a computer network.

Types of Routing Protocols

There are mainly two types of Network Routing Protocols

Static

Dynamic

Routing Protocols

Static Routing Protocols

Static routing protocols are used when an administrator manually assigns the path from source to the destination network. It offers more security to the network.

The administrator must know how each router is connected.

Not an ideal option for large networks as it is time intensive.

Whenever link fails all the network goes down which is not feasible in small networks.

Dynamic Routing Protocols

Dynamic routing protocols are another important type of routing protocol. It helps routers to add information to their routing tables from connected routers automatically. These types of protocols also send out topology updates whenever the network changes’ topological structure.

Easier to configure even on larger networks.

It will be dynamically able to choose a different route in case if a link goes down.

Updates are shared between routers, so it consumes bandwidth.

Distance Vector Routing Protocol (DVR)

In the Distance Vector routing protocol, when a route becomes unavailable, all routing tables need to be updated with new information.

Updates of the network are exchanged periodically, and it is always broadcast.

This protocol always trusts route on routing information received from neighbor routers.

As the routing information are exchanged periodically, unnecessary traffic is generated, which consumes available bandwidth.

Internet Routing Protocols:

The following are types of protocols which help data packets find their way across the Internet:

Routing Information Protocol (RIP)

RIP is used in both LAN and WAN Networks. It also runs on the Application layer of the OSI model. The full form of RIP is the Routing Information Protocol. Two versions of RIP are

RIPv1

RIPv2

The original version or RIPv1 helps you determine network paths based on the IP destination and the hop count journey. RIPv1 also interacts with the network by broadcasting its IP table to all routers connected with the network.

RIPv2 is a little more sophisticated as it sends its routing table on to a multicast address.

Interior Gateway Protocol (IGP)

IGRP is a subtype of the distance-vector interior gateway protocol developed by CISCO. It is introduced to overcome RIP limitations. The metrics used are load, bandwidth, delay, MTU, and reliability. It is widely used by routers to exchange routing data within an autonomous system.

This type of routing protocol is the best for larger network size as it broadcasts after every 90 seconds, and it has a maximum hop count of 255. It helps you to sustain larger networks compared to RIP. IGRP is also widely used as it is resistant to routing loop because it updates itself automatically when route changes occur within the specific network. It is also given an option to load balance traffic across equal or unequal metric cost paths.

Link State Protocols take a unique approach to search the best routing path. In this protocol, the route is calculated based on the speed of the path to the destination and the cost of resources.

Routing protocol tables:

Link state routing protocol maintains below given three tables:

Neighbor table: This table contains information about the neighbors of the router only. For example, adjacency has been formed.

This protocol maintains separate tables for both the best route and the backup routes, so it has more knowledge of the inter-network than any other distance vector routing protocol.

Concept of triggered updates are used, so it does not consume any unnecessary bandwidth.

Partial updates will be triggered when there is a topology change, so it does not need to update where the whole routing table is exchanged.

Exterior Gateway Protocol (EGP)

EGP is a protocol used to exchange data between gateway hosts that are neighbors with each other within autonomous systems. This routing protocol offers a forum for routers to share information across different domains. The full form for EGP is the Exterior Gateway Protocol. EGP protocol includes known routers, network addresses, route costs, or neighboring devices.

Enhanced Interior Gateway Routing Protocol (EIGRP)

EIGRP is a hybrid routing protocol that provides routing protocols, distance vector, and link-state routing protocols. The full form routing protocol EIGRP is Enhanced Interior Gateway Routing Protocol. It will route the same protocols that IGRP routes using the same composite metrics as IGRP, which helps the network select the best path destination.

Open Shortest Path First (OSPF)

Open Shortest Path First (OSPF) protocol is a link-state IGP tailor-made for IP networks using the Shortest Path First (SPF) method.

OSPF routing allows you to maintain databases detailing information about the surrounding topology of the network. It also uses the Dijkstra algorithm (Shortest path algorithm) to recalculate network paths when its topology changes. This protocol is also very secure, as it can authenticate protocol changes to keep data secure.

Here are some main difference between these Distance Vector and Link State routing protocols:

Distance Vector

Distance Vector protocol sends the entire routing table.

It is susceptible to routing loops.

It is less susceptible to routing loops.

Uses only multicast method for routing updates.

It is simple to configure.

It is hard to configure this routing protocol.

Does not know network topology.

Know the entire topology.

Example RIP, IGRP.

Examples: OSPF IS-IS.

Intermediate System-to-Intermediate System (IS-IS)

ISIS CISCO routing protocol is used on the Internet to send IP routing information. It consists of a range of components, including end systems, intermediate systems, areas, and domains.

The full form of ISIS is Intermediate System-to-Intermediate System. Under the IS-IS protocol, routers are organized into groups called areas. Multiple areas are grouped to make form a domain.

Border Gateway Protocol (BGP)

BGP is the last routing protocol of the Internet, which is classified as a DPVP (distance path vector protocol). The full form of BGP is the Border Gateway Protocol.

This type of routing protocol sends updated router table data when changes are made. Therefore, there is no auto-discovery of topology changes, which means that the user needs to configure BGP manually.

What is the purpose of Routing Protocols?

Routing protocols are required for the following reasons:

Allows optimal path selection

Offers loop-free routing

Fast convergence

Minimize update traffic

Easy to configure

Scales to a large size

Compatible with existing hosts and routers

Supports variable length

Classful Vs. Classless Routing Protocols

Here are some main difference between these routing protocols:

Classful Routing Protocols

Classless Routing Protocols

Classless routing protocols can send IP subnet mask information while doing routing updates.

RIPv1 and IGRP are classful protocols. These two are classful protocols as they do not include subnet mask information.

RIPv2, OSPF, EIGRP, and IS-IS are all types of class routing protocols which has subnet mask information within updates.

Summary:

Features

RIP V1

RIP V2

IGRP

OSPF

EIGRP

Classful/Classless

Classful

Classless

Classful

Classless

Classless

Metric

Hop

Hop

Composite Bandwidth, Delay.

Bandwidth

Composite, Bandwidth, Delay.

Periodic

30 seconds

30 seconds

90 seconds

None

30 seconds

255.255.255.255.255

223.0.0.9

255.255.255.255.255

224.0.0.6

224.0.0.10

Category

Distance Vector

Distance Vector

Distance Vector

Hybrid

Default Distance

120

120

200

110

170

Update the detailed information about Excel Sorted Dynamic Unique List on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!