software

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 amp; 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?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-Proquot;H.W.quot; wrote:

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

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 Colvinquot;Ron Coderrequot; wrote:

gt; If every address contains the same 3 fields, maybe this technique will work
gt; for you:
gt;
gt; Insert a column before your data
gt; A2: Name
gt; A3: Address
gt; A4: CityState
gt; A5: (blank)
gt;
gt; Copy that series down until every address is labelled.
gt;
gt; D1: Name
gt; E1: Address
gt; F1: CityState
gt;
gt; D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1 :$A$40 )),ROW()-1))
gt; Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
gt; [Enter].
gt;
gt; Copy D2 to E2 and F2
gt; Copy D2:F2 down as far as you need.
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;H.W.quot; wrote:
gt;
gt; gt; I have a spreasheet with one column. The rows have name, address,city state
gt; gt; zip. i.e. row 1 is name, row2 is address, row3 is city state amp; zip, row4 is
gt; gt; blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000
gt; gt; names,address,city state zip. What I need to do is move all name rows to
gt; gt; column B. All address rows to column C. All city state zip rows to column D.
gt; gt; I know I can cut and paste but that would take forever. Anyone know an easier
gt; gt; way?
gt; gt;

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

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

XL2002, WinXP-Proquot;Michaelquot; wrote:

gt; Ron, I've had the same problem and your formula worked beautifully. You have
gt; just saved me days of tedius copy, paste special, transpose. Thanks very
gt; much.
gt; --
gt; Sincerely, Michael Colvin
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; If every address contains the same 3 fields, maybe this technique will work
gt; gt; for you:
gt; gt;
gt; gt; Insert a column before your data
gt; gt; A2: Name
gt; gt; A3: Address
gt; gt; A4: CityState
gt; gt; A5: (blank)
gt; gt;
gt; gt; Copy that series down until every address is labelled.
gt; gt;
gt; gt; D1: Name
gt; gt; E1: Address
gt; gt; F1: CityState
gt; gt;
gt; gt; D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1 :$A$40 )),ROW()-1))
gt; gt; Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
gt; gt; [Enter].
gt; gt;
gt; gt; Copy D2 to E2 and F2
gt; gt; Copy D2:F2 down as far as you need.
gt; gt;
gt; gt; Is that something you can work with?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;H.W.quot; wrote:
gt; gt;
gt; gt; gt; I have a spreasheet with one column. The rows have name, address,city state
gt; gt; gt; zip. i.e. row 1 is name, row2 is address, row3 is city state amp; zip, row4 is
gt; gt; gt; blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000
gt; gt; gt; names,address,city state zip. What I need to do is move all name rows to
gt; gt; gt; column B. All address rows to column C. All city state zip rows to column D.
gt; gt; gt; I know I can cut and paste but that would take forever. Anyone know an easier
gt; gt; gt; way?
gt; gt; gt;

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.

quot;Ron Coderrequot; wrote:

gt; If every address contains the same 3 fields, maybe this technique will work
gt; for you:
gt;
gt; Insert a column before your data
gt; A2: Name
gt; A3: Address
gt; A4: CityState
gt; A5: (blank)
gt;
gt; Copy that series down until every address is labelled.
gt;
gt; D1: Name
gt; E1: Address
gt; F1: CityState
gt;
gt; D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1 :$A$40 )),ROW()-1))
gt; Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
gt; [Enter].
gt;
gt; Copy D2 to E2 and F2
gt; Copy D2:F2 down as far as you need.
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;H.W.quot; wrote:
gt;
gt; gt; I have a spreasheet with one column. The rows have name, address,city state
gt; gt; zip. i.e. row 1 is name, row2 is address, row3 is city state amp; zip, row4 is
gt; gt; blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000
gt; gt; names,address,city state zip. What I need to do is move all name rows to
gt; gt; column B. All address rows to column C. All city state zip rows to column D.
gt; gt; I know I can cut and paste but that would take forever. Anyone know an easier
gt; gt; way?
gt; gt;


H.W.,

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

