Adding Identifier to Spreadsheet in order to Categorize

[brandonmcg]

I am not sure this is going to make sense or not.
I have a list of loans where my report lists the borrowers in a order.
If you notice there are multiple of the same loan number but there are more than one borrower and they have different Tax ID's.
How do I get it to look like the example at the bottom of the page? I need all common borrowers with the same loan number on one line with their matching tax ID.

Sample Report

Loan Number	BORROWER	TAX_ID
7901359	Bob Smith	123456789​
7901359	Mary Smith	124342333​
7901426	Tom Jones	125227877​
7901545	Angus Young	126113421​
7901839	Steven Tyler	126998965​
7901914	Mary Kluger	127884509​
7902076	Sheldon Cooper	128770053​
7902076	Amy Cooper	129655597​
7902293	Leonard Hofstader	130541141​
7902293	Kelly Hofstader	131426685​
7902293	Baby Hofstader	132312229​
7902347	Stuary Bloom	133197773​
7902436	Raj Koothreppali	134083317​
7902568	Leslie Winkle	134968861​
7903704	Blake Shelton	135854405​
7903858	Sammy Hagar	136739949​
7904087	Samantha Fox	137625493​
7904087	Howard Wolowitz	138511037​
7904087	Bernadette Wolowitz	139396581​
7904130	Emily Sweeney	140282125​
7904269	Sara Bareilles	141167669​
7904277	Alan Parsons	142053213​
7904277	Jill Parsons	142938757​
7904873	David Lee Roth	143824301​
7904873	Wife Lee Roth	144709845​
7905257	Alice Cooper	145595389​

How do I get it to look like this?

Loan Number	BORROWER 1	TAX_ID 1	Borrower 2	Tax ID 2	Borrower 3	Tax ID 3
7901359	Bob Smith	123456789	Mary Smith	124342333
7901426	Tom Jones	125227877
7901545	Angus Young	126113421
7901839	Steven Tyler	126998965
7901914	Mary Kluger	127884509
7902076	Sheldon Cooper	128770053	Amy Cooper	129655597
7902293	Leonard Hofstader	130541141	Kelly Hofstader	131426685	Baby Hofstader	132312229
7902347	Stuary Bloom	133197773
7902436	Raj Koothreppali	134083317
7902568	Leslie Winkle	134968861
7903704	Blake Shelton	135854405
7903858	Sammy Hagar	136739949
7904087	Samantha Fox	137625493	Howard Wolowitz	138511037
7904087	Bernadette Wolowitz	139396581
7904130	Emily Sweeney	140282125
7904269	Sara Bareilles	141167669
7904277	Alan Parsons	142053213	Jill Parsons	142938757
7904873	David Lee Roth	143824301	Wife Lee Roth	144709845
7905257	Alice Cooper	145595389

 

[Toadstool]

Hi Brandonmcg,

Does this do what you want?

Brandonmcg-2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Loan Number	BORROWER	TAX_ID		Loan Number	BORROWER 1	TAX_ID 1	Borrower 2	Tax ID 2	Borrower 3	Tax ID 3
2	7901359	Bob Smith	123456789		7901359	Bob Smith	123456789	Mary Smith	124342333
3	7901359	Mary Smith	124342333		7901426	Tom Jones	125227877
4	7901426	Tom Jones	125227877		7901545	Angus Young	126113421
5	7901545	Angus Young	126113421		7901839	Steven Tyler	126998965
6	7901839	Steven Tyler	126998965		7901914	Mary Kluger	127884509
7	7901914	Mary Kluger	127884509		7902076	Sheldon Cooper	128770053	Amy Cooper	129655597
8	7902076	Sheldon Cooper	128770053		7902293	Leonard Hofstader	130541141	Kelly Hofstader	131426685	Baby Hofstader	132312229
9	7902076	Amy Cooper	129655597		7902347	Stuary Bloom	133197773
10	7902293	Leonard Hofstader	130541141		7902436	Raj Koothreppali	134083317
11	7902293	Kelly Hofstader	131426685		7902568	Leslie Winkle	134968861
12	7902293	Baby Hofstader	132312229		7903704	Blake Shelton	135854405
13	7902347	Stuary Bloom	133197773		7903858	Sammy Hagar	136739949
14	7902436	Raj Koothreppali	134083317		7904087	Samantha Fox	137625493	Howard Wolowitz	138511037	Bernadette Wolowitz	139396581
15	7902568	Leslie Winkle	134968861		7904130	Emily Sweeney	140282125
16	7903704	Blake Shelton	135854405		7904269	Sara Bareilles	141167669
17	7903858	Sammy Hagar	136739949		7904277	Alan Parsons	142053213	Jill Parsons	142938757
18	7904087	Samantha Fox	137625493		7904873	David Lee Roth	143824301	Wife Lee Roth	144709845
19	7904087	Howard Wolowitz	138511037		7905257	Alice Cooper	145595389
20	7904087	Bernadette Wolowitz	139396581
21	7904130	Emily Sweeney	140282125
22	7904269	Sara Bareilles	141167669
23	7904277	Alan Parsons	142053213
24	7904277	Jill Parsons	142938757
25	7904873	David Lee Roth	143824301
26	7904873	Wife Lee Roth	144709845
27	7905257	Alice Cooper	145595389
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MIN($A$2:$A$9999)
F2:K27	F2	=IF($E2="","",IFERROR(INDEX($B$2:$C$9999,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=$E2)),INT((COLUMN()-COLUMN($E$1)+1)/2)),ISODD(COLUMN()-COLUMN($F$1))+1),""))
E3:E27	E3	=IFERROR(AGGREGATE(15,6,$A$2:$A$9999/(($A$2:$A$9999>E2)*($A$2:$A$9999<>"")),1),"")

 

[mumps]

VBA approach:

Sub brandon()
    Application.ScreenUpdating = False
    Dim arr As Variant, dic As Object, rng As Range, fVisRow As Long, cnt As Long, x As Long, y As Long: y = 1
    Dim LastRow As Long
    arr = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr, 1)
        If Not dic.Exists(arr(i, 1)) Then
            dic.Add arr(i, 1), Nothing
            With Range("A1")
                .CurrentRegion.AutoFilter 1, arr(i, 1)
                cnt = [subtotal(103,A:A)] - 1
                If cnt > 1 Then
                    fVisRow = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
                    For Each rng In Range("B" & fVisRow, Range("A" & Rows.Count).End(xlUp)).Offset(1).SpecialCells(xlCellTypeVisible)
                        rng.Resize(, 2).Copy Cells(fVisRow, Columns.Count).End(xlToLeft).Offset(0, 1)
                        Rows(rng.Row).Delete
                    Next rng
                End If
            End With
        End If
    Next i
    lcol = ActiveSheet.UsedRange.Columns.Count
    For x = 2 To lcol Step 2
        Cells(1, x).Resize(, 2) = Array("BORROWER " & y, "TAX ID " & y)
        y = y + 1
    Next x
    Columns.AutoFit
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[brandonmcg]

This works perfectly. I do appreciate your help!!!

 

[Toadstool]

This works perfectly. I do appreciate your help!!!

You're welcome!
Did you try @mumps VBA solution?

 

[brandonmcg]

We are going to try the VBA solution today or tomorrow.

 

[brandonmcg]

I am trying the MACRO and it is freezing my Excel. I will continue to try to get it to run.

 

[brandonmcg]

I am getting this error. I changed the tab names back and forth between sheet 1 and sheet 2.
View attachment 43849

 

[brandonmcg]

I got it to work flawlessly. Thank you for your help!

 

[mumps]

You are very welcome.