• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

not sure how to ask for what I want

B

birdiegirl

New Member
Hi, I am birdiegirl, and I am not sure how to ask for what I want. I have a spreadsheet that lists 175 different addresses, but the addresses repeat for each unique person at that address. So, the total line items in somewhere in the 865 range. I want to figure out how to cull that master list down to each unique address.
I am a relative newb to Excel in that I have only used a few features so the things I do use, I can use well, but then I need to do something different and I flounder trying to figure out what/where to go.
What I do now, is copy the list to another sheet, delete the columns that have the person's name in them until I am left with just the addresses.. Then I tell Excel to remove duplicates, but it never does remove all of them. and for the life of me, I cannot figure out what is there that is telling Excel that it is unique from the line right above it. So I look over the list until I have all unique addresses.
This may be the best way, and if so, I will continue as is, but I wanted to ask if there is a better way.
ultimately, I want to be able to get this list, then print up mailing labels for each of the addresses each week.
also, I am just looking for a little guidance, not for someone to do it for me, I want to learn but I am not sure if I should look in a different direction than I have already.
my column headers are:
OFFICE CODEREGIONDESIGNATIONADVISOR #First nameLast nameAddress Line 1Address Line 2Address Line 3CityStateZipDA OFFICE CODE

Thanks for taking the time to read this and any responses are appreciated.

Birdiegirl
 
Post a copy of your workbook with SAMPLE data - nothing confidential.

We'll take a look at what you have.
 
Hi, birdiegirl!

If you only want to get unique address your initial approach is correct.

As you still gets apparently repeated addresses I'd bet on its apparience and not on its exact equality. Usual causes are leading, trailing or embedded spaces, punctuation, capital letters, and so on.

As an advice the approach you took is correct but only for eventual use. If you say you're going to do the same process each week, I'd go for a macro way. It's nothing from the outter space but perhaps not for very newbies at Excel. A sample file will help as you were yet told.

Regards!

PS: However this thread shouldn't have been posted at Lounge but at Ask Excel a Question, or at VBA Macros eventually.

PS 2: How do I perform this operation? I'm not sure I remember how... :rolleyes:
 
SirJB7 said:
Hi, birdiegirl!

If you only want to get unique address your initial approach is correct.

As you still gets apparently repeated addresses I'd bet on its apparience and not on its exact equality. Usual causes are leading, trailing or embedded spaces, punctuation, capital letters, and so on.

As an advice the approach you took is correct but only for eventual use. If you say you're going to do the same process each week, I'd go for a macro way. It's nothing from the outter space but perhaps not for very newbies at Excel. A sample file will help as you were yet told.

Regards!
Click to expand...

loaded the file just now, I will check out Macro's and learn what I can, it Excel can do what I do for me as a macro, I'm game to learn it. thanks for the suggestion I appreciate it
 
Hi, birdiegirl!

Check the returned file. In 2nd worksheet there are 2 paris of apparently equal addresses. Yellow shaded.
2280 Raleigh Court
2380 Raleigh Court
475 Providence Main Street NW
475 Providence Main Street

If you can wait a couple of hours I'll try to add the macro approach. Please exploit the gap and define exactly any other requirement.

Regards!
 

Attachments

  • sample for chandoo forum.xlsx
    26.1 KB · Views: 4
SirJB7 said:
Hi, birdiegirl!

Check the returned file. In 2nd worksheet there are 2 paris of apparently equal addresses. Yellow shaded.
2280 Raleigh Court
2380 Raleigh Court
475 Providence Main Street NW
475 Providence Main Street

If you can wait a couple of hours I'll try to add the macro approach. Please exploit the gap and define exactly any other requirement.

Regards!
Click to expand...

Yes, both of those addresses should have been the same as each other. I have a number of clerks that imput information and it can be a challenge to get them to not leave a hidden space or to put the NW after the street and not before.
 
Hi, birdiegirl!
While you're still wondering if the original specs are the right ones, I have a couple of doubts:
a) the mailing labels that you print regularly which fields are using? only those of address (columns G:L) or any other like A:C or M columns?
b) how do you print the label? could you upload a sample image indicating size?
Regards!
 
SirJB7 said:
Hi, birdiegirl!
While you're still wondering if the original specs are the right ones, I have a couple of doubts:
a) the mailing labels that you print regularly which fields are using? only those of address (columns G:L) or any other like A:C or M columns?
b) how do you print the label? could you upload a sample image indicating size?
Regards!
Click to expand...
I actually will use
A
G
I
H
J,K,L
on a 2x4 white avery label or equivalent (10 to a page)
currently, I have an access file that I have to update everytime there is a change to an address and that is the file I use to create the labels.
I am looking to create labels from an Excel file as my company is moving away from access.
 
SirJB7, I apologize that I posted in the wrong room. I will make sure any future questions will be in the correct room.
Thanks :)
 
Hi, birdiegirl!
Check the uploaded file. Press green button in 1st worksheet, give a look at 2nd worksheet, print 3rd worksheet.
This is the macro code:
Code: 
Option Explicit

