Join rows based on unique ID

Question

I have 32.000 rows with data. Some data are in a different place and I want to join them with something that I can apply to all rows and not manually. Each "group" have the same ID, in this example is "XPTO"

I have something like this now (but with more columns):

I want it to be like this:

The problem is that I need a clever way, because they are not always exactly like this example. Some of them have 10 rows with the same ID "XPTO" (example)

I am struggling with this =/ ty

What is the logic for merging row 3 with row 1 and not with row 2? — teylyn, Oct 18 '16 at 01:29
Its difficult to explain. Its IF (B1orC1orD1orE1 is not empty) AND (its the first row of XPTO that has B1orC1orD1orE1 not empty) AND (F1 is empty) THEN FIND a first row where (Index is XPTO) AND (B1orC1orD1orE1 is empty) and cut that row after F LETTER (F3,g4,h5 in this example) and paste into the first row found. This is because this data was scrapped and its always broken between column E and F. (sometimes scrapped first E,F,G,H and others first B,C,D,E). I hope I could explain =/ — Renato Pimpão, Oct 18 '16 at 01:58

score 0 · Answer 1 · edited May 23 '17 at 12:33

Here's how I would approach this.

1) From your comment, I understand that the logic is positional (the first one on the left (Casteloes de) goes with the first one on the right (R Dr Antonio) for the matching value in column A. If that is true, then I would insert a column where you start numbering sequentially, then Fill Down to get sequential numbers all the way to the end. This will help preserve the positional logic if you need to sort or rearrange your data. It will also help you with the logic of "first match", "second match", etc.

2) My next step would be to separate the two sets of data into separate tables/tabs (with the sequentially numbered column appearing in each) and use INDEX/MATCH. The recent answer here will help you with how to increment the match: Is there such thing as a VLOOKUP that recognises repeated numbers?

3) Alternative - this may even be easier, although you'll want to do extensive data checking to make sure nothing got screwed up. With the two tables from step 2, sort by any column with data in it, then delete the blank rows from each table. Then, sort each by the sequentially numbered column to return to the original order. At that point you may be able to just copy and paste. Check carefully for errors if you do this.

I am going to try. But do you think it also works with this example: http://i.imgur.com/ru6xAl4.png — Renato Pimpão, Oct 18 '16 at 12:07
I just updated with step 3 above. It's not bullet proof, but it may work. It should work fine with the examples you gave in your comments. — datakritter, Oct 18 '16 at 12:45

score 0 · Accepted Answer · answered Oct 19 '16 at 01:34

I am positive that the solution above given by CriketBird work, at least it has a good logic to solve it, but since I am a newbie in excel, I couldn't figure it out how to solve it that way.

So I solved it by using VBA in excel...(maybe I went too far for this simple problem, but it was my only option).

I will leave the code here if someone want it for a similar situation. (just select the first column and row your table starts and hit run)

Function Area(medico As String) As Integer

Do While countOk < 1

If medico = ActiveCell.Value Then
        ActiveCell.Offset(1, 0).Select
        rowCount = rowCount + 1
Else: countOk = 1
End If

Loop

Area = rowCount

End Function


Sub Teste()

Dim PaginaMedico As String
Dim totalrowCount As Integer
Dim rowCount As Integer
Dim countOk As Integer
Dim right As Integer
Dim left As Integer
Dim listaleft As New Collection
Dim listaright As New Collection


rowCount = 1
rowOk = 0
totalrowCount = 0
right = 0
left = 0


Do While ActiveCell.Value <> 0

PaginaMedico = ActiveCell.Value
rowCount = Area(PaginaMedico)
totalrowCount = totalrowCount + rowCount

Range("A" & (totalrowCount - (rowCount - 1))).Select

For i = ((totalrowCount + 1) - rowCount) To totalrowCount

If IsEmpty(Range("E" & (i)).Value) And IsEmpty(Range("F" & (i)).Value) Then
        Range("T" & (i)).Value = "Empty"
    ElseIf Not IsEmpty(Range("E" & (i)).Value) And Not IsEmpty(Range("F" & (i)).Value) Then
            Range("T" & (i)).Value = "Full"
        ElseIf Not IsEmpty(Range("E" & (i)).Value) And IsEmpty(Range("F" & (i)).Value) Then
                left = left + 1
                listaleft.Add i

            ElseIf IsEmpty(Range("E" & (i)).Value) And Not IsEmpty(Range("F" & (i)).Value) Then
                    right = right + 1
                    listaright.Add i


End If

Next i

If Not (right = left) Then
    Range("T" & totalrowCount).Value = "BOSTA"
    right = 0
    left = 0
End If

If listaleft.Count = listaright.Count Then

For i = 1 To listaleft.Count

    Range("F" & listaright(1) & ":" & "S" & listaright(1)).Cut     Range("F" & listaleft(1) & ":" & "S" & listaleft(1))
    listaright.Remove (1)
    listaleft.Remove (1)

Next i

End If

Set listaleft = New Collection
Set listaright = New Collection


Range("A" & (totalrowCount + 1)).Select


Loop


End Sub

Join rows based on unique ID

2 Answers2