Sorting a messy database output by address suburb

[coalman]

Hi!

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!

WEL02B
5 Mont Street
CALORA NORTH NSW 2211
DOB: 13/01/2011	ABN:
Partner:	Office:
User: P	Money: 0.00	Type: 0
TEC01
Kerow Club
Treggle Industrial Estate
FAUTH WALL TR11 4SN
UNITED KINGDOM
DOB:	ABN: 59 138 211 060
Partner:	Office:
User: C	Money: 0.00	Type: 0
NIV01B
DOB:	ABN:
Partner:	Office:
User: C	Money: 0.00	Type: 0
JOL01
7/ 2-4 Springfield Road
SPRINGFIELD NSW 2135
DOB: 20/03/1980	ABN:
Partner:	Office:
User: I	Money: 0.00	Type: 0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[hiker95]

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.

 

[coalman]

Thanks hiker,

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

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

<tbody>
</tbody>

and the output I would like is

	F	G	H	I	J	K	L	M
1	Name	Address1	Address2	Address3	Address4	DOB	ABN	User
2	WEL02B	5 Mont Street	CALORA NORTH NSW 2211			13/01/2011		P
3	TEC01	Kerow Club	Treggle Industrial Estate	FAUTH WALL TR11 4SN	UNITED KINGDOM		59 138 211 060	C
4	NIV01B							C
5	JOL01	7/ 2-4 Springfield Road	SPRINGFIELD NSW 2135			20/03/1980		I
6	MAN02B	PO Box 8	KOORINGAL NSW 2221				82 237 035 882	P
7	DON001	C/- Super	PO Box 9000	BRISBANE QLD 4000		11/08/1976		I

<tbody>
</tbody>

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

 

[hiker95]

coalman,


Sample raw data:

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

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	WEL02B					Name	Address1	Address2	Address3	Address4	DOB	ABN	User
2	5 Mont Street					WEL02B	5 Mont Street	CALORA NORTH NSW 2211			13/01/2011		P
3	CALORA NORTH NSW 2211					TEC01	Kerow Club	Treggle Industrial Estate	FAUTH WALL TR11 4SN	UNITED KINGDOM		59 138 211 060	C
4	DOB: 13/01/2011	ABN:				NIV01B							C
5	Partner:	Office:				JOL01	7/ 2-4 Springfield Road	SPRINGFIELD NSW 2135			20/03/1980		I
6	User: P	Money: 0.00	Type: 0			MAN02B	PO Box 8	KOORINGAL NSW 2221				82 237 035 882	P
7	TEC01					DON001	C/- Super	PO Box 9000	BRISBANE QLD 4000		11/8/1976		I
8	Kerow Club
9	Treggle Industrial Estate
10	FAUTH WALL TR11 4SN
11	UNITED KINGDOM
12	DOB:	ABN: 59 138 211 060
13	Partner:	Office:
14	User: C	Money: 0.00	Type: 0
15	NIV01B
16	DOB:	ABN:
17	Partner:	Office:
18	User: C	Money: 0.00	Type: 0
19	JOL01
20	7/ 2-4 Springfield Road
21	SPRINGFIELD NSW 2135
22	DOB: 20/03/1980	ABN:
23	Partner:	Office:
24	User: I	Money: 0.00	Type: 0
25	MAN02B
26	PO Box 8
27	KOORINGAL NSW 2221
28	DOB:	ABN: 82 237 035 882
29	Partner:	Office:
30	User: P	Money: 0.00	Type: 0
31	DON001
32	C/- Super
33	PO Box 9000
34	BRISBANE QLD 4000
35	DOB: 11/08/1976	ABN:
36	Partner:	Office:
37	User: I	Money: 0.00	Type: 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.

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.

 

[shawnhet]

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,

 

[shawnhet]

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,

 

[shawnhet]

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,

 

[coalman]

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.

 

[hiker95]

coalman,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

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.

See the link below:

Training / Books / Sites as of 09/13/2012
http://www.mrexcel.com/forum/excel-...gathering-suggestions-please.html#post3250542