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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
cyberkiwiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.