Vlookup for duplicate status

Question

I've been looking for different approaches to fix my problem, whether it's with vlookup, index, index /match but couldn't figure it out yet.

I'm trying to list the same co-tenants of the same apartment on the same line as shown in the picture:

Answer 1

A VBA approach

Sub list()

    Dim wb As Workbook, ws As Worksheet
    Dim iRow As Long, iHeaderRow As Long
    Dim sApt As String, sStatus, sName As String

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    iRow = 4  'start
    sApt = CStr(ws.Cells(iRow, 1))
    Do While Len(sApt) > 0

        sStatus = ws.Cells(iRow, 2)
        sName = ws.Cells(iRow, 3)

        If Not dict.exists(sApt) Then
            dict.Add sApt, ""
        End If

        If LCase(sStatus) = "main" Then
            dict(sApt) = sName & dict(sApt) ' add to front
        Else
            dict(sApt) = dict(sApt) & ";" & sName ' add to back
        End If

        iRow = iRow + 1
        sApt = CStr(ws.Cells(iRow, 1))

    Loop

    ' result header
    iHeaderRow = iRow + 1
    ws.Cells(iHeaderRow, 1) = "Apt"
    ws.Cells(iHeaderRow, 2) = "Main"
    iRow = iRow + 2

    ' result table
    Dim k As Variant, ar As Variant, n As Integer, m As Integer
    For Each k In dict.keys
        ws.Cells(iRow, 1) = k
        ar = Split(dict(k), ";")
        n = UBound(ar)
        ws.Cells(iRow, 2).Resize(1, n + 1) = ar
        If n > m Then m = n ' max for n
        iRow = iRow + 1
    Next

    ' complete header row
    For n = 1 To m
        ws.Cells(iHeaderRow, n + 2) = "Co-tenant"
    Next
    MsgBox "Done"

End Sub

Answer 2

You say ive been looking for different approaches , so it's only a small suggestion if you're using office365 then you can use the filter function for this.

Here is the formula:

=TRANSPOSE(FILTER($B$3:$B$9,$A$3:$A$9=D3))

Answer 3

Here's a method without the helper column:

To get "John" in cell B21, you can use an array formula* that will combine columns A and B so you can match both criteria at the same time (using "&"). The formula would look like this:

=INDEX($C$4:$C$8,MATCH($A21&B$20,$A$4:$A$8&$B$4:$B$8,0))

To get "Simon" in cell C21, you could just copy the previous one since the dollar signs will make sure that the lookup criterion is adjusting correctly.

To get "Alex" in D21, it's a bit more tricky since you are trying to get the 2nd match. A method to get a second match is detailed in this article. In this context, it would look like this:

=INDEX($C$4:$C$8,SMALL(IF($A21&$D20=$A$4:$A$8&$B$4:$B$8,ROW($A$4:$A$8)-ROW($A$4)+1),2))

*: Need to press Ctrl+Shift+Enter in older versions of Excel (2010 and earlier).

Answer 4

Presuming that your data are in columns A2:C? designate columns to the right as recipients of your rearranged data. Enter the exact same descriptions in their captions (in row 1) as are used in the Status column. I used columns D:H and the captions "Main" and "Co-Tenant" in all remaining columns. In this way, I had 4 columns for Co-Tenants. You need as many columns as there might be Co-Tenants as a maximum.

Now enter the formula below in the first cell in the new Main column (that was D2 in my example) and copy it to the entire range, in my example D2:H6. It's the same formula for all cells.

=IF(OFFSET($B2,COLUMN()-4,0)=D$1,OFFSET($B2,COLUMN()-4,1),"")

Observe that the 4 in my formula is the number of the column in which you enter the formula. In my test that was column D, the fourth column). Should you use another column please replace both occurrences of 4 with the number of the column you chose. The same goes for the formula's reference to D$1. The cell specifies the caption of the new Main column.

Now select the entire range with the formula (D2:H6 in my test), Copy and Paste Special > Values. This will replace all formulas with the values they generated. You can now delete columns B:C.

Select the entire range (I didn't delete B:C yet. So it was A2:H6 for me) and click Remove Duplicates on the Data tab. Specify column A for duplicates. This action will retain only the first row of each apartment ID deleting all others, especially those that appeared faulty after applying the formulas. If you didn't delete columns B:C yet they are definitely redundant now.