Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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