Filter datatable by lastname in name column

Question

0.00/5 (No votes)

See more:

I have a Datatable like

Name                ID
Kingshuk Dutta      2
Tapas Nayak         4
Prakash Kiran       7

I've to sort the datatable by Name column but using the LAST NAME only.
It should show like:

Name                ID
Kingshuk Dutta      2
Prakash Kiran       7
Tapas Nayak         4

What I have tried:

The datatable is coming like this, I've to sort it.

I thought of taking the names in a string array and then sort, afterwards insert into datatable. but thats wrong idea. Please suggest.

Posted 31-Jul-17 21:01pm

Kingshuk_SP

Updated 31-Jul-17 21:12pm

Add a Solution

2 solutions

Solution 1

If you split the First name and Last name into separate columns it will make working with your data a lot easier.

Posted 31-Jul-17 21:03pm

Graeme_Grant

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Daniels · Accepted Answer · 2017-07-31T21:11:00

Solution 2

Solution 1 provides the simplest way to do this. If, however, you really need to have a single Name field, there is a way to sort it:

C#

dt.AsEnumerable().OrderBy(x => (x.ItemArray[0] as string).Split(' ')[1]).CopyToDataTable(); // where 'dt' is your DataTable

AsEnumerable returns an EnumerableRowCollection<DataRow>, on which you can call OrderBy with a lambda expression that tells to use the second word of the name. This returns an OrderedEnumerableRowCollection<DataRow> and you can call CopyToDataTable to convert this collection into a new DataTable.

Posted 31-Jul-17 21:11pm

Thomas Daniels

Comments

Karthik_Mahalingam 1-Aug-17 4:00am

5

Kingshuk_SP 2-Aug-17 2:01am

one question.

can you explain the use of "Split(' ')[1]" in the query please ???

Kingshuk_SP 2-Aug-17 2:24am

yeah I got that's for first space we'll get lastname and is sorting. fine.

But if the Name column is like:

Mary Sue Van Pelt
Rose Van Juliet

in such case how can i run a loop ?

Thomas Daniels 2-Aug-17 2:32am

What do you count as "last name" here? Only the last part? Or everything except the first part?

Kingshuk_SP 2-Aug-17 2:46am

yeah only last part. 

like in case of the below name:

Mary Sue Van Pelt

"Pelt" would be the last name

Kingshuk_SP 2-Aug-17 2:53am

thanks guys. I've found the solution:

the code will be:

dt = dt.AsEnumerable().OrderBy(x => (x.ItemArray[0] as string).Split(' ')[(x.ItemArray[0]).ToString().Split(' ').Length - 1]).CopyToDataTable();

Thomas Daniels 2-Aug-17 2:56am

If you use LINQ (using System.Linq;), you can also use the .Last extension method to make your code easier to read:

.Split(' ').Last()

Kingshuk_SP 2-Aug-17 3:06am

thanks @ProgramFOX

it reduced my code.. cheers. :)

Thomas Daniels 2-Aug-17 3:08am

You're welcome!

Kingshuk_SP 2-Aug-17 3:16am

one more thing i need. I have a datatable like below:

Name               Department
John Alter         D01
Prakash Kiran      D01
Steffy Cold        D01
Julia Roberts      D02
Candy Mitchel      D02
Monika Diesel      D03

I want it like below:

Name               Department
John Alter         D01
Prakash Kiran      
Steffy Cold        
Julia Roberts      D02
Candy Mitchel      
Monika Diesel      D03

when its ordered by a department, it should not repeat for the same department.
But the row should be there, only if there is same value in next row under Department column, that needs to be BLANK

Thomas Daniels 2-Aug-17 3:53am

There's no single-line solution for that, but this works (note: the DataTable must be sorted already for this to work)

DataTable newDt = dt.Clone(); // clones the structure, not the data
for (int i = 0; i < dt.Rows.Count; i++)
{
    DataRow newRow = newDt.NewRow();
    newRow["Name"] = dt.Rows[i]["Name"];
    int originalIndex = dt.AsEnumerable().ToList().FindIndex(x => x["Department"] == dt.Rows[i]["Department"]);
    if (originalIndex < i)
    {
        newRow["Department"] = "";
    }
    else
    {
        newRow["Department"] = dt.Rows[i]["Department"];
    }
    newDt.Rows.Add(newRow);
}

Kingshuk_SP 2-Aug-17 4:39am

in this case the condition if (originalIndex < i) always is false and thus not removing the duplicate names.

i think we should go for a counter or flag concept

Kingshuk_SP 2-Aug-17 4:38am

in this case the condition if (originalIndex < i) always is false and thus not removing the duplicate names.

Thomas Daniels 2-Aug-17 4:44am

For me, it worked. originalIndex is the first occurrence of a certain department, so 'originalIndex < i' will be true if a department name is used again later.

Kingshuk_SP 2-Aug-17 4:53am

for me its not working because always originalIndex value is equal with i value.
I debugged and still its same. :(

Thomas Daniels 2-Aug-17 4:58am

Then I'm afraid I can't help.

Kingshuk_SP 2-Aug-17 5:14am

ok. but when I am debugging , i saw...

every time the loop runs, as the "i" value increments, the originalIndex value also increments.. always their value is same. that's y its not going to be true anytime.

int originalIndex = dt.AsEnumerable().ToList().FindIndex(x => x["Department"] == dt.Rows[i]["Department"]);

here originalIndex value is dependent on "i" everytime
so it will find the index of that row according to i value right.

so as an example, when i == 0 , then it will find the index of department corresponds to 0th row and so on....

then how it is coming different in your case, I am not getting really. :)

if you don't mind can you please re-check if you have made any small code changes inside this please

Thomas Daniels 2-Aug-17 5:17am

dt.Rows[i]["Department"] simply returns the department of the row you're copying into newDt, so it will be "D01" for both the first and the second row.

It works perfectly for me; the only thing that I see that could be changed, is replacing the '==' in that line by a .Equals call:

int originalIndex = dt.AsEnumerable().ToList().FindIndex(x => x["Department"].Equals(dt.Rows[i]["Department"]));

Kingshuk_SP 2-Aug-17 5:21am

Thanks a lot. Its done using Equal. cheers. :)