One to Many Join/Transpose based on field in a column

O

onetomanyjoin

New Member
Joined
Jul 9, 2015
Messages
2
Hi All,

I'm trying to import data from Excel into ArcMap and I'm running across some issues with a database. Basically I need each building (row) to contain links to all drawing data. Different buildings may have different numbers of drawings.

Basically I want to convert something that looks like this:

BuildingDrawing
A1
A2
A3
A4
A5
B6
B7
B8
B9
C10
C11
D12
E13
E14
E15

<tbody>
</tbody>


Into something that looks like this:

Building Drawing1 Drawing2 Drawing3Drawing4 Drawing5
A12345
B6789
C1011
D12
E131415

<tbody>
</tbody>


The database is quite large so doing this manually is pretty much out. I do know the max number of columns I'd need per building by using a "COUNTIF" function so I know how many rows to title.

Thank you
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
I hope this is what you're after:


Excel 2012
ABCDEFGHIJKLMN
1BuildingDrawingDrawing 1Drawing 2Drawing 3Drawing 4Drawing 5Drawing 6Drawing 7  
2A1A12345    
3A2B6789     
4A3C1011       
5A4D12        
6A5E131415      
7B6FNO. 134-76f.2test      
8B7GhimedLO161718BOO+1  
9B8
10B9
11C10
12C11
13D12
14E13
15E14
16E15
17FNO. 1
18F34-76f.2
19Ftest
20Ghi
21Gmed
22GLO
23G16
24G17
25G18
26GBOO+1
Sheet65
Cell Formulas
RangeFormula
F2=IF(COLUMNS($F$2:F2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F3=IF(COLUMNS($F$2:F3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F4=IF(COLUMNS($F$2:F4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F5=IF(COLUMNS($F$2:F5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F6=IF(COLUMNS($F$2:F6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F7=IF(COLUMNS($F$2:F7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F8=IF(COLUMNS($F$2:F8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:F$1)-1),"")
F1{=IF(COLUMNS($F$2:F2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:F2),"")}
G2=IF(COLUMNS($F$2:G2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G3=IF(COLUMNS($F$2:G3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G4=IF(COLUMNS($F$2:G4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G5=IF(COLUMNS($F$2:G5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G6=IF(COLUMNS($F$2:G6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G7=IF(COLUMNS($F$2:G7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G8=IF(COLUMNS($F$2:G8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:G$1)-1),"")
G1{=IF(COLUMNS($F$2:G2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:G2),"")}
H2=IF(COLUMNS($F$2:H2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H3=IF(COLUMNS($F$2:H3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H4=IF(COLUMNS($F$2:H4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H5=IF(COLUMNS($F$2:H5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H6=IF(COLUMNS($F$2:H6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H7=IF(COLUMNS($F$2:H7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H8=IF(COLUMNS($F$2:H8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:H$1)-1),"")
H1{=IF(COLUMNS($F$2:H2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:H2),"")}
I2=IF(COLUMNS($F$2:I2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I3=IF(COLUMNS($F$2:I3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I4=IF(COLUMNS($F$2:I4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I5=IF(COLUMNS($F$2:I5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I6=IF(COLUMNS($F$2:I6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I7=IF(COLUMNS($F$2:I7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I8=IF(COLUMNS($F$2:I8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:I$1)-1),"")
I1{=IF(COLUMNS($F$2:I2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:I2),"")}
J2=IF(COLUMNS($F$2:J2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J3=IF(COLUMNS($F$2:J3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J4=IF(COLUMNS($F$2:J4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J5=IF(COLUMNS($F$2:J5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J6=IF(COLUMNS($F$2:J6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J7=IF(COLUMNS($F$2:J7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J8=IF(COLUMNS($F$2:J8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:J$1)-1),"")
J1{=IF(COLUMNS($F$2:J2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:J2),"")}
K2=IF(COLUMNS($F$2:K2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K3=IF(COLUMNS($F$2:K3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K4=IF(COLUMNS($F$2:K4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K5=IF(COLUMNS($F$2:K5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K6=IF(COLUMNS($F$2:K6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K7=IF(COLUMNS($F$2:K7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K8=IF(COLUMNS($F$2:K8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:K$1)-1),"")
K1{=IF(COLUMNS($F$2:K2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:K2),"")}
L2=IF(COLUMNS($F$2:L2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L3=IF(COLUMNS($F$2:L3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L4=IF(COLUMNS($F$2:L4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L5=IF(COLUMNS($F$2:L5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L6=IF(COLUMNS($F$2:L6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L7=IF(COLUMNS($F$2:L7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L8=IF(COLUMNS($F$2:L8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:L$1)-1),"")
L1{=IF(COLUMNS($F$2:L2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:L2),"")}
M2=IF(COLUMNS($F$2:M2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M3=IF(COLUMNS($F$2:M3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M4=IF(COLUMNS($F$2:M4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M5=IF(COLUMNS($F$2:M5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M6=IF(COLUMNS($F$2:M6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M7=IF(COLUMNS($F$2:M7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M8=IF(COLUMNS($F$2:M8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:M$1)-1),"")
M1{=IF(COLUMNS($F$2:M2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:M2),"")}
N2=IF(COLUMNS($F$2:N2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E2,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N3=IF(COLUMNS($F$2:N3)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E3,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N4=IF(COLUMNS($F$2:N4)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E4,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N5=IF(COLUMNS($F$2:N5)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E5,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N6=IF(COLUMNS($F$2:N6)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E6,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N7=IF(COLUMNS($F$2:N7)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E7,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N8=IF(COLUMNS($F$2:N8)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),INDEX($B$2:$B$26,MATCH($E8,$A$2:$A$26,0)+COLUMNS($F$1:N$1)-1),"")
N1{=IF(COLUMNS($F$2:N2)<=MAX(COUNTIF($A$2:$A$26,$E$2:$E$8)),"Drawing "&COLUMNS($F$2:N2),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
onetomanyjoin,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that will adjust to the varying number of raw data rows, and, to the number of resulting columns that will be required.

You can change the raw data worksheet name in the macro.

3. If you need the output results in a NEW worksheet, then, what should that worksheet name be?

Sample raw data, and, results:


Excel 2007
ABCDEFGHIJK
1BuildingDrawingBuildingDrawing1Drawing2Drawing3Drawing4Drawing5
2A1A12345
3A2B6789
4A3C1011
5A4D12
6A5E131415
7B6
8B7
9B8
10B9
11C10
12C11
13D12
14E13
15E14
16E15
17
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 07/09/2015, ME867153
Dim rng As Range, r As Range
Dim v As Variant, o(), oMax As Long, n As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  Set rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each r In rng
      If Not .Exists(r.Value) Then
        n = n + 1
        ReDim Preserve o(1 To rng.Count, 1 To n)
        .Add r.Value, Array(2, n)
        o(1, n) = r.Value
        o(2, n) = r.Offset(, 1).Value
      Else
        v = .Item(r.Value)
        v(0) = v(0) + 1
        o(v(0), v(1)) = r.Offset(, 1).Value
        oMax = Application.Max(oMax, v(0))
        .Item(r.Value) = v
      End If
    Next r
  End With
  .Range("E1").Value = "Building"
  .Range("E2").Resize(n, oMax) = Application.Transpose(o)
  With .Range("F1").Resize(, oMax - 1)
    .Formula = "=""Drawing"" & Column() - 5"
    .Value = .Value
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Upvote 0
This seems to be exactly what I'm looking for. Thank you very much and thanks for the thorough explanation.

1) I'm using Microsoft Office Standard 2013, on Windows 8.1.

2) Using a PC

3) I will probably need to export the values into a new blank workbook before importing to ArcMap but I can just copy over the cells into a new blank file.

hiker95 said:
onetomanyjoin,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that will adjust to the varying number of raw data rows, and, to the number of resulting columns that will be required.

You can change the raw data worksheet name in the macro.

3. If you need the output results in a NEW worksheet, then, what should that worksheet name be?

Sample raw data, and, results:

Excel 2007
ABCDEFGHIJK
1BuildingDrawingBuildingDrawing1Drawing2Drawing3Drawing4Drawing5
2A1A12345
3A2B6789
4A3C1011
5A4D12
6A5E131415
7B6
8B7
9B8
10B9
11C10
12C11
13D12
14E13
15E14
16E15
17

<tbody>
</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 07/09/2015, ME867153
Dim rng As Range, r As Range
Dim v As Variant, o(), oMax As Long, n As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  Set rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each r In rng
      If Not .Exists(r.Value) Then
        n = n + 1
        ReDim Preserve o(1 To rng.Count, 1 To n)
        .Add r.Value, Array(2, n)
        o(1, n) = r.Value
        o(2, n) = r.Offset(, 1).Value
      Else
        v = .Item(r.Value)
        v(0) = v(0) + 1
        o(v(0), v(1)) = r.Offset(, 1).Value
        oMax = Application.Max(oMax, v(0))
        .Item(r.Value) = v
      End If
    Next r
  End With
  .Range("E1").Value = "Building"
  .Range("E2").Resize(n, oMax) = Application.Transpose(o)
  With .Range("F1").Resize(, oMax - 1)
    .Formula = "=""Drawing"" & Column() - 5"
    .Value = .Value
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Transposing Horizontal String
Replies
6
Views
123
c0087
C
H
  • Solved
Transpose data from columns to rows
Replies
7
Views
293
harzer
H
F
  • Solved
Transposing filtered items but skip columns
Replies
5
Views
156
Fluff
Fluff
ExcelNewbie2020
  • Solved
transpose data
2
Replies
15
Views
223
kvsrinivasamurthy
K
H
  • Solved
Transpose data.
Replies
4
Views
123
harzer
H

Forum statistics

Threads
1,215,219
Messages
6,123,690
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top