• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Sorting Excel with Partial Key

Hello,

I use different formulas and macros to sort/filter an excel s/s.  However, I currently have a situation where I have been unsuccessful..I don't think it is difficult, I just can't figure out wysiwyg or programmatically how to solve.  Below is the situation.

I have a s/s that has been merged with an updated version of the same s/s.  
What I want to do is sort the s/s with the "TC ID" but need to search partially in order to determine duplicates (old rows) that can be removed.

Example:

TC ID
PR72300A.SOMETHING...0001
PR72320.SOMETHINGELSE...0002
"
"
PR72300ax.SOMETHING.0001
"
PR72320xx.SOMETHINGELSE.0002

As u can see the first two "TC ID's" have something in commone with the older ones further down... however, how to sort using partial key lookup so they are in the following order:

PR72300A.SOMETHING...0001
PR72300ax.SOMETHING.0001
"
PR72320.SOMETHINGELSE...0002
PR72320xx.SOMETHINGELSE.0002
"
"

This s/s has about 64k rows.
0
66chawger
Asked:
66chawger
  • 4
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
I would imagine that sorting normally would do just what you want.
0
 
Saqib Husain, SyedEngineerCommented:
Unless I am missing something
0
 
66chawgerAuthor Commented:
Yes, the problem is there are many rows separating these in the s/s, so with just normal sorting I cannot get them grouped together as I indicated.  The newer row might be 10-20 rows before the old row even after sorting.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
66chawgerAuthor Commented:
I tried different kinds of filtering... contains, begins with, custom... even fiddled with some formulas and macros but no success.
0
 
Patrick MatthewsCommented:
Please post a sample file.  I agree with ssaqibh: this is something that should work just fine.
0
 
66chawgerAuthor Commented:
My apologies for the lack of response..  I ended up writing some VB script to do what I needed.  It seems the Excel sorting mechanism would work, but the situation was a bit more elaborate.
0
 
66chawgerAuthor Commented:
The question has been open for a lengthy period of time.  The solution ended up being VB code I had to write in order to accomodate the task.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now