Excell Question

Posted on 1999-01-11
Medium Priority
Last Modified: 2011-04-14
I have 2 separate workbooks in Excell 97 format.
The first one has a list of values in the A Column.  For simplicity, let's say there are 50 different values.
In worksheet 2, I have a sub-list of about 10 values.  These 10 values can all be found in the first spreadsheet.

The small list are values that I need to delete out of the first list.  How can I compare these two lists, and find which values are present in both lists?  Is there a Union function or something that will do it?  
Question by:TISS

Expert Comment

ID: 1014959
One possible solution:
Run a macro w/ a nested loop that compares each value in the big list to each value in the small list. When a match is found, the current cell in the big list ="", then exit the exit the nested loop and proceed to the next value in the big list.

Author Comment

ID: 1014960
That's one solution I thought of too, but I counldn't figure out how to code a macro to look at the value in another workbook.  
If it were too lists on the same workbook sheet, I'd be fine.

Any ideas on this?  

Expert Comment

ID: 1014961
Can you use the DGET() database function to extract the fields that match?  Check Excel help on this function.  DGET() extracts the single values that match fields with a pattern you name.  Really, what you want to do amounts to database manipulation and excel is not the best tool, but it sounds like you may be stuck with it.

Will You Be GDPR Compliant by 5/28/2018?

GDPR? That's a regulation for the European Union. But, if you collect data from customers or employees within the EU, then you need to know about GDPR and make sure your organization is compliant by May 2018. Check out our preparation checklist to make sure you're on track today!


Expert Comment

ID: 1014962
If you want to do this only once, you can cut and paste the small database into the larger sheet too.  This will be messy if you are trying to automate this process, however.


Accepted Solution

Urandrial earned 80 total points
ID: 1014963
Here's what i do as a one off.

1.  In coumn B say of the small list enter a -1
2.  In column B of the large list enter a 1
3.  Copy small list to bottom of large list
4.  Highlight columns A and B.  Select Data...Sort..Column A...ascending
5.  In column C row 2 enter '=B1+B2'
6.  Drag the bottom right corner of this cell to the bottom of the combined list (thus      copying the formula for all othe other cells.
7.  Copy column C..paste special...values into column D
8.  Delete data from column C
9.  Highlight columns A and D.  Select Data...Sort...Column D...Ascending.
10.  Delete all rows with a value of 0.

This will delete all of the values in the large list that where contained in the short list.

I hope this helps.


Author Comment

ID: 1014964

Featured Post

We Need Your Input!

WatchGuard is currently running a beta program for our new macOS Host Sensor for our Threat Detection and Response service. We're looking for more macOS users to help provide insight and feedback to help us make the product even better. Please sign up for our beta program today!

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

This is about my first experience with programming Arduino.
For years I have put up with a wireless mouse that consistently disappears or is non-responsive.  This is the story of how I discovered it wasn't really the mouse!
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question