Transpose Column to Rows

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

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

On Dec 10, 9:35*am, Pai wrote:
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

Thanks Pete

It Works gr8

Thanks again


"Pete_UK" wrote:

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

On Dec 10, 9:35 am, Pai wrote:
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

You're welcome - thanks for feeding back.

Pete

On Dec 10, 3:18*pm, Pai wrote:
Thanks Pete

It Works gr8

Thanks again



"Pete_UK" wrote:
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

On Dec 10, 9:35 am, Pai wrote:
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- Hide quoted text -

- Show quoted text -