Convert one column into five
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?
|