Sorting a messy database output by address suburb

C

coalman

New Member
Joined
Sep 16, 2012
Messages
3
Hi!
smile.gif


I would like to sort the list of variables into each of their own rows somehow, but I don't even know where to start!

Any help at all would be awesome! Thank you!

[TABLE="width: 390"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]WEL02B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 Mont Street[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]CALORA NORTH NSW 2211 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOB: 13/01/2011[/TD]
[TD]ABN: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner: [/TD]
[TD]Office: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User: P[/TD]
[TD]Money: 0.00[/TD]
[TD]Type: 0[/TD]
[/TR]
[TR]
[TD]TEC01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kerow Club[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Treggle Industrial Estate[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FAUTH WALL TR11 4SN [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOB: [/TD]
[TD]ABN: 59 138 211 060[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner: [/TD]
[TD]Office: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User: C[/TD]
[TD]Money: 0.00[/TD]
[TD]Type: 0[/TD]
[/TR]
[TR]
[TD]NIV01B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOB: [/TD]
[TD]ABN: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner: [/TD]
[TD]Office: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User: C[/TD]
[TD]Money: 0.00[/TD]
[TD]Type: 0[/TD]
[/TR]
[TR]
[TD]JOL01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/ 2-4 Springfield Road[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPRINGFIELD NSW 2135[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOB: 20/03/1980[/TD]
[TD]ABN: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner: [/TD]
[TD]Office: [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User: I[/TD]
[TD]Money: 0.00[/TD]
[TD]Type: 0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
coalman,

Welcome to the MrExcel forum.

What version of Excel are you using?

We cannot tell what cells, rows, columns your raw data is in. And, you have not given us a screenshot of what the results should look like (manually formatted by you).


Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Thanks hiker,

I'm using Excel 2007. The Raw data I have is

ABC

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]5 Mont Street[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]CALORA NORTH NSW 2211[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]DOB: 13/01/2011[/TD]
[TD="align: left"]ABN:[/TD]

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]User: P[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]Treggle Industrial Estate[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]FAUTH WALL TR11 4SN[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]DOB:[/TD]
[TD="align: left"]ABN: 59 138 211 060[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]User: C[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]NIV01B[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]User: C[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: left"]JOL01[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: left"]7/ 2-4 Springfield Road[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: left"]SPRINGFIELD NSW 2135[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: left"]DOB: 20/03/1980[/TD]
[TD="align: left"]ABN:[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: left"]Partner:[/TD]
[TD="align: left"]Office:[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: left"]User: I[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: left"]MAN02B[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: left"]PO Box 8[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: left"]KOORINGAL NSW 2221[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: left"]DOB:[/TD]
[TD="align: left"]ABN: 82 237 035 882[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: left"]Partner:[/TD]
[TD="align: left"]Office:[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="align: left"]User: P[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="align: left"]DON001[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: left"]C/- Super[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: left"]PO Box 9000[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: left"]BRISBANE QLD 4000[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: left"]DOB: 11/08/1976[/TD]
[TD="align: left"]ABN:[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: left"]Partner:[/TD]
[TD="align: left"]Office:[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: left"]User: I[/TD]
[TD="align: left"]Money: 0.00[/TD]
[TD="align: left"]Type: 0[/TD]

</tbody>

and the output I would like is

FGHIJKLM
NameAddress1Address2Address3Address4DOBABNUser
P
59 138 211 060
C
I
82 237 035 882P
I

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]WEL02B[/TD]
[TD="align: left"]5 Mont Street[/TD]
[TD="align: left"]CALORA NORTH NSW 2211[/TD]

[TD="align: right"]13/01/2011[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]TEC01[/TD]
[TD="align: left"]Kerow Club[/TD]
[TD="align: left"]Treggle Industrial Estate[/TD]
[TD="align: left"]FAUTH WALL TR11 4SN[/TD]
[TD="align: left"]UNITED KINGDOM[/TD]

[TD="align: left"]C[/TD]

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]JOL01[/TD]
[TD="align: left"]7/ 2-4 Springfield Road[/TD]
[TD="align: left"]SPRINGFIELD NSW 2135[/TD]

[TD="align: right"]20/03/1980[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]MAN02B[/TD]
[TD="align: left"]PO Box 8[/TD]
[TD="align: left"]KOORINGAL NSW 2221[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]DON001[/TD]
[TD="align: left"]C/- Super[/TD]
[TD="align: left"]PO Box 9000[/TD]
[TD="align: left"]BRISBANE QLD 4000[/TD]

[TD="align: right"]11/08/1976[/TD]

</tbody>


or any other output that is clean and easy to sort through. Again many thanks.
 
Upvote 0
coalman,


Sample raw data:


Excel Workbook
ABCDEFGHIJKLM
1WEL02B
25 Mont Street
3CALORA NORTH NSW 2211
4DOB: 13/01/2011ABN:
5Partner:Office:
6User: PMoney: 0.00Type: 0
7TEC01
8Kerow Club
9Treggle Industrial Estate
10FAUTH WALL TR11 4SN
11UNITED KINGDOM
12DOB:ABN: 59 138 211 060
13Partner:Office:
14User: CMoney: 0.00Type: 0
15NIV01B
16DOB:ABN:
17Partner:Office:
18User: CMoney: 0.00Type: 0
19JOL01
207/ 2-4 Springfield Road
21SPRINGFIELD NSW 2135
22DOB: 20/03/1980ABN:
23Partner:Office:
24User: IMoney: 0.00Type: 0
25MAN02B
26PO Box 8
27KOORINGAL NSW 2221
28DOB:ABN: 82 237 035 882
29Partner:Office:
30User: PMoney: 0.00Type: 0
31DON001
32C/- Super
33PO Box 9000
34BRISBANE QLD 4000
35DOB: 11/08/1976ABN:
36Partner:Office:
37User: IMoney: 0.00Type: 0
38
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJKLM
1WEL02BNameAddress1Address2Address3Address4DOBABNUser
25 Mont StreetWEL02B5 Mont StreetCALORA NORTH NSW 221113/01/2011P
3CALORA NORTH NSW 2211TEC01Kerow ClubTreggle Industrial EstateFAUTH WALL TR11 4SNUNITED KINGDOM59 138 211 060C
4DOB: 13/01/2011ABN:NIV01BC
5Partner:Office:JOL017/ 2-4 Springfield RoadSPRINGFIELD NSW 213520/03/1980I
6User: PMoney: 0.00Type: 0MAN02BPO Box 8KOORINGAL NSW 222182 237 035 882P
7TEC01DON001C/- SuperPO Box 9000BRISBANE QLD 400011/8/1976I
8Kerow Club
9Treggle Industrial Estate
10FAUTH WALL TR11 4SN
11UNITED KINGDOM
12DOB:ABN: 59 138 211 060
13Partner:Office:
14User: CMoney: 0.00Type: 0
15NIV01B
16DOB:ABN:
17Partner:Office:
18User: CMoney: 0.00Type: 0
19JOL01
207/ 2-4 Springfield Road
21SPRINGFIELD NSW 2135
22DOB: 20/03/1980ABN:
23Partner:Office:
24User: IMoney: 0.00Type: 0
25MAN02B
26PO Box 8
27KOORINGAL NSW 2221
28DOB:ABN: 82 237 035 882
29Partner:Office:
30User: PMoney: 0.00Type: 0
31DON001
32C/- Super
33PO Box 9000
34BRISBANE QLD 4000
35DOB: 11/08/1976ABN:
36Partner:Office:
37User: IMoney: 0.00Type: 0
38
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 09/17/2012
' http://www.mrexcel.com/forum/excel-questions/659553-sorting-messy-database-output-address-suburb.html
Dim i() As Variant, o() As Variant
Dim r As Long, lr As Long, nr As Long, c As Long, lc As Long, nc As Long, n As Long
Application.ScreenUpdating = False
Columns("F:M").ClearContents
lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lc = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
i = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountIf(Columns(1), "DOB*")
ReDim o(1 To n + 1, 1 To 8)
o(1, 1) = "Name"
o(1, 2) = "Address1"
o(1, 3) = "Address2"
o(1, 4) = "Address3"
o(1, 5) = "Address4"
o(1, 6) = "DOB"
o(1, 7) = "ABN"
o(1, 8) = "User"
nr = 2: nc = 0
For r = LBound(i, 1) To UBound(i, 1)
  If Left(i(r, 1), 8) = "Partner:" Then
    'do nothing
  ElseIf InStr(i(r, 1), "DOB:") = 0 And InStr(i(r, 1), "User:") = 0 And InStr(i(r, 1), "User:") = 0 Then
    'nr = nr + 1
    nc = nc + 1
    o(nr, nc) = i(r, 1)
  ElseIf InStr(i(r, 1), "DOB:") > 0 Then
    If Len(i(r, 1)) > 4 Then
      o(nr, 6) = Right(i(r, 1), Len(i(r, 1)) - 5)
    End If
    If Len(i(r, 2)) > 4 Then
      o(nr, 7) = Right(i(r, 2), Len(i(r, 2)) - 5)
    End If
  ElseIf InStr(i(r, 1), "User:") > 0 Then
    o(nr, 8) = Right(i(r, 1), Len(i(r, 1)) - 6)
  End If
  If o(nr, 8) <> "" Then
    nc = 0
    nr = nr + 1
  End If
Next r
Range("F1").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns("F: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
I thought I would give a shot at a non-macro solution for the above (as it doesn't seem to have worked perfectly).

If I would doing this I would move column C to Column E and Column B to the now empty column C - then I would use Text to columns delimited on :

then starting in G2 to R2 I would post the following formulae:

=A1="User"
=IF(AND($A1="User",A3<>"DOB"),A3,"")
=IF(AND($A1="User",A4<>"DOB",H2<>""),A4,"")
=IF(AND($A1="User",A5<>"DOB",I2<>""),A5,"")
=IF(AND($A1="User",A6<>"DOB",J2<>""),A6,"")
=OFFSET($A2,COUNTBLANK($H2:$K2)+1,1)
=OFFSET($A2,COUNTBLANK($H2:$K2)+1,3)
=OFFSET($A2,COUNTBLANK($H2:$K2)+2,1)
=OFFSET($A2,COUNTBLANK($H2:$K2)+2,3)
=OFFSET($A2,COUNTBLANK($H2:$K2)+3,1)
=OFFSET($A2,COUNTBLANK($H2:$K2)+3,3)
=OFFSET($A2,COUNTBLANK($H2:$K2)+3,5)

Then you can filter for column G being True and this should give you the results you are looking for with 4 total fields for the Addresses.

Cheers, :)
 
Upvote 0
Oops, my formulae above were not accurate:

Try these instead:

=A1="User"
=IF(AND($A1="User",A3<>"DOB"),A3,"")
=IF(AND($A1="User",A4<>"DOB",H2<>""),A4,"")
=IF(AND($A1="User",A5<>"DOB",I2<>""),A5,"")
=IF(AND($A1="User",A6<>"DOB",J2<>""),A6,"")
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+1,1)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+1,3)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+2,1)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+2,3)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+3,1)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+3,3)
=OFFSET($A2,ABS(4-COUNTBLANK($H2:$K2))+3,5)

Cheers, :)
 
Upvote 0
On looking closer at hiker's solution above I see that it works fine. I just didn't read his output correctly. Sorry for any confusion this may've caused.

Cheers, :)
 
Upvote 0
Wow, that is amazing! Perfect! Thank you so much hiker! I really have a lot of work to do before I can do anything like this, but I would love to learn.

Thanks also Shawn, I didn't even consider/know it was possible to do those equations.
 
Upvote 0
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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