Merging lists

[Pilule]

I have a question about merging several lists but I think it would be easier to explain if I can show an example. Someone suggested I post a sample on Dropbox. How would I post a link to the file?

Thanks

 

[hiker95]

I have a question about merging several lists but I think it would be easier to explain if I can show an example. Someone suggested I post a sample on Dropbox. How would I post a link to the file?

Thanks

Pilule,

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


Once you have created the uploaded file, copy the address line and then paste it back here!!

 

[Pilule]

Here’s the file.
The three worksheet, Main, Update 1 and Update 2 have the same names in the same order. The letter in column A is a rating, R, G and Y. A want to add the rating from Update 1 in the Main sheet and then I want to add the ratings from Update 2 in the Main sheet.
When I add data to the main list, I want the data that’s already there to remain there.
I don’t know how I could do that. I watched a lot of videos, on Youtube, but none of them does what I want to do.

Thanks



https://www.dropbox.com/s/jp0m1ngy43sf2pa/Listes.xls?dl=0

 

[Pilule]

I posted my file.

 

[hiker95]

The three worksheet, Main, Update 1 and Update 2 have the same names in the same order. The letter in column A is a rating, R, G and Y. A want to add the rating from Update 1 in the Main sheet and then I want to add the ratings from Update 2 in the Main sheet.
When I add data to the main list, I want the data that’s already there to remain there.

Pilule,

Would there ever be more than one rating in column A in worksheet Main, from worksheets Update 1, and, Update 2?

If so, how should they be displayed?

 

[hiker95]

Pilule,

Here is a macro solution for you to consider.

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 UpdateMain()
' hiker95, 10/12/2017, ME1026657
Application.ScreenUpdating = False
Dim wm As Worksheet, w1 As Worksheet, w2 As Worksheet
Dim lr As Long, r As Range, a As String
Set wm = Sheets("Main")
Set w1 = Sheets("Update 1")
Set w2 = Sheets("Update 2")
With wm
  lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  .Range("A1:A" & lr).ClearContents
  For Each r In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    a = ""
    If r.Offset(, -1) = vbEmpty Then
      If w1.Range("A" & r.Row).Value <> "" Then
        a = a & w1.Range("A" & r.Row).Value
      End If
      If w2.Range("A" & r.Row).Value <> "" Then
        a = a & w2.Range("A" & r.Row).Value
      End If
    End If
    If a <> "" Then
      r.Offset(, -1).Value = a
    End If
  Next r
  .Columns(1).AutoFit
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the UpdateMain macro.

 

[Pilule]

There shouldn't be more than one rating per cell. Somebody suggested to use the ''&'' operator, sort of an "and" function for text. It would go this way:

In cell "A1" of the main sheet, the formula would be something like: =update1'A1&update2'A2&update3'A3 and so on.

The problem with that would be that it could put more than one value in a cell. I think that if I could put an ''or'' operator for text instead it could work. I don't know if that exist and what the syntax of it would be. It would go like this:

In cell "A1" of the main sheet, the formula would be something like: =update1'A1 "or" update2'A1 "or" update3'A1 and so on.

 

[hiker95]

There shouldn't be more than one rating per cell. Somebody suggested to use the ''&'' operator, sort of an "and" function for text. It would go this way:

In cell "A1" of the main sheet, the formula would be something like: =update1'A1&update2'A2&update3'A3 and so on.

The problem with that would be that it could put more than one value in a cell. I think that if I could put an ''or'' operator for text instead it could work. I don't know if that exist and what the syntax of it would be. It would go like this:

In cell "A1" of the main sheet, the formula would be something like: =update1'A1 "or" update2'A1 "or" update3'A1 and so on.

Pilule,

You did not state in your original post that you were looking for a formula solution.

Maybe one of the MrExcel formula Guru's will be able to assist you.

 

[hiker95]

There shouldn't be more than one rating per cell.

Pilule,

Here is a new macro for you to consider that will do the above quote.

With the same instructions as my reply #6.

Sub UpdateMain_V2()
' hiker95, 10/12/2017, ME1026657
Application.ScreenUpdating = False
Dim wm As Worksheet, w1 As Worksheet, w2 As Worksheet
Dim lr As Long, r As Range
Set wm = Sheets("Main")
Set w1 = Sheets("Update 1")
Set w2 = Sheets("Update 2")
With wm
  lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  .Range("A1:A" & lr).ClearContents
  For Each r In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    If r.Offset(, -1) = vbEmpty Then
      If w1.Range("A" & r.Row).Value <> "" Then
        r.Offset(, -1).Value = w1.Range("A" & r.Row).Value
      End If
      If w2.Range("A" & r.Row).Value <> "" Then
        r.Offset(, -1).Value = w2.Range("A" & r.Row).Value
      End If
    End If
  Next r
  .Columns(1).AutoFit
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my reply #6.

Then run the UpdateMain_V2 macro.