Convert one column into five
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? |
Convert one column into five
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? |
Convert one column into five
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? |
Convert one column into five
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? |
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? |
Convert one column into five
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 -- 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? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531006 |
Convert one column into five
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(TR UE,ISBLANK($B2:$B7),0),{11,14,15,21,32,43,54},{"Sk ip","Name","Name","Skip","CityState","Addr2","Addr 1"}) 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? |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com