How To Transpose Column into Rows
Sponsor
Excel Tips Question:
I need your help to solve my excel problem, i have about 1600 contact name along with company name, address1, address2, city, state, and pincode
For Ex:
Mr.hardeep Kanwar
Bi/200
Yamuna Vihar
Dehi
110053
Some times it has 5data as mentioned in the Example, Sometime4 and sometimes 6 And in the end of every data there is a Blank Row Like This
Mr.hardeep Kanwar
Bi/200
Yamuna Vihar
Dehi
110053
Blank
Ms. Danielle Fran
American Cancer Society
1555 Abbey Road
East Lansing, MI 48823
Blank
Now i want to Convert like this
Mr.hardeep Kanwar Bi/200 Yamuna Vihar Dehi 110053
Ms. Danielle Fran American Cancer Society 1555 Abbey Road East Lansing, MI
48823
But Without using Marco.
Any Help will be Highly Appreciate
Excel Tips Answer:
Follow this step : Insert a blank row at the top of your data, and put this microsoft excel formulas in the cells stated:
B2: =IF($A1=”",$A2,”")
C2: =IF($A1=”",$A3,”")
D2: =IF($A1=”",$A4,”")
(Note the similarities with these excel formula)
E2: =IF($A1=”",IF(OR($A5=”",D2=”"),”",$A5),”")
F2: =IF($A1=”",IF(OR($A6=”",E2=”"),”",$A6),”")
G2: =IF($A1=”",IF(OR($A7=”",F2=”"),”",$A7),”")
Then you may just copy these down to get the name and address in
columns B to G on the top row of your current addresses. You can fix
the values and then column A can be deleted.
The blank rows can be deleted by applying a filter to one of the
columns, selecting Blanks from the pull-down, highlighting the visible
(blank) rows and then clicking on Edit | Delete Rows. Then you can
select All from the filter pull-down to see your data in the format
you require.
Rate This Tips:
(No Ratings Yet)
Loading ...Incoming excel search terms
macro to transpose columns to rows,excel vba transpose column to row,excel transpose macro,excel transpose every x row into columns,transpose column to row with blank rows,transpose multiple addresses with vba in excel,coluumn transpose to row in macro,excel convert columns into rows,excel transpose addresses,excel vba transpose,excel vba transpose columns to rows,transpose column to row vba,transpose columns to rows excel,transpose every 4 rows in excel,transpose rows into columns with blanks,vba excel transpose row to column,vba transpose row to column,visual basic excel tranpose column row,transpose every three columns into rows,,bi vba excel tips,can you get data in column to a row in excel?,code of transpose from row to column in vb,column to row row to column excel,convert column into rows in excel,convert column-into-rows excel macro,copy and transpose from column to row in another worksheet using excel vba,copy column onto row vba,copy column to row transpose excel vba,copy rows to another worksheet and transpose to a column,excel colum to row,excel column into rows,excel columns to rows multiple addresses,excel convert column to row,excel convert column to row without transpose,excel convert column to row without using transpose,excel document transfer columns into rows,excel fill column into row!,excel formula format address blank rows address1 address2,excel formulas transfer row to columns,excel how to switch column and rows,excel macro colum to row every,excel macro columns into rows,excel macro convert column to rows,excel macro copy row down 3 times,excel macro copy rows and transpose to column,excel macro from column to row,excel macro how to convert columns to rows,excel macro to transpose addresses from columns to rows,excel macro to transpose rows to columns
Related Excel Tips
Comments
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project
(5.00 out of 5) - How to Remove Excel Vba Macro Button (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Copy from Worksheet (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Remove Hyperlinks in Excel (5.00 out of 5)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
