Need help with sorting - turn 3070,3071,3070 10-1 into 3070, 3070 10-1, 3071

[matt_m_is_me]

Hi there,

I'm working with some data and the sample names are labelled with either a number, or a number and a dilution, but I don't think the dilution is written in a proper way to allow sorting. This is what some of our data looks like when we sort from lowest to highest:

3070
3071
3070 10-1
3070 10-2
3071 10-1


The 10-1 means it's a diluted sample of the main sample, so 3070 -1 is the diluted version of 3070.

We would like to be able to sort the values, but when we sort it clumps up the non-diluted samples, then the diluted samples. Would it be possible to make excel sort out data by grouping up the diluted samples with the original. It would be perfect if this would be the final result:

3070
3070 10-1
3070 10-2
3071
3071 10-1

This would allow us to make a graph showing the original sample, with all of the diluted samples right after, making it much easier to read.

Thanks for the help everyone!

[matt_m_is_me]

Hi there,

I'm working with some data and the sample names are labelled with either a number, or a number and a dilution, but I don't think the dilution is written in a proper way to allow sorting. This is what some of our data looks like when we sort from lowest to highest:

3070
3071
3070 10-1
3070 10-2
3071 10-1


The 10-1 means it's a diluted sample of the main sample, so 3070 -1 is the diluted version of 3070.

We would like to be able to sort the values, but when we sort it clumps up the non-diluted samples, then the diluted samples. Would it be possible to make excel sort out data by grouping up the diluted samples with the original. It would be perfect if this would be the final result:

3070
3070 10-1
3070 10-2
3071
3071 10-1

This would allow us to make a graph showing the original sample, with all of the diluted samples right after, making it much easier to read.

Thanks for the help everyone!

[MrShorty]

What usually happens with this kind of data entry is that the 3070 and 3071 are entered as numbers. The 3070 10-1 cannot be interpreted as numbers so Excel treats them as text strings. The sorting rules for numbers and text are different, and the result is unexpected sorting like this.

I would probably approach this one of two ways:

1) Separate sample id from dilution ratio. Then you can have a two keyed sort that will first sort by column 1 (sample id) and then by column 2 (dilution ratio).
2) During data entry, precede every entry by an apostrophe so that every value is a text string. If you get a prompt warning about "numbers stored as text," make sure to select the "treat everything as text" option.

[ckim551]

sort.xlsx

Add a new column and add formula =TEXT(A1,"###") and sort by new column.

Charles

[matt_m_is_me]

Hi there, thanks a lot for the help so far.

The problem I'm having is that the excel file is made by a program that is connected to a machine that we use in the lab. We label our samples like "3070, 3070 10-1, 3080 10-2" etc on the program we use before using the machine. Then we run the samples through the machine, and the program outputs an excel file full of the results from the machine.

For the first way, it's looking like we'd have to manually go into the excel file and create a new column and manually type in the dilutions which I don't really want to do.
The second way seems possible, but again it would mean that we'd have to label our samples with a * at the beginning and hope that it doesn't screw up anything in the program that we're using. Plus that would mean changing the way that we label our samples before running them through the machine, I'm sure our older employees using the machine would not appreciate the change.

I was told by another user of this forum this:
"Add a new column and add formula =TEXT(A1,"###") and sort by new column."

I rather like this idea, if we can get it to work. I've been playing around with this function and it seems to just copy the cell completely, which means that I can't sort by the first then the second. Would it be at all possible to have our function only type in the dilution of the samples in the second column?

So 3070 in the first column, using the function would put either 0, 10-0, or leave the cell blank in the second column.
3070 10-1 in the first column, using the function would put 10-1 into the second column, etc.

This way I believe would be the best way for us, if we can get this function to work the way I'd like it to.

[matt_m_is_me]

Hi Charles,

Thanks a lot for the help so far but I seem to be missing something important. Sorry I'm a bit new to this.

I've been testing your function and it appears to copy the entire cell from the first column and put it into the second column so my data looks like:
3070 3070
3080 3080
3070 10-1 3070 10-1
3070 10-2 3070 10-2, etc

So I'm still unable to get my data to look like:
3070
3070 10-1
3070 10-2
3080

Would it be possible to change the function so that it only puts the dilution into the second column? So for example:
3070 in the first column, using the function would put either 0, 10-0, or leave the cell blank in the second column
3070 10-1 in the first column, using the function would put 10-1 into the second column

I think this might be what you were trying to do for me, but I just couldn't get it to work.

Thanks again for your help though, we've almost got it.

[ckim551]

Can you create an Excel file to show before and after examples?

[FDibbins]

Change the formula inthe helper column to this, copied down...
=TEXT(LEFT(A1,4),"0000")
Then sort based on the 2nd column

[FDibbins]

Welcome to the Forum, unfortunately:

This was from a duplicate post and as such would not comply with Rule 5 of our forum rules. However, because there has already been valid feedback on the thread, I have merged into this 1.

Please do not post duplicate threads, they will be closed!!

[matt_m_is_me]

FDibbins you're my hero, thanks so much, it worked exactly how I wanted.

Thanks for your help too, Charles.

I'll mark this as solved. Thanks everyone.

[FDibbins]

Happy to help and thanks for the feedback