Need to convert 3000 row data to column in a specific format

[Lokeshkumarmylapilli]

The below sample data(Company name, address and revenue) is in row wise. I would like to have company on one column, address on second column and revenue on third column. Please let me know the process and procedure. There is 3000 rows of data to be converted to the mentioned format

Cameron International Corporation
Houston, Texas,
United States
$7,210.39M


Roper Technologies, Inc.
Sarasota, Florida,
United States
$5,366.80M
.
.
.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Glenn Kennedy]

I have assumed (in the absence of a sample sheet) that each group is in a single cell.

Use this (assuming data start in A2) copied across and down:

=FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,CHAR(10),"</B><B>",1),CHAR(10),"</B><B>",2),CHAR(10)," ")&"</B></A>","//B["&COLUMNS($B2:B2)&"]")

See sheet.

[Lokeshkumarmylapilli]

Hi Glenn,
Thank you for your quick response.
I tired attaching the sheet. There's some error popping .
However, my data is in not a single cell. Each line indicates a row here
For say,
Cameron International Corporation is in first row
Houston, Texas, is in second row
United States is in third row
$7,210.39M is in fourth row


Regards,
Lokesh

[Glenn Kennedy]

OK. How is this, then?

=IFERROR(INDEX($A$1:$A$8,4*(ROWS(C$1:C1)-1)+COLUMNS($C1:C1)),"")

In C1, copied across and down. if this is still ncorrect, you will HAVE to re-read the yellow banner and try again to attach a sample file.

[Lokeshkumarmylapilli]

Hi,
Worked well. Thank you Glenn

Appreciate your efforts!!


Lokesh

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.