Merge Duplicates from Source Worksheet into Main Worksheet

Craig Peter DG

New Member
Joined
Sep 10, 2016
Messages
19
Office Version
  1. 2013
This is rather difficult to explain the problem folks, hope someone can understand my problem.

I have a table of Source Data with Number ID's running down Column A, some of these numbers (alot of them) are duplicates because that is how the source data I am pulling from was entered. One patient may have reported MULTIPLE symptoms but there was only 5 Symptom columns. Obviously the Source Data dbase only allowed for 5 columns across the page next to each ID number. So, source data contains DUPLICATE ID's with Unique Symptoms. NB: The symptoms for each patient ID are not duplicated, only the patient ID is duplicated, down column A. as in this picture (I have identified the duplicates with conditional formatting color red.
1612502858665.png


Problem is, my data table which is where I am consolidating all the source data, currently only has the patient ID listed once, yet, the source data may contain MULTIPLE entries against that patient ID number. (this shows my main data table with only unique patient ID's currently entered.)
1612503184287.png


I need a workaround that will allow me to bring the duplicate patient ID's with all their symptoms, from the source data into my main data worksheet. I do not want MORE THAN 5 columns of symptoms in my main data worksheet as it will be too wide and I am going to PIVOT table the final data.

Is this a macro solution or a function solution? I am not sure how to extract every duplicated patient ID from the source data into the main data, or a better word would be to MERGE i suppose.
Thanks everyone, sorry for the long problem.
Craig
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Craig Peter DG

New Member
Joined
Sep 10, 2016
Messages
19
Office Version
  1. 2013
This is rather difficult to explain the problem folks, hope someone can understand my problem.

I have a table of Source Data with Number ID's running down Column A, some of these numbers (alot of them) are duplicates because that is how the source data I am pulling from was entered. One patient may have reported MULTIPLE symptoms but there was only 5 Symptom columns. Obviously the Source Data dbase only allowed for 5 columns across the page next to each ID number. So, source data contains DUPLICATE ID's with Unique Symptoms. NB: The symptoms for each patient ID are not duplicated, only the patient ID is duplicated, down column A. as in this picture (I have identified the duplicates with conditional formatting color red.
View attachment 31322

Problem is, my data table which is where I am consolidating all the source data, currently only has the patient ID listed once, yet, the source data may contain MULTIPLE entries against that patient ID number. (this shows my main data table with only unique patient ID's currently entered.)
View attachment 31323

I need a workaround that will allow me to bring the duplicate patient ID's with all their symptoms, from the source data into my main data worksheet. I do not want MORE THAN 5 columns of symptoms in my main data worksheet as it will be too wide and I am going to PIVOT table the final data.

Is this a macro solution or a function solution? I am not sure how to extract every duplicated patient ID from the source data into the main data, or a better word would be to MERGE i suppose.
Thanks everyone, sorry for the long problem.
Craig

I'm now wondering if there is a way to VLOOKUP duplicated patient ID's, and if there is duplicates, bring their data over into the main worksheet on separate lines, co ie, create new rows each time VLOOKUP finds a duplicate in the source data and bring those 5 Symptom columns with it........
 

Craig Peter DG

New Member
Joined
Sep 10, 2016
Messages
19
Office Version
  1. 2013
In hindsight, what I should have done at the beginning of creating my data worksheet, was use the Biggest and most comprehensive Source Data worksheet as my basis of preparing and collating the information. I now realise this would have been a much more efficient way to extrapolate, lookup and collate the info into 1 single viewer friendly dataset.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
Hi Craig,

Not too sure about a formula but this macro will do the job (just change rng1 and rng2 to suit):

VBA Code:
Option Explicit
Sub Macro4()

    'https://www.mrexcel.com/board/threads/merge-duplicates-from-source-worksheet-into-main-worksheet.1160787

    Dim rng1 As Range, rng2 As Range
    Dim rngCellRng1 As Range, rngCellRng2 As Range
    Dim i As Long
    Dim strTemp As String
    Dim varTemp As Variant
    
    Set rng1 = Range("A55:A58") 'Range that contains unique id's. Change to suit.
    Set rng2 = Range("A5:A11") 'Range that contains duplicated id's. Change to suit.
    
    Application.ScreenUpdating = False
    
    For Each rngCellRng1 In rng1
        If Len(rngCellRng1) > 0 Then
            For Each rngCellRng2 In rng2
                If rngCellRng2.Value = rngCellRng1.Value Then
                    i = 1
                    Do Until Len(rngCellRng2.Offset(0, i)) = 0
                        strTemp = IIf(Len(strTemp) = 0, rngCellRng2.Offset(0, i), strTemp & "|" & rngCellRng2.Offset(0, i))
                        i = i + 1
                    Loop
                End If
            Next rngCellRng2
            If Len(strTemp) > 0 Then
                i = 1
                For Each varTemp In Split(strTemp, "|")
                    rngCellRng1.Offset(0, i).Value = varTemp
                    i = i + 1
                Next varTemp
            End If
            strTemp = ""
        End If
    Next rngCellRng1

    Application.ScreenUpdating = True

    MsgBox "Unique ID's have now been populated.", vbInformation
    
End Sub

Regards,

Robert
 

Watch MrExcel Video

Forum statistics

Threads
1,128,207
Messages
5,629,294
Members
416,384
Latest member
frsamiee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top