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

I think I would create a macro that made a list of the unique items using
datafilteradvanced 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

"Ken DeYoung - Educational Consultant"
soft.com wrote in message
...
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

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

"Don Guillett" wrote:

I think I would create a macro that made a list of the unique items using
datafilteradvanced 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

"Ken DeYoung - Educational Consultant"
soft.com wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken DeYoung - Educational Consultant"
soft.com wrote in message
...
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

"Don Guillett" wrote:

I think I would create a macro that made a list of the unique items using
datafilteradvanced 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

"Ken DeYoung - Educational Consultant"
soft.com wrote in
message
...
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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
The macro recorder is your friend. Try the first part first.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken DeYoung - Educational Consultant"
soft.com wrote in
message ...
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

"Don Guillett" wrote:

I think I would create a macro that made a list of the unique items
using
datafilteradvanced 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

"Ken DeYoung - Educational Consultant"
soft.com wrote in
message
...
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