Merging slightly similar records

  • Thread starter Ken DeYoung - Educational Consultant
  • Start date
K

Ken DeYoung - Educational Consultant

I am trying to consolidate records in excel or access whichever will turn out
to be easier. The problem is that these records come from a number of
diffeent databases. They almost all have similar column headings but some
duplicate info and some not. I am looking to consolidiate the simlar unique
IDs eliminating duplicate info for some colums but not for all.

Below is an example of a before and after scenerio. I am assuming that this
will require macros but I am not very good at the coding.

Format I recieve the data in

ID Name Term Test Score
35615 Mike Ramos Fall Math 125
35615 Mike Ramos Winter Math 115
35615 Mike Ramos Fall Reading 125

Final outcome desired
ID Name Term1 Test1 Score1 Term2 Test2 score2 Term3
Test3.....
35615 Mike R. Fall Math 125 Winter Math 115
Fall 125

It does not have to be as spread out as that but the idea is taking multiple
data entries for the same unique ID and make one entry.

If I was only dealing with a few entries i could do it manually but I am
working with a couple thousand students each of which have at least 6 data
lines with at least . Thanks in advance for any assistance in this endevor.

Ken DeYoung
 
D

Don Guillett

I think I would create a macro that made a list of the unique items using
data>filter>advanced filter for items 1 & 2 and then use that list to create
a macro to append columns 3:5 as they occur to the next available column for
that person.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Ken DeYoung - Educational Consultant"
 
K

Ken DeYoung - Educational Consultant

First of all, thanks for your prompt reply. Next what you say makes sense
however my main problem is that I am not sure how to begin coding such a
macro. Are you able to provide an example using my simplified example? If I
had a sample i could then modify it as needed to handle the larger data set.
Again thanks for your help

Ken
 
D

Don Guillett

The macro recorder is your friend. Try the first part first.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Ken DeYoung - Educational Consultant"
 
D

Don Guillett

Here is one I did recently that is somewhat similar. Modify to suit

Sub getcountriesinonecell()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range(Cells(1, "d"), Cells(lr, "e")).ClearContents
Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), Unique:=True

dlr = Cells(Rows.Count, "d").End(xlUp).Row
For Each x In Range("d2:d" & dlr)
ms = ""
With Range("a1:a" & lr)
Set c = .Find(x)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If InStr(ms, c.Offset(, 2)) < 1 Then
ms = ms & "," & c.Offset(, 2)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Cells(x.Row, 5) = Right(ms, Len(ms) - 1)
Next
End Sub
 
Top