Automatically sorting data

[leehutch]

I have a master list of customer names, addresses, postcodes an
financial details with which I want to sort by post code into differen
worksheets (to give me approx 25 sublists which will then be linked int
autoroute to show customer location by post code).

I can do this manually but I would like to find a way of setting up th
spreadsheet, so when I import an updated customer list each month i
automatically updates the sublists.

EXAMPLE MASTER LIS

-Postcode Customer name plus other data....-
BN1 Smith Ltd
BN1 Jones and Co
BN1 Brown and Son
BN2 Green Bros
BN2 Pink Inc

EXAMPLE SUBLIST 1 (WORKSHEET BN1

-Postcode Customer name plus other data....-
BN1 Smith Ltd
BN1 Jones and Co
BN1 Brown and Son

EXAMPLE SUBLIST 2 (WORKSHEET BN2

-Postcode Customer name plus other data....-
BN2 Green Bros
BN2 Pink Inc

I can put a reference to each postcode I am looking for in cell A1 o
each sublist, and I would like a function that looks for all occurance
of that postcode in the master list and copies all required data int
the sublist.

I have looked at the obvious VLOOKUP and macro options but I can
figure it out.

Can anyone help??

 

[Max]

One play ..
(Link to a sample file is provided below)

Assume the master list is in sheet: Master
in cols A to E, headers in row1, data from row2 down

Using 25 empty cols to the right of the data, say cols K to AI

Put in K1: BN1
Fill K1 across to AI1 (BN1, BN2, ... BN25)

Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across to AI2, fill down to say AI100,
to cover the max expected data in the master list

Click Insert > Name > Define
Put under "Names in workbook:": WSN

Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)

Click OK
(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan)

In a sheet named: BN1
With the same col headers in A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$1:$AI$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$AI$1,0)),ROWS($A$1:A1)),OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$
1:$AI$1,0)),0)))

Copy A2 across to E2, fill down to E100
(cover the same range size as was done in "Master"' cols K to AI)

Cols A to E will return only the lines for postcode: BN1 from "Master",
all neatly bunched at the top

Now, just make a copy of the sheet: BN1, rename it as: BN2
and you'd get the results for BN2.

Repeat the copy > rename sheet process to get the rest of the 25 postcodes'
sheets

Here's the link to a sample file with the implemented construct:
http://www.savefile.com/files/9975448
File: Automatically sorting data_LeeHutch_misc.xls

 

[HiArt]

Hi Max,

something along the lines of the following should do the trick


Code:
--------------------

Public Sub SplitPC()

'This macro creates sublists based on the data in Sheet1.
'Assumes postcode is first 3 characters of data string.

'Declarations
Dim lngLastRow As Long
Dim strCopy As String
Dim strStoredPC As String

'Find last row allowing for empty rows
lngLastRow = Rows.Count
If Cells(Rows.Count, 1).Value = "" Then
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
End If

'Sort the data
ActiveSheet.Range("A1:A" & lngLastRow).Sort _
Key1:=Worksheets("Sheet1").Range("A1")

'Add sheet for first sublist
Sheets.Add After:=Sheets(Sheets.Count)

'Position Cursor at the start of the data
Sheets(1).Select
ActiveSheet.Range("A1").Activate
ActiveSheet.Range("A1").Select

'Store postcode for subsequent comparison
strStoredPC = Left(ActiveCell.Value, 3)

'rename sublist sheet based on stored postcode
Worksheets(Sheets.Count).Name = strStoredPC

'Copy data in to first sublist
ActiveCell.Copy
Sheets(Sheets.Count).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets(1).Select
ActiveCell.Offset(1, 0).Select

'Walk the remaining data, each time the post code changes add a new sheet
Do Until ActiveCell.Value = "" 'stop at first empty cell
'Check for new postcode
If Left(ActiveCell.Value, 3) = strStoredPC Then
'Postcode has not changed, do nothing
Else
'Postcode has changed, set-up new sublist
strStoredPC = Left(ActiveCell.Value, 3)
Sheets.Add After:=Sheets(Sheets.Count)
Worksheets(Sheets.Count).Name = strStoredPC
Sheets(1).Select
End If

'Copy data in to sublist
ActiveCell.Copy
Sheets(Sheets.Count).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets(1).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

--------------------


HTH

Art

 

[Max]

Hi Max, ...

... I believe your sub was meant for the OP "leehutch" <g> ?

But thanks for the offering ! I tried it and it works fine. The sub
assumes that the data is all sitting in a single column, as per line below.

'Assumes postcode is first 3 characters of data string.

I had read the orig. post as hinting that the data was already sliced into
several columns in the "master list", instead of in a single column.

If this is the case, and assuming the data sits in a sheet: Master, in cols
A to E, headers in row1, data from row2 down (those were my assumptions),
how could your sub be amended to process the desired outcome in this
situation ?

Thanks

 

[Max]

.. how could your sub be amended to process the desired outcome ..

The orig. headers in A1:E1 in Master will also need to be copied over by the
sub to form the headers in each of the 25 sublist sheets created