=INDIRECT(quot;$Aquot;amp;((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(quot;$aquot;amp;((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

--

H.W. Wrote:
gt; Ron, That worked GREAT!!!! Thank You !!!!!! I now find I have another
gt; problem. This column doesn't always have just three rows of info and
gt; then a
gt; blank row. Every once in a while there is an additional row for an
gt; address2.
gt; Got any ideas on an easy way to find them and doing something with
gt; them?
gt;
gt; Thanks again,
gt; H.W.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; If every address contains the same 3 fields, maybe this technique
gt; will work
gt; gt; for you:
gt; gt;
gt; gt; Insert a column before your data
gt; gt; A2: Name
gt; gt; A3: Address
gt; gt; A4: CityState
gt; gt; A5: (blank)
gt; gt;
gt; gt; Copy that series down until every address is labelled.
gt; gt;
gt; gt; D1: Name
gt; gt; E1: Address
gt; gt; F1: CityState
gt; gt;
gt; gt; D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1 :$A$40
gt; )),ROW()-1))
gt; gt; Note: To commit array formulas, hold down [Ctrl] and [Shift] when you
gt; press
gt; gt; [Enter].
gt; gt;
gt; gt; Copy D2 to E2 and F2
gt; gt; Copy D2:F2 down as far as you need.
gt; gt;
gt; gt; Is that something you can work with?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;H.W.quot; wrote:
gt; gt;
gt; gt; gt; I have a spreasheet with one column. The rows have name,
gt; address,city state
gt; gt; gt; zip. i.e. row 1 is name, row2 is address, row3 is city state amp; zip,
gt; row4 is
gt; gt; gt; blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on
gt; for 3000
gt; gt; gt; names,address,city state zip. What I need to do is move all name
gt; rows to
gt; gt; gt; column B. All address rows to column C. All city state zip rows to
gt; column D.
gt; gt; gt; I know I can cut and paste but that would take forever. Anyone know
gt; an easier
gt; gt; gt; way?
gt; gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531006Try 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(TR UE,ISBLANK($B2:$B7),0),{11,14,15,21,32,43,54},{quot;Sk ipquot;,quot;Namequot;,quot;Namequot;,quot;Skipquot;,quot;CityStatequot;,quot;Addr2quot;,quot;Addr 1quot;})

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=quot;Namequot;,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)),quot;quot;,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-Proquot;H.W.quot; wrote:

gt; Ron, That worked GREAT!!!! Thank You !!!!!! I now find I have another
gt; problem. This column doesn't always have just three rows of info and then a
gt; blank row. Every once in a while there is an additional row for an address2.
gt; Got any ideas on an easy way to find them and doing something with them?
gt;
gt; Thanks again,
gt; H.W.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; If every address contains the same 3 fields, maybe this technique will work
gt; gt; for you:
gt; gt;
gt; gt; Insert a column before your data
gt; gt; A2: Name
gt; gt; A3: Address
gt; gt; A4: CityState
gt; gt; A5: (blank)
gt; gt;
gt; gt; Copy that series down until every address is labelled.
gt; gt;
gt; gt; D1: Name
gt; gt; E1: Address
gt; gt; F1: CityState
gt; gt;
gt; gt; D2: =INDEX($B$1:$B$40,SMALL(IF($A$1:$A$40=D$1,ROW($A$1 :$A$40 )),ROW()-1))
gt; gt; Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
gt; gt; [Enter].
gt; gt;
gt; gt; Copy D2 to E2 and F2
gt; gt; Copy D2:F2 down as far as you need.
gt; gt;
gt; gt; Is that something you can work with?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;H.W.quot; wrote:
gt; gt;
gt; gt; gt; I have a spreasheet with one column. The rows have name, address,city state
gt; gt; gt; zip. i.e. row 1 is name, row2 is address, row3 is city state amp; zip, row4 is
gt; gt; gt; blank, row5 is name, row6 is address, etc.,etc.,etc.. This goes on for 3000
gt; gt; gt; names,address,city state zip. What I need to do is move all name rows to
gt; gt; gt; column B. All address rows to column C. All city state zip rows to column D.
gt; gt; gt; I know I can cut and paste but that would take forever. Anyone know an easier
gt; gt; gt; way?
gt; gt; gt;