Link to home
Start Free TrialLog in
Avatar of Padraig O'Dea
Padraig O'Dea

asked on

Simple Excel re-arrange data. (I can work in access too).

Hi,
This is breaking my heart and should be much easier!
I have 10,000 rows in a sheet.

Current FORMAT
==============
John      Red
John      Blue
John      Grey
Paul      Black
Jane      Green
Jane         Red

Required Format
==============
John  Red Blue Grey
Paul  Black
Jane   Green  Red

Any help out there?  I have spend almost two hours on this simple matter!
EENYCI1001.xlsx
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Please find attached. I used this guide
https://www.extendoffice.com/documents/excel/3153-excel-concatenate-if-same-value.html
You can also use VBA
Sub ConcatenateCellsIfSameValues()
    Dim xCol As New Collection
    Dim xSrc As Variant
    Dim xRes() As Variant
    Dim I As Long
    Dim J As Long
    Dim xRg As Range
    xSrc    = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
    Set xRg = Range("D1")
    On Error Resume Next
    For I = 2 To UBound(xSrc)
        xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
    Next I
    On Error GoTo 0
    ReDim xRes(1 To xCol.Count + 1, 1 To 2)
    xRes(1, 1) = "No"
    xRes(1, 2) = "Combined Color"
    For I = 1 To xCol.Count
        xRes(I + 1, 1) = xCol(I)
        For J = 2 To UBound(xSrc)
            If xSrc(J, 1) = xRes(I + 1, 1) Then
                xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 2)
            End If
        Next J
        xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
    Next I
    Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
    xRg.NumberFormat = "@"
    xRg = xRes
    xRg.EntireColumn.AutoFit
End Sub

Open in new window

Combined.xlsx
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Sub macro1()

For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    Set c = Range("A" & Idx)
    If c.Offset(-1) = c Then
        Range(c.Offset(, 1), c.End(xlToRight)).Copy c.Offset(-1).End(xlToRight).Offset(, 1)
        c.EntireRow.Delete
    End If
Next
End Su

Open in new window

Regards
Avatar of Padraig O'Dea

ASKER

Thanks folks,

Unfortunately my VBA i not great.  Although, I am currently trying to tweak your solutions.
The data that I provided was a simplified very of the live data which is simple too.
I attach the actual live data.

The source data is in the first sheet.
The two simple requirements are shown in the second and third sheet.  (Yellow & Orange colours may clarify).

Any chance that you could tweak your script to produce results similar to "Requirement 1" and "Requirement 2".

Thanks for you time and efforts.
Padraig_Contacts_10012017.xlsx
See attached formula driven solution.

Sheet2 has solution:

1) two pivot tables with only one column each to get a list of unique values from each column.
2) row 1 incrementing number to cover the number of different options per value (name).
3) columns B to H formula to extract each option per value (name). This relies on all entries for each value being listed together; not necessarily sorted alphabetically. The pivot will then sort the values.

Hope that helps
Thanks
Rob H
EENYCI1001.xlsx
Did you look at attached file in 41954977?
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to ROb,Shaun,Rgonzo1971, Aikimark.

This was a real eye-opener.  All is now resolved.
I had assumed (dangerous word) that this task was going to be a simple 10 minutes of manipulation.
But I got stuck for several hours.

I will  try the Access command "dconcat" later - the Patrick Matthews one.
(I think I came across this guy before - very helpful stuff).

Thanks again ,all.