0

I have two Sheets in Excel. For this example, Sheet 1 and Sheet 2.

Sheet 1: 100 records with Name, Location, Gender

Sheet 2: 100 records with Name, Location, Age

The sources of the data are not synchronized, therefore, I have data that exist in Sheet 1 that doesn't exist in Sheet 2, I also have data in Sheet 2 that doesn't exist in Sheet 1 and I have data that exists in both sheets. So, there may be names in Sheet 1 that are not in Sheet 2 and vice versa.

I'm looking for an automated way to consolidate both sheets into a third sheet and populate the Name & Location and where available Age and Gender. I know this would be easy to do in MS Access, but due to my audience, I need to keep this in Excel and I'm a complete noob to VBA :S

What I have done: On sheet 3, I have made formulas to extract the data from Sheet 1 and in Sheet 2 I have added a field ("X"), which the formula will label the field with "1" if the field is not present in Sheet 1 and will label with "0" if the field is present in Sheet 1. In my mind, I'm looking for a way that will state: "If field "X" in Sheet 2 = "1", then extract that data and append it to Sheet 3, but am unsure on how to do that :(

6
  • 2
    Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?. – DavidPostill Jun 23 '15 at 18:48
  • David I apologize. I haven't used code for this, as I've only gone as far as the "Hello World" tutorial. What I have done: On sheet 3, I have made formulas to extract the data from Sheet 1 and in Sheet 2 I have added a field, which the formula will label the field with "1" if the field is not present in Sheet 1 and will label with "0" if the field is present in Sheet 1. In my mind, I'm looking for a way that will state: "If field "X" in Sheet 2 = "1", then extract that data and append it to Sheet 3, but am unsure on how to do that :( Is this still to general? – stemar805 Jun 23 '15 at 19:08
  • try search for megre tables excel and you will find something like this: digdb.com/excel_add_ins/join_merge_tables_lists And now you just need to modify the code. (Use concat of two fields for lookup) – befzz Jun 23 '15 at 19:29
  • Maybe convert them to tables and then use formulas? I don't know, this would be easy with VBA. Search superuser for consolidating data with vba, I'm sure you'll find something. – Raystafarian Jun 23 '15 at 19:30
  • @Raystafarian, I did that search & found @Ajedi32 provided this answer two and a half years ago: Go to the data tab on the ribbon, click "From Other Sources", and "From Microsoft Query". Then click Excel Files, select the file that you are currently working in & click okay. Then, hit cancel & when promoted on whether you want to continue editing in Microsoft Query, hit "Yes". From here you can click the SQL button & write a custom SQL Query on any sheet in the spreadsheet. In my case: SELECT * FROM 'Sheet1$'` 'Sheet1$' UNION SELECT * FROM 'Sheet2$' `'Sheet2$'`` – stemar805 Jun 24 '15 at 12:08
0

this would require a not so difficult VBA script.

Here is the pseudo code

Create Array that contains All Names from Sheet B
For i = 0 to CellFromACount
 IF A.Name is not in ArrayB
   SheetB.AddLine (Item)
Next

In order the write the code, follow some tutorial by searching Excel vba tutorial on a search engine. Example here: VBA Tutorial

The things you need to do are:

  1. Array of strings
  2. Get Value From Cell
  3. For Loop
  4. If condition
  5. Add new line at bottom of the sheet
5
  • Thank you for this, much appreciated. Hopefully with this guidance, I know what to look for and can learn how to "fish". – stemar805 Jun 24 '15 at 12:16
  • 1
    This is not an answer. It does not provide instructions or code on how to accomplish the task. It references outside material without providing a summary. And it suggests the user google for answers. – Raystafarian Jun 24 '15 at 12:25
  • @Raystafarian, Agreed this is not an answer, but the person told me what type of code I need to accomplish the task. I was showing appreciation for them pointing me in the right direction. :) – stemar805 Jun 24 '15 at 13:34
  • Actualy, it does provides solution on how to accomplish the task and a summary of everything he has to do to have a solution. I don't think the goal was to write a 10 pages tutorial as an answer – Xavier Jun 24 '15 at 14:10
  • @Raystafarian It does provide help and the correct path to an answer. Even though it doesnt' help for future reference about the "wiki" aspect of StackExchange, it still helps the user giving him the right path to follow, for something that simple. Sometimes, people can just figure it out by themselves :) – Gil Sand Jun 24 '15 at 14:30
