Combine multiple Rows and 2 columns into 1 row

Hello,
I think this should be an easy problem but I just cannot figure out how to do it.

In column A I have a Name, in column B a transaction code and in column C I have a date.

There are multiple encounters for each person. I was wondering how do i combine the rows to have the unique name, with the transaction code and the date of the transaction in the next column and so on. Column A is a unique number, Column B is text and Column C is a date...Here is how the table is set up....
A              B         C
1001      G01      1/8/2009
1001      992      1/22/2009
1001      99      2/19/2009
1001      99      3/12/2009
1002      99      1/6/2009
1002      99      1/12/2009
1002      99      3/25/2009
1002      99      4/8/2009
1004      99      1/13/2009
1004      G01      1/25/2009

I am wondering how to get

1001      99        date 1    next transaction    next date

Can anyone help me out..I would love any advice. Thanks




GarrickB28Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Hi there,

This will work if your data start from A1
Sub Q_EE()
Dim ws As Worksheet
Dim rWork As Range
Dim rUsed As Range
Dim rTarget As Range
Set ws = ActiveWorkbook.ActiveSheet
ws.UsedRange.Sort ws.UsedRange.Cells(1, 1), xlAscending
Set rUsed = Intersect(ws.UsedRange, ws.Columns(1))
Set rUsed = rUsed.Resize(rUsed.Rows.Count - 1).Offset(1, 0)
Set rTarget = rUsed.Cells(1, 1).Offset(-1, 3)
Dim i As Long
Dim j As Long
i = rUsed.Row
j = i + rUsed.Rows.Count - 1
While i <= j
Set rWork = ws.Cells(i, 1)
If rWork.Value = rWork.Offset(-1).Value Then
    rWork.Offset(0, 1).Resize(1, 2).Copy rTarget
    rWork.EntireRow.Delete
    Set rTarget = rTarget.Offset(, 2)
    j = j - 1
Else
    Set rTarget = rWork.Offset(, 3)
    i = i + 1
End If
Wend
End Sub

Open in new window

0
 
Jim P.Commented:
You are looking to output this on a report? An Excell SS?

This would really need a VBA function to do smoothly. But it wouldn't be designed for input or editing data.
0
 
GarrickB28Author Commented:
It does not have to be, I had it in access, I tried to do a cross-tab query, but I could only get every date going cross the column headers with the transactions filled in.
0
 
JuniorMemberCommented:

Do you wish the output to be like:

A              B         C                 D
1001      G01      1/8/2009      1001      G01      1/8/2009      992      1/22/2009
1001      992      1/22/2009     1001      992      1/22/2009      99      2/19/2009
1001      99      2/19/2009      1001      99      2/19/2009      99      3/12/2009
1001      99      3/12/2009      1001      99      3/12/2009
1002      99      1/6/2009      1002      99      1/6/2009      99      1/12/2009
1002      99      1/12/2009      1002      99      1/12/2009      99      3/25/2009
1002      99      3/25/2009      1002      99      3/25/2009      99      4/8/2009
1002      99      4/8/2009      1002      99
1004      99      1/13/2009      1004      99      1/13/2009      G01      1/25/2009
1004      G01      1/25/2009    1004      G01      1/25/2009
0
 
GarrickB28Author Commented:
I only need a unique value in column A, then alternating transaction and date for as many occurences. Thanks I know this is a weird question, and I am very new to this.


A              B         C                   D             E            F                G             H
1001      G01      1/8/2009       1001       G01      1/8/2009      992      1/22/2009
1002      99         1/6/2009       1002         99        1/6/2009      
1004      99         1/13/2009      1004      99         1/13/2009      G01      1/25/2009
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.