MrExcel.com - Your One Stop for Excel Tips & Solutions

I am trying to format text one column into diffrent rows & columns

Posted by Travis P on February 07, 2002 11:04 PM

I am trying to format some text into separate columns. The text is in one column as follows:

Column A
Company Name A
PO BOX 1
NEWARK NJ 07101

Company Name B
PO BOX 2
1400 SANS SOUCI PARKWAY
WILKES-BARRE PA 18703

Company Name C
PO BOX 3
CHICAGO IL 60694

Company Name D
PO BOX 4
CLEARFIELD UT 84015

Company Name E
PO BOX 5
OGDEN UT 84401

Company Name F
PO BOX 6
BALTIMORE MD 21264-4351

When I formatted the test to excel I made this column as illustrated above.

I would like to know of a formula to which I can make the data appear on one row rather than in multiple rows for on company the formula should result if giving me that following in one row but in different columns:

All on the same ROW

Column A
Company Name A

Column B
PO BOX 1

Column C
NEWARK NJ 07101

I have use vlookups and hlookups and pivot tables but I can’t figure out how to get the date if a row-by-row way.

I have been able to get company names to appear row-by-row in one column by sorting and I figured that I could use a combination of a vlookup with a hlookup to give me the results I was looking for but I do not know the proper formula to nest in the ether lookup. I’v also tested some DSUM function but none are working for me? So please let me know what or how to fix my problem….

Posted by computerfreaks on February 08, 2002 1:48 AM

There is no way that you can make Excel recognize the difference between what you have written, I'm guessing the original columns have all the info for each company in a cell each? If so you can't expect excel to be able to differentiate between the two without some form of input mask.

What i think you may be able to do, I'll have to look into this, is make excel treat a certain character as being the divide between an array of data, for example:

2345/234 23 2 / 7342812

could be split into the following:

2345
234 23 2
7342812

I may be getting confused with Java programming, but I'll check. Otherwise you need to change the way the original data is stored.

Posted by Derek on February 08, 2002 8:41 AM

Travis
Can be done by formula but rather complicated.
This macro code should do the trick. Select all the address blocks in your column (from first to last entry inclusive) before running the macro. It will dump it into columns B,C,D,E...etc.
Macro assumes the cell/s separating your address block are blank.
Hope this helps
Derek

Selection.Select
For Each cell In Selection:
If cell.Value > "" And cell.Offset(-1, 0).Value = "" Then
Range(cell, cell.End(xlDown)).Copy
Range("B65536").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
End If
Next cell
End Sub

Posted by Derek on February 08, 2002 8:43 AM

Travis
This can be done by formula but it is a bit complicated. Here is some macro code that should do the trick. It assumes your column of data is in column A and it will dump the result into Columns B,C,D,E...etc (your data can actually be in any column except those columns that will receive the result). Before starting your macro you must select the data in your column (from and including your first entry to last entry). It works on the assumption that the cells separating your address blocks are completely blank cells.

Selection.Select
For Each cell In Selection:
If cell.Value > "" And cell.Offset(-1, 0).Value = "" Then
Range(cell, cell.End(xlDown)).Copy
Range("B65536").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
End If
Next cell
End Sub

Hope this helps
Derek

Posted by Derek on February 09, 2002 6:12 AM

Re: Here's how to do it by formula

Travis
You need to use 3 extra columns for this (B:D) and your answers will use columns E:K.

Range A1:D1 should be blank
Your address list should be in column A, starting A2, with A1 blank.
Copy and paste the following formulas into the cells indicated and scroll them as indicated
B2 FORMULA (scroll down) IS: =IF(AND(A2>"",A1=""),"E",IF(B1="E","F",IF(B1="F","G",IF(B1="G","H",IF(B1="H","I",IF(B1="I","J",IF(B1="J","K","E")))))))
C2 FORMULA (scroll down) IS: =IF(AND(A2>"",A1=""),1+C1,C1)
D2 FORMULA (scroll down) IS: ="$"&B2&"$"&C2
E1 FROMULA (scroll right & down) IS: =IF(ISERROR(INDIRECT("A"&MATCH(CELL("ADDRESS",E1),$D:$D,0))),"",IF(INDIRECT("A"&MATCH(CELL("ADDRESS",E1),$D:$D,0))=0,"",INDIRECT("A"&MATCH(CELL("ADDRESS",E1),$D:$D,0))))

There are probably several other ways to do it, this is just one way.
Derek