Append Unique Data

[EdStockton]

I have a question that I am sure is not unique. I have one Workbook with a column of data in Sheet1 and a column of data in Sheet2.

The column on Sheet1 is my master customer list. Most customers are repeat customers but each month we get a few new customers. I show the current month sales on Sheet2. I want to compare the list on Sheet1 with the list on Sheet2. If there are names on Sheet2 that do not appear on Sheet1, I want to add those customer names to the bottom of the list on Sheet1.

Any help would be appreciated.

Thanks, Ed Stockton

 

[hiker95]

EdStockton,

New samples raw data:

Excel 2007
	A
1	Name
2	Joe Walsh
3	Tom Cary
4	Bill White
5	Donald Super
6	Bill Downs
7	Jim Burgess
8	Ed White
9	Cary Baker
10	Jim Brown
11	Carl Sayers
12	Jim Foster
13
Sheet2

Excel 2007
	A
1	Name
2	Joe Walsh
3	Tom Cary
4	Bill White
5	Larry Smith
6	Donald Super
7	Jim Burgess
8	Ed White
9	Joe Adel
10	Cary Baker
11	Jim Brown
12	Thomas Watkins
13	Carl Sayers
14
15
16
Sheet1

After the new macro in worksheet Sheet1:

Excel 2007
	A
1	Name
2	Joe Walsh
3	Tom Cary
4	Bill White
5	Larry Smith
6	Donald Super
7	Jim Burgess
8	Ed White
9	Joe Adel
10	Cary Baker
11	Jim Brown
12	Thomas Watkins
13	Carl Sayers
14	Bill Downs
15	Jim Foster
16
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub UpdateNames_V2()
' hiker95, 08/07/2014, ME797099
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, a As Range, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w2
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set a = w1.Columns(1).Find(c, LookAt:=xlWhole)
    If a Is Nothing Then
      nr = w1.Cells(w1.Rows.Count, "A").End(xlUp).Row + 1
      w1.Cells(nr, 1) = c
    End If
    Set a = Nothing
  Next c
End With
With w1
  .Columns(1).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateNames_V2 macro.

 

[hiker95]

apo,

Edit: oops.. I didn't see hiker95's post #8.. that is a better explanation.. thanks mate..

You are very welcome.

 

[EdStockton]

Thank you all for your help. This has resolved my problem. I do well with formulas because I'm good with math, but programing is actually a language. I got a minor in programing as an undergraduate but that was in 1983 and I've not done any programing since. There is just so much I don't know.

I remember using a button when I got bored and did a self education course on programing with VBA about five year ago and it worked well with forms. I will try it as what I'm doing now will be a monthly procedure.

I participate in an accounting forum (being a CPA) and on that forum we are supposed to provide a response when the subject is considered resolved and provide a summary and perspective to close out the thread. Is that to be done here as well?

 

[hiker95]

EdStockton,

Thank you all for your help. This has resolved my problem.

You are very welcome. Glad we could help.

I remember using a button when I got bored and did a self education course on programing with VBA about five year ago and it worked well with forms. I will try it as what I'm doing now will be a monthly procedure.

See if something in my most up to date list, in the below link will help:

Training / Books / Sites as of 6/26/2014

http://www.mrexcel.com/forum/excel-questions/788128-compare-values-worksheets.html#post3857750

I participate in an accounting forum (being a CPA) and on that forum we are supposed to provide a response when the subject is considered resolved and provide a summary and perspective to close out the thread. Is that to be done here as well?

You do not have to do anything further - your good comments are enough.

And, come back anytime.

 

[EdStockton]

I'm back again. I tried to adapt your code to a different set of circumstances and was unsuccessful.

I have uploaded the file to Box. The link is as follows: https://app.box.com/s/rljtuf4p9jwthxwnaw1m

I think I have done that correctly.

I want the code to look at the names on worksheet CD column B and compare them to the names on JanSales column J. In the names on JanSales do not appear on CD column B, I want to add those names to the end of the list on column B worksheet CD.

