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?
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
"H.W." wrote:
> 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?
>
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
"Ron Coderre" wrote:
> 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
>
>
> "H.W." wrote:
>
> > 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?
> >
You're very welcome, Michael...I'm glad that helped.
***********
Regards,
Ron
XL2002, WinXP-Pro
"Michael" wrote:
> 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
>
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "H.W." wrote:
> >
> > > 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?
> > >
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.
"Ron Coderre" wrote:
> 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
>
>
> "H.W." wrote:
>
> > 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?
> >
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
--
Originally Posted by H.W.
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
"H.W." wrote:
> 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.
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "H.W." wrote:
> >
> > > 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?
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks