Excel help

slinky malinky

slinky malinky

slinky malinky

Suspended
Joined
28 Mar 2003
Posts
1,740
:confused:

I have a spreadsheet with addresses and postcodes and other details. This issue i have is that some of the postcodes are in the wrong columns, as are some of the other address fields. Is there an easy way of searching for say the postcode format and moving it to a specific collumn.
 
You just want to group them according to area is it?

What if you put them into one long list first and then separate them out again according to whatever grouping system you're using?

Might be easier to understand what you're after if you showed us an example of what you're working with and an example of how you want it to look.
 
Sorry, i probably did not explain myself very well :p

Untitled.jpg



I want all the postcodes in column J
 
hmm. column k (postcodes), formula "if cell j is blank, get value from cell i, else j"
select column k and "replace formula with values"
column L (town), formula "if cell j is blank value = "", else get value from cell i"
again select L and replace formula with values
delete columns i & j.

that make sense?
 
Last edited:
Probably be best to do the above via use of a macro and just have it go through the entire spreadsheet.

It is the case that everytime the cell in column J is empty the postcode is in the cell in column I?

Edit:

If the above condition is true then this should work, just change the value to which 'Count' goes to ie something greater than the number of the last row.

Code: 
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 26/05/2010 by Antar Bolaeisk:p
'
' Keyboard Shortcut: Ctrl+q
'

Count = 1

Do While Count <= 20

If IsEmpty(Cells(Count, 10)) = True Then

    Cells(Count, 9).Select
    Selection.Cut Destination:=Cells(Count, 10)
    Count = Count + 1
    
    Else
    Count = Count + 1
    End If
    
    Loop
        
End Sub
 
Last edited:
Antar Bolaeisk said:
Probably be best to do the above via use of a macro and just have it go through the entire spreadsheet.

It is the case that everytime the cell in column J is empty the postcode is in the cell in column I?
Click to expand...

most of the time yes, but there are other errors but i could correct those manually. when you say use a macro, do you mean record one or write one?
 
Was just editing my above post as you made that one :)

The macro posted above will copy the contents of the 'I' cell into the 'J' cell if the 'J' cell is empty.
 
Antar Bolaeisk said:
Was just editing my above post as you made that one :)

The macro posted above will copy the contents of the 'I' cell into the 'J' cell if the 'J' cell is empty.
Click to expand...

That works assuming there is a postcode in cell 'I'.

If there's not too many, do it manually by sorting the sheet on column I.
This would group the postcodes together and then just manually go down the list shifting blocks of cells to the right.
 
You could always use "if(len" too assuming all the cells which contain a postcode are 6 or 7 characters long.
 
Tomsk said:
That works assuming there is a postcode in cell 'I'.

If there's not too many, do it manually by sorting the sheet on column I.
This would group the postcodes together and then just manually go down the list shifting blocks of cells to the right.
Click to expand...

Yeah, I feel silly now as the outcome of that macro is easily achievable by just sorting the sheet to isolate the empty cells then copying in the missing data. Oh well.
 
slinky, was this an import issue from a txt file or something? If no other modifications were made yet, update the original document with an extra delimiter in those affected spots, then re-import it.

Of course, it may be too late to do that now. :)
 
As above, I assume you have a list of the address's someplace, and havent broken them up the way you like?

You would need to show us the original file you are importing from.

If its just:-

20 Charlton Way
Hartford
London
W7...
Click to expand...

Then as above, its the way you have imported your text.

If there was previously commas, that would help in the import.

If it was just one line with no breaks other than spaces, well then its a whole different kettle of fish.
 
ok, thanks for all the advice. a bit more information. the data, about 29,000 address records, was exported from a database to be input into another database. in the original database there is no postcode field specifically so when the address details have been input it has not been done the same each time, hence the postcode appears in different columns.

let me have a play around with that macro and see if i can get it looking better.
 
You must log in or register to reply here.
Back
Top Bottom