I tried to adapt the code you gave me but instead of the names being added to the end of column B on the CD worksheet, they are added to Column A starting at cell A2 of the CD worksheet.

I've tried, but I don't understand the code well enough to figure out why.

Eventually, I would like to put in the names of successive worksheets into a particular cell somewhere on the CD worksheet, run the code, and add the names appearing on other worksheets (FebSales, MarchSales, etc.) to column B of the CD worksheet.

Your help for someone who is new would be greatly appreciated by that person, Me.

 

[apo]

Hi..

Here's how i would do it..

1. Create named Ranges for each months sheet for column J.

I made one when testing called "JanSales".
Use this formula in the Refers to: field..

=OFFSET(JanSales!$J$2,0,0,MATCH("*",JanSales!$J:$J,-1)-1,1)

2. Paste the below code into a Module and run it.. it will copy the names you want over to the CD sheet in Column B and will automatically insert your SUM formulas as needed 2 rows below your last row..
So you can remove your current Sum formulas (for JanSales sheet anyway) as they will be in the wrong row when you add names..

Sub apo()
    Dim i As String, vArr, it, x, cnt As Long
    With CreateObject("scripting.dictionary")
        For Each it In Sheets("CD").Range("B6:B" & Sheets("CD").Range("B" & Rows.Count).End(xlUp).Row)
            x = .Item(it.Value)
        Next
        For Each it In Range("JanSales")
            If Not .Exists(it.Value) Then
                If i = vbNullString Then
                    i = (it): cnt = cnt + 1
                ElseIf it <> vbnulstring Then
                    i = i & "$" & (it): cnt = cnt + 1
                End If
            End If
        Next
        vArr = Split(i, "$")
        Sheets("CD").Range("B" & Sheets("CD").Range("B" & Rows.Count).End(xlUp).Row).Offset(1).Resize(UBound(vArr)).Value = Application.Transpose(vArr)


        Sheets("CD").Range("C" & Sheets("CD").Range("B" & Rows.Count).End(xlUp).Row).Offset(2).Resize(1, 14).Formula = _
        "=Subtotal(9,C6:C" & Sheets("CD").Range("B" & Rows.Count).End(xlUp).Row & ")"


        Sheets("CD").Range("D" & Sheets("CD").Range("D" & Rows.Count).End(xlUp).Row).Value = ""
    End With
End Sub

Eventually, I would like to put in the names of successive worksheets into a particular cell somewhere on the CD worksheet, run the code, and add the names appearing on other worksheets (FebSales, MarchSales, etc.) to column B of the CD worksheet.

I haven't got time right now.. but.. again.. what I would do (and there are always many ways to skin a cat).. is:

1. If you know which sheets you want to loop through and apply the same process.. then make it loop through them.. your "JanSales" range part in the code would therefore be dynamic..

2. Or.. Put all your sheet names in a cell separated by a comma or something (like you say).. or in a listbox.. etc etc.. and make the code be applied to your selection/input.

Edit: I may not have explained that too well.. let me know if you don't understand my waffling..

 

[hiker95]

EdStockton,

Thanks for the workbook.

I have created an array to hold the worksheet names of the worksheets that you want the macro to run in.

w = Array("JanSales", "FebSales", "MarchSales", "AprilSales", "MaySales", "JuneSales")

You will have to update/change the array after you insert new monthly Sales months.


Sample worksheet CD before the macro (the YELLOW cell is the current last use cell in column B):

Excel 2007
	B
1210	Zena w. Green
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
CD

After the macro we have found 74 new names:

Excel 2007
	B
