Transpose Column to Rows

P

Pai

I have around 1600 Name along with company
name,Address1,Address2,City,State,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

Hardeep Kanwar
 
P

Pete_UK

Insert a blank row at the top of your data, and put these formulae in
the cells stated:

B2: =IF($A1="",$A2,"")
C2: =IF($A1="",$A3,"")
D2: =IF($A1="",$A4,"")

(Note the similarities with these formulae)

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 can 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.

Hope this helps.

Pete
 
P

Pai

Thanks Pete

It Works gr8

Thanks again


Pete_UK said:
Insert a blank row at the top of your data, and put these formulae in
the cells stated:

B2: =IF($A1="",$A2,"")
C2: =IF($A1="",$A3,"")
D2: =IF($A1="",$A4,"")

(Note the similarities with these formulae)

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 can 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.

Hope this helps.

Pete
Click to expand...
 
Top