Sub UniqueAddressesPrinted2x5()
    '
    ' constants
    '  worksheets
    Const ksWSData = "Sheet1"
    Const ksWSAddresses = "Addresses"
    Const ksWSLabels = "Labels"
    '  ranges
    Const ksRngInput = "A:A,G:L"
    Const ksRngOutput = "A:G"
    '  labels
    Const kiLabelRowsPerPage = 5
    Const kiLabelColumnsPerPage = 2
    Const kiLabelRows = 6
    Const kiLabelColumns = 2
    '
    ' declarations
    Dim rngI As Range, rngO As Range, rngL As Range
    Dim I As Integer, J As Integer, K As Integer
    Dim iRowBase As Integer, iColBase As Integer
    '
    ' start
    '  ranges
    Set rngI = Worksheets(ksWSData).Range(ksRngInput)
    Set rngO = Worksheets(ksWSAddresses).Range(ksRngOutput)
    Set rngL = Worksheets(ksWSLabels).Cells
    rngO.ClearContents
    rngL.ClearContents
    '
    ' process
    '  copy data
    Worksheets(ksWSData).Activate
    rngI.Copy
    Worksheets(ksWSAddresses).Activate
    rngO.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    '  make up
    rngO.AutoFilter
    rngO.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    '  remove duplicates
    rngO.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
    '  format print
    Worksheets(ksWSLabels).Activate
    '   addresses counter
    I = 2
    '   labels counters
    '    row
    J = 0
    '    column
    K = kiLabelColumns
    '    build
    With rngO
        Do Until .Cells(I, 2).Value = ""
            ' position
            K = K + 1
            If K > kiLabelColumns Then
                K = 1
                J = J + 1
            End If
            iRowBase = (J - 1) * kiLabelRows
            iColBase = (K - 1) * kiLabelColumns
            ' fields
            rngL.Cells(iRowBase + 2, iColBase + 2).Value = .Cells(I, 1).Value
            rngL.Cells(iRowBase + 3, iColBase + 2).Value = .Cells(I, 2).Value
            rngL.Cells(iRowBase + 4, iColBase + 2).Value = .Cells(I, 4).Value
            rngL.Cells(iRowBase + 5, iColBase + 2).Value = .Cells(I, 3).Value
            rngL.Cells(iRowBase + 6, iColBase + 2).Value = _
                .Cells(I, 5).Value & ", " & .Cells(I, 6).Value & ", " & .Cells(I, 7).Value
            ' cycle
            I = I + 1
        Loop
    End With
    '
    ' end
    '  ranges
    Set rngL = Nothing
    Set rngO = Nothing
    Set rngI = Nothing
    '
End Sub
Regards!
 

Attachments

  • sample for chandoo forum (for birdiegirl at Chandoo.org).xlsm
    46.3 KB · Views: 9
SirJB7 said:
Hi, birdiegirl!
Check the uploaded file. Press green button in 1st worksheet, give a look at 2nd worksheet, print 3rd worksheet.
This is the macro code:
Code: 
Option Explicit

Sub UniqueAddressesPrinted2x5()
    '
    ' constants
    '  worksheets
    Const ksWSData = "Sheet1"
    Const ksWSAddresses = "Addresses"
    Const ksWSLabels = "Labels"
    '  ranges
    Const ksRngInput = "A:A,G:L"
    Const ksRngOutput = "A:G"
    '  labels
    Const kiLabelRowsPerPage = 5
    Const kiLabelColumnsPerPage = 2
    Const kiLabelRows = 6
    Const kiLabelColumns = 2
    '
    ' declarations
    Dim rngI As Range, rngO As Range, rngL As Range
    Dim I As Integer, J As Integer, K As Integer
    Dim iRowBase As Integer, iColBase As Integer
    '
    ' start
    '  ranges
    Set rngI = Worksheets(ksWSData).Range(ksRngInput)
    Set rngO = Worksheets(ksWSAddresses).Range(ksRngOutput)
    Set rngL = Worksheets(ksWSLabels).Cells
    rngO.ClearContents
    rngL.ClearContents
    '
    ' process
    '  copy data
    Worksheets(ksWSData).Activate
    rngI.Copy
    Worksheets(ksWSAddresses).Activate
    rngO.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    '  make up
    rngO.AutoFilter
    rngO.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    '  remove duplicates
    rngO.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
    '  format print
    Worksheets(ksWSLabels).Activate
    '   addresses counter
    I = 2
    '   labels counters
    '    row
    J = 0
    '    column
    K = kiLabelColumns
    '    build
    With rngO
        Do Until .Cells(I, 2).Value = ""
            ' position
            K = K + 1
            If K > kiLabelColumns Then
                K = 1
                J = J + 1
            End If
            iRowBase = (J - 1) * kiLabelRows
            iColBase = (K - 1) * kiLabelColumns
            ' fields
            rngL.Cells(iRowBase + 2, iColBase + 2).Value = .Cells(I, 1).Value
            rngL.Cells(iRowBase + 3, iColBase + 2).Value = .Cells(I, 2).Value
            rngL.Cells(iRowBase + 4, iColBase + 2).Value = .Cells(I, 4).Value
            rngL.Cells(iRowBase + 5, iColBase + 2).Value = .Cells(I, 3).Value
            rngL.Cells(iRowBase + 6, iColBase + 2).Value = _
                .Cells(I, 5).Value & ", " & .Cells(I, 6).Value & ", " & .Cells(I, 7).Value
            ' cycle
            I = I + 1
        Loop
    End With
    '
    ' end
    '  ranges
    Set rngL = Nothing
    Set rngO = Nothing
    Set rngI = Nothing
    '
End Sub
Regards!
Click to expand...

Thank you so much for the quick replies, I will study the macro code and see what I can learn, but yea, this is doing exactly what I need it to do and should be able to print labels out just fine.

Thank you again

Birdiegirl
 
Hi, birdiegirl!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top