Filtering out duplicates or unique Entries....HELP!!!

[spunkeymonkey]

I have a project that I need to submit to my Manager tomorrow, and am at my wits end trying to accomplish something that I know should be straight forward.

Using Excel 97, I have been trying to use the AdvanceFilter as well as formulas found through online searches, but I simply cannot achieve the results I am looking for.

Below is a simplified look at my excel situation:

A B C D

1 3 3 6
2 8 0 3
3 5 7 4
4 2 4 0
5 0 9 2

I have four companies (A, B, C & D) which I need to bill for each active employee (represented here by numbers ranging from 0 to 9). Some employees may appear in two or more of the companies, but I only need to account for them ONCE (thus, eliminate any duplications).

What I need is a reprentation of unique entries, and I can't figure out how to use the Advance Filter feature or another Excel option to accomplish the following results of unique employee entries (in the same format as above obviously):

Company A should show the following unique entries: 1, 2, 3, 4, 5
Company B should show the following unique entries: 8, 0
Company C should show the following unique entries: 7, 9
Company D should show the following unique entries: 6

Either, I would like a new column of unique entries (I would just bill cumulatively for 10 employees) OR for duplicates to be deleted (I could then breakdown the billing--Company A for five people, Company B for two people, Company C for two and Company D for one).

S.O.S Someone if you could HELP!
I need a Step by Step because I am obviously missing something in my previous attempts

 

[sweater_vests_rock]

spunkey.

no worries.

unfortunately Enum is not supported in Excel97. you will have to replace these lines

Enum InputBoxConstants 
   xlText = 2 
   xlRange = 8 
End Enum

with this

#If VBA6 Then
Enum InputBoxConstants
   xlText = 2 
   xlRange = 8
End Enum
#Else
   Public Const xlText as Integer = 2
   Public Const xlRange as Integer = 8
#End If

after you have done that, everything should be ready for the program to run. to do this, exit the VBEditor and run the program with the key command ALT+F8, selecting the macro entitled "FilterUniqueEmployees", and clicking "OK".

the program will direct you what to do, ie select the range with the company names (like you would for a chart), and then again select the range with the employee ids. the program will handle the rest.

as i mentioned, with a long (and wide?) list of names, this program might take a bit to run. i haven't had a chance to look into expediting the process, but i do have one or two thoughts to test out.

hope this helps.
ben.

--------------------------------
ps. if you are still having problems with the Public Const statement, add an andpersant (&) at the end of the lines (blahblah = 2&) for the two equal statements. i've never had to use that before, and i can't test it!

 

[spunkeymonkey]

Thanks for the programming for dummies session

I have learned alot. I am going to stop bugging you now because I am sure you have far more pressing programming problems you could be attending to right now.

Just to keep you update before I let you go. I copied the new section into the beginning of the code and it seems to still be having problems with the lines that contain the ENUM:

#If VBA6 Then
Enum InputBoxConstants
xlText = 2
xlRange = 8
End Enum
#Else
Public Const xlText As Integer = 2
Public Const xlRange As Integer = 8
#End If

thanks for all the help
spunkey

 

[sweater_vests_rock]

spunkey.

alright, i guess just drop them then. delete all lines but

Public Const xlText As Integer = 2
Public Const xlRange As Integer = 8

ben.

 

[spunkeymonkey]

Super Star

Ben

Thanks alot

I just tried it and it works perfectly. It automates what I was doing manual. Wicked. Sweater-Vests do Rock!

Have a great day.
Spunkeymonkey