Conditionally Transpose Rows to Columns

L

LinkSome

New Member
Joined
Oct 25, 2014
Messages
8
Hello,

I had originally posted this last week and thought a separate post answered my question. However, I cannot modify that formula well enough to work here so I'm posting again! I'm looking to move values that show as additional rows to a separate column within the row it is truly associated with. The complete range of the spreadsheet is very large, but for an example I'll include three instances.

There are a total of three potential address lines. Ocassionally the first is blank but the second is filled, generally the third is not used but it does make an appearance every thousand rows or so. I would like for each potential address line to move up to the row it is associated with. So if Address 1 was blank but 2 and 3 had contents (separate rows in address column below), the Address 2 field would fill the blank Address 1 and and Address 3 would fill Address 2 next to it. If all three had contents, it would transpose across the three fields. The city is generally the last row in the address column that is associated with a specific instance, though sometimes that would have missing data as well. I would like that transposed across to a separate column, City. Finally, the State and Zip appear on the row with the City but that is below the instance it is associated with. If those items could be moved up, or everything else moved down, it would be a great help.

test

*ABCDEFGHIJKLM
OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
XXX FREKPO BOX AAA*****AAADIVIDENDS
*775 AAA STREET***********
*STAMFORD***CA06902-6828******
YYY JAMES S******AAADIVIDENDS
*CIO AAA CO***********
*STAMFORD***CA06926-700******
ZZZ WILLIAM******AAADIVIDENDS
*CIO AAA CO***********
*STAMFORD***CA06926-700******

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

to

test

*ABCDEFGHIJKLM
OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
XXX FREKPO BOX AAA775 AAA STREET*STAMFORDCA06902-6828AAADIVIDENDS
YYY JAMES SCIO AAA CO**STAMFORDCA06926-700AAADIVIDENDS
ZZZ WILLIAMCIO AAA CO**STAMFORDCA06926-700AAADIVIDENDS

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:156px;"><col style="width:125px;"><col style="width:105px;"><col style="width:73px;"><col style="width:76px;"><col style="width:44px;"><col style="width:75px;"><col style="width:98px;"><col style="width:89px;"><col style="width:103px;"><col style="width:158px;"><col style="width:28px;"><col style="width:42px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Sort by date Sort by votes
LinkSome,

Can we have another screenshot where the groups in column B contain variations of 1 cell, different 2 cells, different 3 cells, and, 4 cells?


The city is generally the last row in the address column that is associated with a specific instance, though sometimes that would have missing data as well.
Click to expand...

And, a few examples of the above.
 
Last edited:
Upvote 0
Hi,

I think the below will provide a few more examples of the deviations. I'm hesitant to add too much as I need to take out sensitive information and the table's paste in fairly large.

test

*ABCDEFGHIJKLM
OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
XXX FREKC/O AZY*****AAADIVIDENDS
*775 ATLANTIC STREET***********
*Floor 2***********
*STAMFORDCA06902-6828*********
YYY JAMES S******AAADIVIDENDS
*C/O PHIL YYY***********
*22 FLORIDA ROAD***********
*STAMFORDCA06926-700*********
ZZZ WILLIAM******AAADIVIDENDS
*210 BALTIC AVE***********
*C/O AAA CO***********
**CA06926-700*********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

To

test

*ABCDEFGHIJKLM
OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
XXX FREKC/O AZY775 ATLANTIC STREETFloor 2STAMFORDCA06902-6828AAADIVIDENDS
YYY JAMES SC/O PHIL YYY22 FLORIDA ROAD*STAMFORDCA06926-700AAADIVIDENDS
ZZZ WILLIAM210 BALTIC AVEC/O AAA CO**CA06926-700AAADIVIDENDS

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:156px;"><col style="width:139px;"><col style="width:139px;"><col style="width:75px;"><col style="width:76px;"><col style="width:44px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
LinkSome,

Thanks for the new screenshot.

I am now confused.


Here is your original raw data structure:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKPO BOX AAAAAA19947DIVIDENDS77384
3775 AAA STREET
4STAMFORDCA06902-6828
5YYY JAMES SAAA19947DIVIDENDS77833.6
6CIO AAA CO
7STAMFORDCA06926-700
8ZZZ WILLIAMAAA19947DIVIDENDS78050.4
9CIO AAA CO
10STAMFORDCA06926-700
11
Sheet1


And, here is the new raw data structure:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKC/O AZYAAA19947DIVIDENDS77384
3775 ATLANTIC STREET
4Floor 2
5STAMFORDCA06902-6828
6YYY JAMES SAAA19947DIVIDENDS77833.6
7C/O PHIL YYY
822 FLORIDA ROAD
9STAMFORDCA06926-700
10ZZZ WILLIAMAAA19947DIVIDENDS78050.4
11210 BALTIC AVE
12C/O AAA CO
13CA06926-700
14
Sheet1



Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Last edited:
Upvote 0
Hi,

