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 macro to convert columns to rows,excel transpose every x row into columns,excel vba transpose,transpose column to row with blank rows,transpose multiple addresses with vba in excel,coluumn transpose to row in macro,excel convert column to row,excel convert columns into rows,excel macro transpose,excel macro transpose columns to row,excel macrotranspose rows into columns,excel transpose addresses,excel transpose row to column,excel vba transpose columns to rows,excel vba transpose worksheet,macro to transpose data from columns to row and group the data,macro to transpose rows to columns,transpose column to row in excel,transpose column to row vba,transpose columns to rows excel,transpose every 4 rows in excel,transpose excel macro,transpose excel vba,transpose rows into columns with blanks,transpose xcel,vba excel transpose,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 to row excel,convert column-into-rows excel macro,convert columns into rows excel,convert from column to row in vba,converting row to column formula into another sheet,copy and transpose from column to row in another worksheet using excel vba,copy and transpose macro,copy column onto row vba,copy column to row transpose excel vba,copy rows to another worksheet and transpose to a column,excel
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)
- Remove Hyperlinks in Excel (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)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