0

This should work. Put in in a module:

Public Sub sheetmerge()
Dim wkb As Workbook
Dim wks, wks1, wks2 As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
Set wks2 = wkb.Sheets(3)
emptyRow1 = False
i = 1
k = 1
' Loops while there is data on sheet 1 column 1
While emptyRow1 = False
    found = False
    ' extracts the name from column 1, row i
    name1 = wks.Cells(i, 1)
    If name1 <> "" Then
        'As it is not empty then loop through Sheet2 names
        emptyRow2 = False
        j = 1
        While emptyRow2 = False
            name2 = wks1.Cells(j, 1)
            If name2 <> "" Then
                'As name2 is not empty then compare name and name 2
                If name1 = name2 Then
                    'If name1 is the same as name2 then put the data on Sheet3
                    wks2.Cells(k, 1) = name1
                    wks2.Cells(k, 2) = wks.Cells(i, 2)
                    wks2.Cells(k, 3) = wks.Cells(i, 3)
                    wks2.Cells(k, 4) = wks1.Cells(j, 3)
                    found = True
                    k = k + 1
                    'stop looping sheet2 by setting emptyrow2 to True
                    emptyRow2 = True
                End If
            Else
                'name2 is empty, then stop looping sheet2
                emptyRow2 = True
            End If
            j = j + 1
        Wend
    Else
        'name1 is empty then stop looping Sheet1
        emptyRow1 = True
    End If
    'if after looping there was no coincidence of names then put the data from Sheet1
    If found = False Then
        If name1 <> "" Then
            wks2.Cells(k, 1) = name1
            wks2.Cells(k, 2) = wks.Cells(i, 2)
            wks2.Cells(k, 3) = wks.Cells(i, 3)
            k = k + 1
        End If
    End If
    i = i + 1
Wend
'This area loops through Sheet2 seeking for coincidences
emptyRow2 = False
j = 1
While emptyRow2 = False
    found = False
    name2 = wks1.Cells(j, 1)
    If name2 <> "" Then
        emptyRow1 = False
        i = 1
        While emptyRow1 = False
            name1 = wks.Cells(i, 1)
            If name1 <> "" Then
                If name2 = name1 Then
                    found = True
                    emptyRow1 = True
                End If
            Else
                emptyRow1 = True
            End If
            i = i + 1
        Wend
    Else
        emptyRow2 = True
    End If
    ' If there wasn't a coincidence it means this name is in sheet2 but is not on Sheet1 so, put it in Sheet3.
    If found = False Then
        wks2.Cells(k, 1) = name2
        wks2.Cells(k, 2) = wks1.Cells(j, 2)
        wks2.Cells(k, 4) = wks1.Cells(j, 3)
        k = k + 1
    End If
    j = j + 1
Wend
End Sub

It loops through rows on sheet1 putting them on sheet3 putting a fourth column if is there coincidence with sheet2.

After that it loops through sheet2 putting on sheet3 rows not found on sheet1.

4
  • Thank you very much. I found an answer yesterday, but will check to see if this works sometime today (am on a deadline). It's always good to know more than one way of doing something! Thank you again for your time and help! – stemar805 Jun 24 '15 at 12:14
  • This did a great job, but did add duplicates. I really need to learn this code. Thanks a million! – stemar805 Jun 25 '15 at 17:17
  • I'm pretty sure that the below is the part that identifies the fields. Can you advise how I can/supposed to change that so that it can look at the title on the second row? My sheets actually have 135 columns and the information is scattered :( wks2.Cells(k, 1) = name1 wks2.Cells(k, 2) = wks.Cells(i, 2) wks2.Cells(k, 3) = wks.Cells(i, 3) wks2.Cells(k, 4) = wks1.Cells(j, 3) – stemar805 Jun 25 '15 at 17:24
  • I added some comments to the code to make it more clear. If your information start on second row then change the lines i=1and j=1to ì=2 and j=2. The code supposes the data on both Sheets is in columns 1,2 and 3. It could be very difficult to search for it on 135 columns. – jcbermu Jun 26 '15 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.