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):
Excel_Formatting.jpg
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top