Sorry about that. I think I had moved it around while trying to modify some coding that had been written in another thread. It starts where it is shown in the second posting. The other three columns: "City", "State", and "ZIP" were added by me to the resulting conversion file when I bring it in from PDF. They do not exist originally.
 
Upvote 0
This VBA, slightly modified from another thread, solves my issue with output in the B column but causes me to lose the State and ZIP data in the last row. Could anyone suggest something that would allow me to save that data and move it up?

Sub TransposeDiagData()
Dim LastRow As Long, Blanks As Range, Ar As Range
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:A" & LastRow).Value = Range("A1:A" & LastRow).Value
On Error GoTo NoBlanks
Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
For Each Ar In Intersect(Blanks.EntireRow, Columns("B")).Areas
Intersect(Ar(1).Offset(-1).EntireRow, Columns("C")).Resize(, Ar.Count) = Application.Transpose(Ar)
Next
Blanks.EntireRow.Delete
NoBlanks:
End Sub
 
Upvote 0
LinkSome,

The following is based on your original screenshots.

If we start off with this:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKPO BOX AAAAAA19947DIVIDENDS77384
3775 AAA STREET
4STAMFORDCA06902-6828
5YYY JAMES SAAA19947DIVIDENDS77833.6
6CIO AAA CO
7STAMFORDCA06926-700
8ZZZ WILLIAMAAA19947DIVIDENDS78050.4
9CIO AAA CO
10STAMFORDCA06926-700
11
Sheet1


And run my latest macro, we get this:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKPO BOX AAA775 AAA STREETSTAMFORDCA06902-6828AAA19947DIVIDENDS77384
3YYY JAMES SCIO AAA COSTAMFORDCA06926-700AAA19947DIVIDENDS77833.6
4ZZZ WILLIAMCIO AAA COSTAMFORDCA06926-700AAA19947DIVIDENDS78050.4
5
6
7
8
9
10
11
Sheet1



If we start off with this:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKPO BOX AAAAAA19947DIVIDENDS77384
3775 AAA STREET
4STAMFORDCA06902-6828
5YYY JAMES SAAA19947DIVIDENDS77833.6
6CIO AAA CO
7STAMFORDCA06926-700
8ZZZ WILLIAMAAA19947DIVIDENDS78050.4
9CIO AAA CO
10STAMFORDCA06926-700
11AAA LinkSome
12CIO AAA COAAA19947DIVIDENDS78050.4
13775 AAA STREET
14Suite 222
15STAMFORDCA06926-700
16
Sheet1


And run my latest macro, we get this:


Excel 2007
ABCDEFGHIJKLM
1OWNER NAMEADDRESSADDRESS 2ADDRESS 3CITYSTATEZIPHOLDER NAMEREPORT YEARPROPERTY TYPEPROPERTY DESCRIPTIONIDCASH
2XXX FREKPO BOX AAA775 AAA STREETSTAMFORDCA06902-6828AAA19947DIVIDENDS77384
3YYY JAMES SCIO AAA COSTAMFORDCA06926-700AAA19947DIVIDENDS77833.6
4ZZZ WILLIAMCIO AAA COSTAMFORDCA06926-700AAA19947DIVIDENDS78050.4
5AAA LinkSomeCIO AAA CO775 AAA STREETSuite 222STAMFORDCA06926-700AAA19947DIVIDENDS78050.4
6
7
8
9
10
11
12
13
14
15
16
Sheet1
 
Upvote 0
LinkSome,

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, 11/03/2014, ME815245
Dim Area As Range, lr As Long, sr As Long, er As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
For Each Area In Range("B2:B" & lr).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    Cells(er, 5) = Cells(er, 2)
    Cells(er, 2) = ""
  End With
Next Area
For Each Area In Range("B2:B" & lr).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    If .Rows.Count = 1 Then
      Cells(sr - 1, 2) = Cells(sr, 2)
      Cells(sr, 2) = ""
    ElseIf .Rows.Count = 2 Then
      Cells(sr, 2).Resize(, .Rows.Count) = Application.Transpose(Area)
      Cells(sr + 1, 2) = ""
    ElseIf .Rows.Count = 3 Then
      Cells(sr - 1, 2).Resize(, .Rows.Count) = Application.Transpose(Area)
      Cells(sr, 2).Resize(.Rows.Count) = ""
    End If
    Range("A" & sr - 1 & ":M" & er + 1).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
  End With
Next Area
Columns("A:M").AutoFit
Application.ScreenUpdating = True
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

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

Similar threads

C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
188
Kaper
Kaper
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
L
  • Solved
VBA Help Modify Code - Prevent chart from resizing with columns / rows
Replies
3
Views
1K
Anthony47
A
M
  • Solved
Looking for the best way to transpose data from a single row into multiple rows
2
Replies
11
Views
1K
DanteAmor
DanteAmor
D
  • Question Question
Transpose columns to rows and insert based upon conditions.
Replies
4
Views
1K
d3mo
D

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