Hi Guys

wandering could someone give me a hand here.I posted here as seemed most logical place

At work myself and my team have a task to send notifications to customers all automated.

Generally a list is built from our systems and it gives all customer IDs that need to be notified.

a Customer ID example AB12345

So in this list it could be 10 customers or 2000 not necessarily in numerical order its randomly pulled.


=====
Now the list for example is 2000 Customer IDs and from that 2000 there could be 400 invalid IDs which we get a second list showing us them

What is a quick simple method using excel or any other free way - to compare Sheet A to Sheet B - removing sheet B list from main sheet As list so when its done sheet A for example will now only have the correct 1600 Cust IDs



Hope i explained it correctly any questions please let me know.

So B would be a subset of A, you are looking to subtract B from A. This is A-B = {aϵA: a ¬ϵ B} where ϵ is the membership and ¬ is negation. In words this would mean that the set A minus the set B is a set of all the elements of A that are not in the set B.

If you need help with how to specifically do that one would need to know the file format the file is received in. Ideally you would receive a csv file where ids are separated by a newline or a comma, in which case you can use whatever tool you want. Python or excel for example

Python:

A = open("A.csv",'r')
B = open("B.csv",'r')
C = []
for element in A:
if element in B:
C.append(a)

If it's excel files you are receiving, using excel will be easier, it's more user friendly than a programming language. In this case you need to know how to use formulas, you would just need to know how to check for membership, how to negate a boolean, and perhaps how to use a conditional. I believe these are the Match function, the Not function and the IF function respectively. The end result should be something similar to the python code above, the mathematical formula, and the english explanation. Find someone in your team who knows how to use excel, or take a 101 course on it, should be no problem.

Hi LoveThee thanks

Yes excel is not something i am skilled in at all unfortunately i keep meaning to do a beginner course in it but i always seem to be studying other courses more in line with i actually do.


The IDs can either be separated by commas or i can manually delete the comma

example
Sheet A
ab1
ab2
ab3
ab4
ab5
ab99
ab292
ab343
ab0939

Sheet B
ab99
ab3
ab292
ab1
ab0939


i need to take away the IDs listed in sheet B from sheet A

and be left with
Sheet A
ab2
ab4
ab5
ab343



and the order they are in as in numerical/apha is not important the automated tool does the rest once we have a clean list it sends out a notification.


any method to do that fast is what im looking as if i have a list 2000 and need to take random 400 using my eyes that can be a long slow boring task im gonna try messing with excel now but i am really bad with it.

Um...have the process that's doing the data pulls deduplicate the data, and just give you the results?

Then, work on a system where sending emails is automated, taking the deduplicated list as input.

I can't think of any valid reason why humans need to be involved with this process.

ha yes our managers are in talks with the automated company that send the list out.

i will explain it a bit better maybe

The company i work for do planned works on a network- these planned works could involve a down time for the customers.

So we use a tool called Agile to send the notification it is linked to our database where customer contact details is stored.

Our database is what generates the impact list.

We then feed that impact list in to agile to it then starts to compile the notification and cross references our database for customer contact details.

If even one customer contact detail is missing the tool will not send the notification until that one Customer ID is removed from the list or updated on the database.

I would be even happy if just sent the notification and then said these are the ones not sent ---"list"

Step one find the manual for excel.

Step 2 search for the most basic example of a formula, pay attention to generic words like function, input, output, variable, argument, parameter, so you can understand the underlying ideas rather than excel specific details.

Step 2, search for the IF function, read it learn what it does, pay attention to generic words like branching, conditional and control flow

Step 3 search for the = operator, pay attention to generic words like comparision, condition, equality.

Step 4 search for the Not function, pay attention to generic concepts like negation, inversion, booleans.

Step 5 search for the Match function, pay attention to generic concepts like membership, list or set.

Ideally try to find a resource that explains the generic mathematical/programming concepts. Wikipedia is an option, as always. But try to find a nerd within your company that can recommend a resource and solve your doubts if they arise.

If you can't find someone to help you within your company, try to contact your local library and ask for books about algebra. Until then you are stuck with doing this by hand.

Additionally, try to find out what the format of the file is, if it's an .xlsx it's a file with a lot of junk added by microsoft so you can only read it with Excel. If it's something like a .csv then you can use pretty much any tool.

The extension, the size of the file and an investigation with an inert tool like notepad should reveal the nature of the file.

Thanks again Lovethee

i think i will enrol myself in a INE or similar excel course for beginners. I have been meaning to do it for years seems i always have a problem that seems basic enough but no skillset i will mention what has been mentioned above to one of my colleagues who i know is a bit better with excel thanmyself

thanks again

This seems like you could just use a simple vlookup and filter to get your list. In Sheet A, next to column where data is, put in formula "=vlookup($A2,Sheet2A1:A10,1,0)" - this will give you all the matches. Filter this column for "#N/A" and you have exactly what you need.

Good luck! It seems like that's what you're looking for.