Check customer lists in Excel: update if exists already, add if it doesn't

I have two separate CSV files of customers (let's call them Master List.csv and Update List.csv). There are two columns in Master List that I need to update, Email and Group.

Here's what I need to do in Excel:

1) If a customer's email is in both Master List and Update List, then update the Group for that entry to "Member"

2) If a customer's email is in only Update List but NOT Master List, add their email address to Master List and set the Group to Member for the entry
rovermediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Can you post the files ?
0
rovermediaAuthor Commented:
Unfortunately it's all actual customer data. I can make a mockup though with some dummy data.
0
rovermediaAuthor Commented:
Here is an example workbook.  It shows the original Master Customer List, an Update List, and an example of how everything should be when done.
example.xlsx
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

gowflowCommented:
ok clear just one question
In the sheet you created How it should look You want a new sheet with this info or this info should show in Update sheet or ... ?

gowflow
0
rovermediaAuthor Commented:
It doesn't really matter - could be new sheet, or just make all the changes to the Master Customer List...it's all getting imported back into our ecommerce solution.
0
gowflowCommented:
you tell me all is feasible but to di this I need:

1) Exact sheet names same name as you have in your production workbook
2) Exact layout column header and position of columns same as you have in your production workbook
3) Where you want the data to be updated. Sheet and column

gowflow
0
rovermediaAuthor Commented:
1) sheet names don't have to be exact - I used this only as a mockup and created those sheet names myself
2) in the actual files there are additional columns that will remain untouched (such as customer address, etc)
3) Can the original Master Customer List be updated itself? So the group column there would be updated, and new rows would be added for emails not already in master customer list.
0
gowflowCommented:
You want me to write a macro that will do what you ask for then:

1) I need to know exact name of sheets or else the macro won't work.

for the rest you answered.

gowflow
0
rovermediaAuthor Commented:
I see.

Sheets would be:

Master Customers
Updates

If you want to make a new sheet with the complete updated values, it would be

Revised Customers

Otherwise, would just update the Master Customers.

Thanks!
0
gowflowCommented:
Sorry seems you don't get it completely.

It is not me who decide what you want I can do either. You tell me what is best for you:

Update the initial Master customers
or
Create a Revised Customers.

But as yopu gave very limited info then my suggestion to you knowing that you also have more columns is to simply update Master Customers with the appropriate info.

So this macro you would run once in a while and it would update your master Customers with the latest info.

One last thing I get it that your sheet Updates have data only in Col A and it is all emails right ? No header ? in row 1 like titles ?

You make my life difficult.
gowflow
0
rovermediaAuthor Commented:
Row 1 there are headers in my actual data (just as in example)

Yes, update Master Customers seems the best way.

Thank you.
0
Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit

Sub UpdateMaster()
    With Sheets("Master Customer List")
        Sheets("Update List").Range("A1").CurrentRegion.Offset(1).Copy _
                .Cells(.Rows.Count, 1).End(xlUp).Offset (1)
        .Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
ok sorry for delay due to time zone difference. Here it is.

The below code exist in the attached workbook, however to incorporate it to your production workbook simply save your production workbook into a macroenabled files (extension .xlsm instead of .xlsx) and open the VBA editor create a Module and paste the below code in the module created.

Sub UpdateCustomers()
Dim WSM As Worksheet
Dim WSU As Worksheet
Dim MaxRowM As Long, MaxRowU As Long, I As Long
Dim lMembers As Long, lSubscribers As Long
Dim cCell As Range

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WSM = Sheets("Master Customers")
MaxRowM = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row + 1

Set WSU = Sheets("Updates")
MaxRowU = WSU.Range("A" & WSU.Rows.Count).End(xlUp).Row

For I = 2 To MaxRowU
    Set cCell = WSM.Range("A2:A" & MaxRowM).Find(what:=WSU.Range("A" & I), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        cCell.Offset(, 1).Value = "Subscriber"
        lSubscribers = lSubscribers + 1
    Else
        WSU.Range("A" & I).Copy WSM.Cells(MaxRowM, "A")
        WSM.Cells(MaxRowM, "B") = "Member"
        lMembers = lMembers + 1
        MaxRowM = MaxRowM + 1
    End If
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox ("following categories were updated successfully:" & Chr(10) _
    & "New Members: " & lMembers & Chr(10) _
    & "Subscribers: " & lSubscribers)
End Sub

Open in new window


Pls run the attached file enabling macros and simply select from the Macroes UpdateCustomers and run it and see results in sheet Master Customers. I took the liberty to add more items in sheet Updates. At the end it give you a summary of what was performed.

Let me know if any question or more help needed.
gowflow
example.xlsm
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning 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.