Forum Discussion
ColinL965
Sep 08, 2022Copper Contributor
Sort function
Trying to figure out a sort on a table.
my problem is as follows.
a column contains serial no's . There are wrongly entered serial no's ( without a prefix )
eg. SN12345 = Correct, 12345 = Incorrect
I would like to group the correct and incorrect no's together in some sort of sort. Is this possible using just the data in the column?
Sorry if i'm not making this clear.
Hope someone can help?
- ColinL965Copper ContributorI am helped by the fact that the near duplicates are few, however the data table itself is massive.
- NikolinoDEGold Contributor
A quick solution would be to add an additional column with the formula...see attached file.
to find Text
=LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))-1)
to show result
=IF(B1=""; "Incorrect";"Correct")
Hope I could help you with these information.
I know I don't know anything (Socrates)
- ColinL965Copper Contributor
This has possibilities, however it is complicated by the fact that some of the serial numbers without SN prefix are actually genuine serial No's for other non-related units ( i would need to look at the complete dataset to determine this however ) I think it would be too complicated to work out criteria to distinguish between them, but i think this could be a good starting point.
- NikolinoDEGold Contributor
It is not dependent on the letters...if letters then correct, if not then incorrect...any text.
Attached is the example in the file expanded with other letters...otherwise VBA would be an additional option.
- Harun24HRBronze ContributorDid you try SORT method from DATA tab?
- ColinL965Copper ContributorI'll have a look at this.
- Riny_van_EekelenPlatinum Contributor
ColinL965 Would it work if you created a new column that fixes the issue of the incorrect serial numbers. Something like this:
- ColinL965Copper ContributorEach entry in the table is unique. At this stage i dont want to alter any of the data, just bring entries together for later deletion from a main database. For further information this table has been exported from a much larger database, which i cannot modify directly or import back to.