Excel veterans: Can a file like this be sorted?

Anmorphic

New Member
Joined
Dec 21, 2005
Messages
4
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):
 

Some videos you may like

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
Joined
Dec 15, 2003
Messages
154
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
Joined
Dec 15, 2003
Messages
154
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
Joined
Dec 15, 2003
Messages
154

ADVERTISEMENT

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
Joined
May 15, 2003
Messages
29,536
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
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

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
Sheets.Add after:=DataWorksheet
Set ReFormattedsheet = ActiveSheet
Range("A1") = "Last Name"
Range("B1") = "Full Name"
Range("C1") = "Street Address"
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
Joined
Dec 21, 2005
Messages
4
MnPoker and Barrie thank you for the prompt reply. You guys are geniuses

Barrie....WOW. :eek: 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
Joined
Dec 15, 2003
Messages
154
Anmorphic said:
MnPoker and Barry thank you for the prompt reply. You guys are geniuses

Barry....WOW. :eek: 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
Joined
Dec 21, 2005
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,384
Members
412,590
Latest member
Velly
Top