Hi there:

The rows in Excel sheet like this:
IDNUM Lastname Firstname Institution COURSE Address
123 Harris Jane ABC college MINOR SOCIOLOGY 164 Firefox RD
123 Harris Jane ABC college Religion & Modern Thought 164 Firefox RD
123 Harris Jane WRS UNIVERSITY PSYCHOLOGY 164 Firefox RD
123 Harris Jane WRS UNIVERSITY Nutrition 164 Fireforx RD
234 Smith John CBD UNVERSTIY AESTHETIC PRINCIPLES & ELEMENTS 603 Parkway DR
234 Smith John CBD UNVERSTIY INTRODUCTION TO VISUAL CULTURE 603 Parkway DR

I am trying to combine the rows into a row for each idnum.
idnum Lastname firstname institution course address
123 Harris Jane ABC college MINOR SOCIOLOGY ABC college Religion & Modern Thought WRS UNIVERSITY PSYCHOLOGY WRS UNIVERSITY Nutrition 164 Fireforx RD
234 Smith John CBD UNVERSTIY AESTHETIC PRINCIPLES & ELEMENTS CBD UNVERSTIY INTRODUCTION TO VISUAL CULTURE 603 Parkway DR

The output will be each cell contains individual content and no concentrnate.

Can anyboday help me with this please?

Thanks,

Melissa

hi,

Is this a one time exersize?

What version of Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Perhaps you could also expalin the business case for this requirement, as there are occasions that a requirement can be met in a number of different ways

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

I am using Excel 2010 and it is not one time thing.

Each idnum could have up to 14 courses. One row for each idnum contains those info as above. I am tying to cobmine those rows to one row and each "data item" being in it's own cell so that a person merges this excel file to a word doc.

I created a macro to combine some columns to one row like:
123 Harris Jane ABC college WRS UNIVERSITY
234 Smith John CBD UNVERSTIY

I don't know how to deal with course and address column.

Thanks,

Melissa

That structure seem extremely confusing to me. Where does one institution data end and the next begin? You have to hunt for it!!! Even if this is for a Word document, it seems to me that tabular data is MUCH MUCH easier to assimilate than paragraphical data, as you are posing.

Nonetheless, do you have any code? Are you having problems with your code? Please post.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Sorry, I am trying to get the output like this
IDNUM LASTNAME FIRSTNAME INSTITUTION (UP TO 4 INSTITUTIONS) COURSE (COURSE1, COURSE2 UPTO COURSE14) ADDRESS
123 Harris Jane ABC college WRS UNIVERSITY | MINOR SOCIOLOGY(course1 from ABC college) Religion & Modern Thought(course2 from ABC college) PSYCHOLOGY (course1 from WRS university) Nutrition (course2 from WRS) 164 Firefox RD

234 Smith John CBD UNVERSTIY |AESTHETIC PRINCIPLES & ELEMENTS INTRODUCTION TO VISUAL CULTURE 603 Parkway DR

"|" means the end of institution data.

I copied someone's macro to generate
123 Harris Jane ABC college WRS UNIVERSITY
234 Smith John CBD UNVERSTIY

I don't know how to go further for course and address.
Here is the Macro:

Sub Merge()
Dim bSame As Boolean
Dim iCol As Integer
Dim lRow As Long
Dim rData As Range, R As Range
Dim vDataLine() As Variant
Dim vKey(1 To 3) As Variant
Dim wsFr As Worksheet, wsTo As Worksheet

Set wsFr = Sheets("Sheet1")
Set wsTo = Sheets("Sheet2")

wsTo.Cells.ClearContents

Set rData = wsFr.Range("A2:A" & wsFr.Cells(Rows.Count, 1).End(xlUp).Row)
ReDim vDataLine(1 To 4)
For iCol = 1 To 4
vDataLine(iCol) = wsFr.Cells(1, iCol).Value
Next iCol

For Each R In rData
bSame = True
For iCol = 1 To 3
vKey(iCol) = R.Offset(0, iCol - 1).Value
If vKey(iCol) <> vDataLine(iCol) Then bSame = False
Next iCol
If bSame = False Then
lRow = lRow + 1
wsTo.Range(Cells(lRow, 1).Address, Cells(lRow, UBound(vDataLine)).Address) = vDataLine
ReDim vDataLine(1 To 3)
For iCol = 1 To 3
vDataLine(iCol) = vKey(iCol)
Next iCol
End If
iCol = UBound(vDataLine) + 1
ReDim Preserve vDataLine(1 To iCol)
vDataLine(iCol) = R.Offset(0, 3).Value
Next R
lRow = lRow + 1
wsTo.Range(Cells(lRow, 1).Address, Cells(lRow, UBound(vDataLine)).Address) = vDataLine
End Sub

Then what happens if there are more that FOUR institutions or more than FOURTEEN courses?????

We really need to know.

This sort of structure is a BAD idea, IMNSHO!

I will look at the code you posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

So WHO is it that foisted these NEW requirements on you? PARENTHESES and PIPES?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

I'm looking at your REVISED example and I see inconsistency. Under what LOGIC are the PARENTHESES included or NOT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Parenthess / pipe is not included. This is the way I tried to explain the layout. Sorry to confuse you even more. The purpose is to send students the list of transfer credits have been granted so that there are limits for the number of institutions and the number of credits will be granted. The maximum number of institution is 4 and maximum courses is 14.

Melissa

Can anyone help with this please? I used to generate the results by SQL queries, but the trouble is performance issue because of so many join statements. I am trying to pull the data from database and manipluate the data on Excel.

Thanks,

Melissa

I need a USEABLE table of sample data. Please take your posted sample table and delimit the data with PIPE character, like

IDNUM|Lastname|Firstname|Institution|COURSE|Address

I will give it a try.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Quote:

The purpose is to send students the list of transfer credits have been granted so that there are limits for the number of institutions and the number of credits will be granted.

What is the purpose of have a single row per student?

Would it not accomplish the same purpose to produce

For Jane Harris

123 Harris Jane ABC college MINOR SOCIOLOGY 164 Firefox RD
123 Harris Jane ABC college Religion & Modern Thought 164 Firefox RD
123 Harris Jane WRS UNIVERSITY PSYCHOLOGY 164 Firefox RD
123 Harris Jane WRS UNIVERSITY Nutrition 164 Fireforx RD

For John Smith

234 Smith John CBD UNVERSTIY AESTHETIC PRINCIPLES & ELEMENTS 603 Parkway DR
234 Smith John CBD UNVERSTIY INTRODUCTION TO VISUAL CULTURE 603 Parkway DR

If I were doing it I would...

123 Jane Harris, 164 Firefox RD
234 John Smith, 603 Parkway DR

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

I have to agree with Skip in that this seems like a very confusing arrangement of the data you are converting to; especially since you are pushing the address to some arbitrary column. Why not at least move the Address column to immediately after the First Name column? If you do in fact need a single row per student for merging into a Word document, in addition to moving the Address to a constant column, consider adding a constant "delimiter" value before the first Course name in each row, such as "Courses:". It may even be useful to do that for the Institutions even though you will always know what column they start in.

Stepping off my soapbox, here's a solution that I think will give you what you're looking for. You'll need to add a reference to Microsoft Scripting Runtime to get access to the Dictionary class.