# Excel veterans: Can a file like this be sorted?

#### Anmorphic

##### New Member
Hello everyone, I've got an Excel spreadsheet which has customer data from an older program in a "report" style. I wish to sort the spreadsheet by separating the customer data into separate columns, however the format the spreadsheet is in is very, umm, difficult. I need to figure out how to write a sorting algorithm to sort this data. Below I show the format it's in, and the format I would like it to be sorted into. Thank you in advance, any help or tips would be fantastic!

Existing Format of Data (note there are two empty lines between name and one empty line between next customer data):

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### MNpoker

##### Board Regular
Insert column A and enter the following formulas (these are the formulas as the appear in row 3.

Column A:
=IF(B3="LastName",1+A2,A2)

Column F: (next to the data for FullName, Street, etc)
=IF(D2="Full Name",B2,D3)

Column G:
=A3&F3

Column H
=IF(D2="Full Name",C2,E3)

Column A will now look like this:
Book1.xls
ABCD
1RecordABC
21LastNameNelsonFullName
31
41
51Street
61City,StateZip
71Tel:
81Fax:
91
102LastNameSandersFullName
112
122
132Street
142City,StateZip
152Tel:
162Fax:
172
183LastNameJohnsonFullName
193
203
213Street
223City,StateZip
233Tel:
243Fax:
Sheet1

#### MNpoker

##### Board Regular
Now you will need to put in a new table with each header (keep City State Zip together for now)

The formulas here are:
Row 28 Has each category

Column A has a list 1 through the number of records.

column B through G have the formulas:
=VLOOKUP(\$A29&B\$28,\$G\$1:\$H\$24,2,FALSE)

And this will be your result
Book1.xls
ABCDEFG
28FullNameLastNameStreetTel:Fax:City,StateZip
291DavisNelsonABC1213A,B90210
302AlexSandersDEF1415C,D,10000
313JimmyJohnsonGHI1617E,F20000
Sheet1

#### MNpoker

##### Board Regular

Now you will have to have a way to extract the City, State and Zip info from column G.

What does the data look like? (You may have to paste special values and use text to columns or something)

Sorry my images look so bad, I'm not sure how to fix them.

#### Smitty

##### Legend
Sorry my images look so bad, I'm not sure how to fix them.
When the HTML Maker creates the new browser window, select "View Source", then copy the HTML code that opens in the new window and paste that into your post. That'll get rid of the "&nbsp".

Anmorphic...Welcome to the Board!

Will there always be two rows between Full Name and Street or are there inconsistencies in the data?

Smitty

#### Barrie Davidson

##### MrExcel MVP

Welcome to MrExcel!

The following code will create a new worksheet and get the data in a row format. The only thing I didn't get was how to extract City, State, and Zip.
Code:
``````Option Explicit

Sub ReFormatData()
Dim DataWorksheet As Worksheet
Dim ReFormattedsheet As Worksheet
Dim LastRow As Long, i As Long

LastRow = Range("A65536").End(xlUp).Row + 6
Set DataWorksheet = ActiveSheet
Set ReFormattedsheet = ActiveSheet
Range("A1") = "Last Name"
Range("B1") = "Full Name"
Range("D1") = "City"
Range("E1") = "State"
Range("F1") = "Zip"
Range("G1") = "Telephone"
Range("H1") = "Fax"
For i = 1 To (LastRow - 1) / 7
ReFormattedsheet.Cells(i + 1, 1) = DataWorksheet.Cells((i - 1) * 7 + i, 1) 'Last name
ReFormattedsheet.Cells(i + 1, 2) = DataWorksheet.Cells((i - 1) * 7 + i, 3) 'Full Name
ReFormattedsheet.Cells(i + 1, 3) = DataWorksheet.Cells((i - 1) * 7 + i + 3, 3) 'Street Address
ReFormattedsheet.Cells(i + 1, 4) = DataWorksheet.Cells((i - 1) * 7 + i + 4, 3) 'City
ReFormattedsheet.Cells(i + 1, 7) = DataWorksheet.Cells((i - 1) * 7 + i + 5, 3) 'Telephone
ReFormattedsheet.Cells(i + 1, 8) = DataWorksheet.Cells((i - 1) * 7 + i + 6, 3) 'Fax
Next i

End Sub``````

Post back with an explanation on how to extract City, State, and Zip and I (or someone else) will tweak the code.

Hope this helps.

#### Anmorphic

##### New Member
MnPoker and Barrie thank you for the prompt reply. You guys are geniuses

Barrie....WOW. I had to tweak your code a bit because I gave misinformation, it turns out telephone and fax are in column 4 so I changed telephone and fax to column 4 in your VB Script and it worked beautifully.

Thank you Everyone for the reply.

In response to your question Barry regarding the City State and Zip Code
Here is how 99% are formatted.

Format: City, CA Zip
Ex) Woodland, CA 95776

The other 1% have just a single , (comma in them)

I'm a newbie when it comes to VB Script but I'll play with your code and see if I can figure it out Barry. Thank you again to everyone!

#### MNpoker

##### Board Regular
Anmorphic said:
MnPoker and Barry thank you for the prompt reply. You guys are geniuses

Barry....WOW. I had to tweak your code a bit because I gave misinformation, it turns out telephone and fax are in column 4 so I changed telephone and fax to column 4 in your VB Script and it worked beautifully.

Thank you Everyone for the reply.

In response to your question Barry regarding the City State and Zip Code
Here is how 99% are formatted.

Format: City, CA Zip
Ex) Woodland, CA 95776

The other 1% have just a single , (comma in them)

I'm a newbie when it comes to VB Script but I'll play with your code and see if I can figure it out Barry. Thank you again to everyone!

It's fortunate that your data is this way (States are all 2 letter Abbreviations 'I hope' if not true let me know)

In Column H, I, J (or next to your final column)
' I'll assume the data for City State Zip is in G

Column H:
=Left(G3,find(",",g3)-1)

Column I
=mid(G3,find(",",G3)+2,2)

Column J
=mid(G3,find(",",G3)+5,len(G3)-find(",",G3)+3)
On this one this whole part 'len(G3)-find(",",G3)+3' can be changed to 5 if all the Zip codes are in the 5 digit format

#### Anmorphic

##### New Member
Thanks MNPoker!

That did the trick.

On a totally separate question, does anyone know how I could delete the reference column yet keep the value which was obtained from it in the requesting column?

In other words, I don't need a City, STATE ZIP column any more but when I delete it the new City, State, and Zip columns produce function errors since their referring column is gone.

Replies
8
Views
125
Replies
1
Views
127
Replies
1
Views
100
Replies
8
Views
389
Replies
9
Views
149