Address Rows to Columns dependent upon a Cell Value of Account Ref - hard to explain!

[MattDanFrank2]

Hi, I'm really hoping someone can help me please.

I've extracted approx 9000 rows of data from a paper document using some OCR software, which I now need to get into a format that I can put into a database i.e. account, address line 1, address line 2, address line 3 etc.

As you can see in the main table at the bottom, in column A there is the account number with the corresponding address info in multiple rows in column B.

Is there a way to separate the address rows in column B to multiple columns instead, in the same row as the Account Ref?

Note: Not all the addresses have the same number of rows

For example,

	A	B	C	D	E	F
1	ACCOUNT	ADD1	ADD2	ADD3	ADD4	ADD5
2	321	321 Recreation Club	Gilmore Street	Alexandria	Glasgow	G83 0DA

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

Your help will be so much appreciated!!

Matthew

Main Table

	A	B
1	ACCOUNT	TO SEPARATE
2	321	321 Recreation Club
3		Gilmore Street
4		Alexandria
5		Glasgow
6		G83 ODA
7	39STEPS	39 Steps
8		28a Great King Street
9		Edinburgh
10		EH3 6Q1-1
11	4SIGHT	4Sight Financial Software Ltd
12		Conference House
13		152 Morrison Street
14		Edinburgh
15		EH3 8EB
16	ABALMOUR	Alan Balmour
17		257 Sandpiper Drive
18		Greenhill
19		East Kilbride
20		G75 8UN

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

 

[MickG]

Try this:-
Results sheet(2) starting "A1"

[COLOR="Navy"]Sub[/COLOR] MG19Sep50
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 10)
MsgBox Rng.Address
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1) <> "" Then: Rw = Rw + 1: c = 0
    c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 1 [COLOR="Navy"]Then[/COLOR]
            ray(Rw, c) = Dn.Offset(, -1)
            c = c + 1
            ray(Rw, c) = Dn
        [COLOR="Navy"]Else[/COLOR]
            ray(Rw, c) = Dn
        [COLOR="Navy"]End[/COLOR] If
        oMax = application.Max(oMax, c)
        
[COLOR="Navy"]Next[/COLOR] Dn
Sheets("sheet2").Range("A1").Resize(Rw, oMax) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MattDanFrank2]

Mick! Whoever and wherever you are - YOU ARE A GOD!!!! I can't thank you enough, seriously, thank you so so much. (running around the office high-fiving people). Best, Matthew.

 

[MickG]

Your welcome
Regrds Mick