1210	Zena w. Green
1211	Kathy Wheeler
1212	Lamar Dorsey, III
1213	Karen M. Swilley
1214	Isaac Jenrette
1215	E. Richard Clark
1216	Marcellous Jackson
1217	Linda M. Turner Lockhart
1218	Demetrius Patterson
1219	Samantha Nicholson
1220	Dorothy Robinson
1221	Lisa D. Logan
1222	Medina Bolton
1223	Xavier Light
1224	Frederick D. Farmer
1225	Annie W. Lindsey
1226	Daniel Green
1227	Christine A. Agnew
1228	Robert Hightower
1229	Carter Smith, Jr.
1230	Carolyn Moon
1231	Ezekiel McConnell, Sr.
1232	Danny Stillwell
1233	Walter Barber McClelland
1234	Everett M. Ellington, Jr.
1235	Sonya Bridges
1236	Charleise T. Young
1237	Marcus Phillips
1238	Sara N. Hammond
1239	Rosa Farley
1240	William Carr, Jr.
1241	Charles E. Wyatt
1242	Judy G. Coleman
1243	Gloria H. Colbert
1244	Darryl M. Combs
1245	Manuel Jackson
1246	Carolyn Colbert
1247	Abraham L. Davis
1248	Beverly Carroll Daniel
1249	Leroy F. Hollingsworth
1250	Thales e. Walker
1251	David Lamar Bertolini
1252	Kenya Neal Copeland
1253	Alice Spencer
1254	Katrina Jones
1255	Andrew William Fellers
1256	Arian Johnston
1257	Kathleen Johnson
1258	Mearia F. Howell
1259	Gail Zellars
1260	Natha S. Harris
1261	Phyllis Grier
1262	Margie W. Clark
1263	Monica Y. Spann
1264	Elizabeth Markam
1265	Dr. Carter Smith, Jr.
1266	Ronald McCants
1267	Donald L. Alford
1268	RALPH A. LAMBERT
1269	Kathryn Bell
1270	Cynthia T. Holloway
1271	Paul E. Hanley, Jr.
1272	Angela Lavern Nelson
1273	Irish N. Benson
1274	Valerie Hudson Watts
1275	Nellie Roberts
1276	Shirley Brewton
1277	Linda Horne Melvin
1278	Susan Claire Dobbs
1279	Samantha Ellington
1280	Willie J. Jones
1281	Betty N. Chaney
1282	Shadonna Carr
1283	Marion Parks
1284	Mary Katherine Lenoir
1285
CD

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub UpdateNames_V2()
' hiker95, 08/15/2014, ME797099
Dim wc As Worksheet, ws As Worksheet
Dim c As Range, b As Range
Dim w As Variant, i As Long, nr As Long, lr As Long
Application.ScreenUpdating = False
Set wc = Sheets("CD")
If wc.FilterMode Then wc.ShowAllData
w = Array("JanSales", "FebSales", "MarchSales", "AprilSales", "MaySales", "JuneSales")
For i = LBound(w) To UBound(w)
  Set ws = Sheets(w(i))
  With ws
    lr = .Cells(Rows.Count, "J").End(xlUp).Row
    For Each c In .Range("J2", .Range("J" & Rows.Count).End(xlUp))
      If c <> "" Then
        Set b = wc.Columns(2).Find(c, LookAt:=xlWhole)
        If b Is Nothing Then
          nr = wc.Cells(wc.Rows.Count, "B").End(xlUp).Row + 1
          wc.Cells(nr, "B") = c.Value
        End If
      End If
    Next c
  End With
Next i
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateNames_V2 macro.

 

[EdStockton]

1. Create named Ranges for each months sheet for column J.

I made one when testing called "JanSales".
Use this formula in the Refers to: field..

=OFFSET(JanSales!$J$2,0,0,MATCH("*",JanSales!$J:$J,-1)-1,1)

I am unsure what you mean by the "Refers to: field.."

 

[apo]

On the menu Strip.. click the Formulas Tab then the "Name Manager" option..

You will see the lower field in the popup is "Refers to:"

 

[EdStockton]

apo,

I did not know what to do with the OFFSET formula so I went to cell D30 on the JanSales worksheet and typed it in and received a value error message. Apparently, I did wrong. Where should I put that formula?