Convert one column into five

[Guest]

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?

 

[Guest]

Ron, I've had the same problem and your formula worked beautifully. You have
just saved me days of tedius copy, paste special, transpose. Thanks very
much.
--
Sincerely, Michael Colvin

If every address contains the same 3 fields, maybe this technique will work
for you:

Insert a column before your data
A2: Name
A3: Address
A4: CityState
A5: (blank)

Copy that series down until every address is labelled.

D1: Name
E1: Address
F1: CityState

D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1:$A$40 )),ROW()-1))
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy D2 to E2 and F2
Copy D2:F2 down as far as you need.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?

 

[Guest]

You're very welcome, Michael...I'm glad that helped.

***********
Regards,
Ron

XL2002, WinXP-Pro

Ron, I've had the same problem and your formula worked beautifully. You have
just saved me days of tedius copy, paste special, transpose. Thanks very
much.
--
Sincerely, Michael Colvin

If every address contains the same 3 fields, maybe this technique will work
for you:

Insert a column before your data
A2: Name
A3: Address
A4: CityState
A5: (blank)

Copy that series down until every address is labelled.

D1: Name
E1: Address
F1: CityState

D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1:$A$40 )),ROW()-1))
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy D2 to E2 and F2
Copy D2:F2 down as far as you need.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?

 

[Guest]

Ron, That worked GREAT!!!! Thank You !!!!!! I now find I have another
problem. This column doesn't always have just three rows of info and then a
blank row. Every once in a while there is an additional row for an address2.
Got any ideas on an easy way to find them and doing something with them?

Thanks again,
H.W.

If every address contains the same 3 fields, maybe this technique will work
for you:

Insert a column before your data
A2: Name
A3: Address
A4: CityState
A5: (blank)

Copy that series down until every address is labelled.

D1: Name
E1: Address
F1: CityState

D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1:$A$40 )),ROW()-1))
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy D2 to E2 and F2
Copy D2:F2 down as far as you need.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?

 

[Bryan Hessey]

H.W.,

Another way to spread 5 repeating rows over 5 columns is, in B1 put

=INDIRECT("$A"&((ROW()*5)-6+COLUMN()))

and formula drag this to F1,
then select B1:F1 and formula copy down to cover all of your data.

This will show where your extra line appears, you can either copy/add
the data to the Address1 cell, and remove the additional line
(preferred),

or (optional) adjust the formulas from that point to be +1 more on the
cell selected, ie

=INDIRECT("$a"&((ROW()*5)-6+COLUMN()+1))

etc, and re-copy the formula from that point on.


When you have the data in a good looking form, select columns B to F,
Copy, and Paste Special - Value, back over themselves.
You can then delete column A.

Hope this helps

--

Ron, That worked GREAT!!!! Thank You !!!!!! I now find I have another
problem. This column doesn't always have just three rows of info and
then a
blank row. Every once in a while there is an additional row for an
address2.
Got any ideas on an easy way to find them and doing something with
them?

Thanks again,
H.W.

If every address contains the same 3 fields, maybe this technique will work
for you:

Insert a column before your data
A2: Name
A3: Address
A4: CityState
A5: (blank)

Copy that series down until every address is labelled.

D1: Name
E1: Address
F1: CityState

D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1:$A$40 )),ROW()-1))
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy D2 to E2 and F2
Copy D2:F2 down as far as you need.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?

 

[Guest]

Try something like this:

With a list of address fields beginning in B2 and extending down.

This formula assigns a value type to each field:
A2:
=LOOKUP(MATCH(TRUE,ISBLANK($B1:$B6),0)*10+MATCH(TRUE,ISBLANK($B2:$B7),0),{11,14,15,21,32,43,54},{"Skip","Name","Name","Skip","CityState","Addr2","Addr1"})

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy A2 and paste into A3 and down as far as needed

This formula finds the row number of the start of a new address:
C1: NameRef
C2: =SMALL(IF($A$1:$A$40="Name",ROW($A$1:$A$40 )),ROW()-1)
Commit that formula with Ctrl/Shift/Enter

These formula read address data from the list:
D1: Name
E1: Addr1
F1: Addr2
G1: CityState

D2: =INDEX($B:$B,$C2)

E2:
=IF(ISNA(VLOOKUP(E$1,INDEX($A:$A,$C2):INDEX($B:$B,$C2+4),2,0)),"",VLOOKUP(E$1,INDEX($A:$A,$C2):INDEX($B:$B,$C2+4),2,0))

Copy E2 across through G2

Copy D2:G2 down as far as needed

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

Ron, That worked GREAT!!!! Thank You !!!!!! I now find I have another
problem. This column doesn't always have just three rows of info and then a
blank row. Every once in a while there is an additional row for an address2.
Got any ideas on an easy way to find them and doing something with them?

Thanks again,
H.W.

If every address contains the same 3 fields, maybe this technique will work
for you:

Insert a column before your data
A2: Name
A3: Address
A4: CityState
A5: (blank)

Copy that series down until every address is labelled.

D1: Name
E1: Address
F1: CityState

D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1:$A$40 )),ROW()-1))
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy D2 to E2 and F2
Copy D2:F2 down as far as you need.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a spreasheet with one column. The rows have name, address,city state
zip. i.e. row 1 is name, row2 is address, row3 is city state & zip, row4 is
blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000+
names,address,city state zip. What I need to do is move all name rows to
column B. All address rows to column C. All city state zip rows to column D.
I know I can cut and paste but that would take forever. Anyone know an easier
way?