Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 68

Thread: Database/List Manipulation

  1. #21
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by mperrah View Post
    re post #18
    maybe add an if statement in the loop so If account doesn't match but address does, combine those ?
    they would have to be in separate columns, can't combine...the filter search would be useless, it would be 00000-xxx00000-xxx.
    it would have to check against itself I guess and then add into the next open pricing slot, if duplicates.

    please see attached, that is a direct export, got rid of some content but left the columns as it really would be

    also to note, we have 49 pricing codes, but only 5 are frequently used, so once we get this going, I would add the remainder on the end of the columns for the rare use that they would show up. wont worry about that.
    Attached Files Attached Files

  2. #22
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    On your request:
    Sub M_snb() 
        sn = Sheet1.Cells(1).CurrentRegion.Resize(, 10) 
         
        With CreateObject("scripting.dictionary") 
            For j = 2 To UBound(sn) 
                c00 = Split(sn(j, 2), "-")(0) & "_" & sn(j, 3) & "_" & sn(j, 4) 
                 
                If .exists(c00) Then 
                    sp = .Item(c00) 
                Else 
                    sp = Application.Index(sn, j, Array(1, 2, 3, 4, 5, 6, 7, 8)) 
                End If 
                sp(Application.Match(sn(j, 10), Array("0000", "0004", "5001"), 0) + 5) = "'" & sn(j, 2) 
                 
                .Item(c00) = sp 
            Next 
             
            Sheet2.Cells(1).Resize(.Count, 8) = Application.Index(.items, 0, 0) 
        End With 
    End Sub
    Attached Files Attached Files
    Last edited by snb; 06-15-2015 at 02:13 PM.

  3. #23
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    ok, So what if we run the script to combine all matching company numbers and remove the duplicates,
    then start another pass that then checks for duplicate addresses

    where would this account number get moved too?

    add a new column at the end of the row?

    or insert a new one next to the pricing type (Normal/Discount/Gov)

    If you know in advance how many duplicates there will be we can format the sheet prior to running the script and have the pricing data adjacent,

    If you wont know how many duplicate address' may occur we can add a new column to the end of the row, regardless of how many.
    I like being able to account for all possible situations, your RealSample is laid out different then I expected,
    If you know what needs to go where we can help with the heavy lifting.

  4. #24
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by snb View Post
    On your request:
    hmm, that organizes it nicely yes, but it doesn't remove the duplicated (or show that it will remove duplicate addresses). I still see 10025 106th street 3x, with 3 different account #s, if that were on 1 line then yeah that would be good.


    Quote Originally Posted by mperrah View Post
    ok, So what if we run the script to combine all matching company numbers and remove the duplicates,
    then start another pass that then checks for duplicate addresses

    where would this account number get moved too?

    add a new column at the end of the row?

    or insert a new one next to the pricing type (Normal/Discount/Gov)

    If you know in advance how many duplicates there will be we can format the sheet prior to running the script and have the pricing data adjacent,

    If you wont know how many duplicate address' may occur we can add a new column to the end of the row, regardless of how many.
    I like being able to account for all possible situations, your RealSample is laid out different then I expected,
    If you know what needs to go where we can help with the heavy lifting.
    Yeah that is what I am basically looking for.
    Do you mean matching company numbers (account #s) that the first 6 digits are the same, then the only difference is the last 3? 123456-001, 123456-002?

    Ok – just accounted for all possible situations, I have formatted the realsample attachment to look like how I think it should. I have also attached the totals of the price classes (you can see if the real sample too, but may be easier to see in the smaller attachment).


    To help explain, '0000' is the code, 'List price' is the description of the code, and '14' is the number of duplicates, so we would need List price 1, 2,3,4,5,6 all to 14. So 14 columns. Which I have already done in the excel RealSample. Having that many columns isn't annoying to be honest, because all the vital information is before the first List Price 1, then once you see that information, you can scroll right to the price class account you're looking for! I like it!


    0000 List price 14


    And just to explain why there is 14, is that we have a buying group that helps out customers, so all the buying goes through one address. I would of set it up differently, but before my time!

    Hope that helps to explain more and give you a better base to start some of that heavy lifting you were offering !
    If it’s a lot of work to code each of the 33, just do a few of each and I can look at how to do the rest…that is a lot of redundant work lol.

    Thank you so much again!

    edit - just updated price class code.xls at 11:09EST, if you opened it before then, please re-download and open! thx
    Attached Files Attached Files

  5. #25
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You might have noticed that the data are very inconsistent. That explains that superficially 'identical' adresses are in fact different (check for spaces !!).

    I still see 10025 106th street 3x, with 3 different account #s, if that were on 1 line then yeah that would be good.
    I don't, because the file you uploaded doesn't contain any.
    Attached Files Attached Files
    Last edited by snb; 06-16-2015 at 08:25 AM.

  6. #26
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    ok, so you're segregating them correctly but not on 1 line.

    A 091001-000 company ABC 10025 106TH ST STE 105A 12 Alberta 10 West 5001 HCCI (DVA, NIHB) 091001-000
    A 101542-000 company ABC 10025 106TH ST STE 105A 12 Alberta 10 West 5 WCB alberta 101542-000
    A 001001-000 company ABC 10025 106TH ST STE 105A 12 Alberta 10 West 0 List price 001001-000

    should look like

    Status Account Company Company 2 Address 1 Address 2 City PC PR Lattitude Longitude Accuracy Level TE TE Description Region Region Description Phone Number Fax Website Email Sales Channel Sub-Channel Class IFA DUNS Manufacturers Price Class Price Class Description List price WCB BC HCCI (DVA, NIHB) WSIB ONTARIO WCB alberta
    A 091001-000 company ABC 10025 106TH ST STE 105A 12 Alberta 10 West 5001 HCCI (DVA, NIHB) 001001-000 091001-000 101542-000


    --

    however, will this code take into account my last post about 14 list prices, etc?

  7. #27
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    As snb said, your data on RealSample is very inconsistent.
    Many of the address values have an extra space in random areas, usually between the street name and suffix (Rd, St etc)
    This will make finding matches near to impossible.

    Also, you have 14 columns for list price, but your data leaves no indication which column it should go in?
    How do we know if its listprice 1 or listprice 14?

    and what does the last 3 digits of the account number signify?
    they don't seem to correspond to the price list

  8. #28
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    re post #25 from snb,
    his code worked for me, quite elegant.
    data output to sheet2, added a fix for extra spaces throughout the address list,
    added columns to the right and has the duplicate company items added there. nice work

    about the error, be sure you have a sheet named "Sheet2" maybe

  9. #29
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    That's true I did not notice it, but like you say, some dashes, extra spaces etc. I think we could go through that and make them all the same. Do you propose a sem-automatic way of doing it? I noticed in snb's code he had "- " to "-", etc.

    Assuming everything is the exact same, then we can find matches.

    There are only a few accounts that will need all 14, and the order is not of any importance, just need them to go into 1 slot, assuming start at 1 and if empty, put it in, etc.

    The last 3 digits are ship-to accounts. So sometimes you can have 123456-001,002,003. 001,002,003 are usually different addresses.

    100424-002 company ABC 2300 EGLINTON AVE W STE 401
    100424-003 company ABC 515 PARK RD N UNIT # 3


    edit - also just checked more data, and more of them seem to be exact matches than not, so it may not be a huge issue (I realize it still is one).


    Quote Originally Posted by mperrah View Post
    re post #25 from snb,
    his code worked for me, quite elegant.
    data output to sheet2, added a fix for extra spaces throughout the address list,
    added columns to the right and has the duplicate company items added there. nice work

    about the error, be sure you have a sheet named "Sheet2" maybe
    Yeah I had to re-open it, something messed up when I opened and saved. I replied with post #26, it is great, except not all on one line, like the comment you are inquiring about. (importance of which account goes into which slot of list price 1-14)

  10. #30
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I think this what you are looking for.
    Attached Files Attached Files

  11. #31
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    Next step is to sort snb's sheet2 by address1 and do another combine of account numbers by matching address.

  12. #32
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    another question,
    on the first pass we consolidate companies by account number regardless of address, loosing them (address) in the process
    edit: an address match was part of the first pass...

    Maybe we should sort and combine by address first,
    then combine the account number / pricing structure and find a way to keep the alternate address,
    in designated new column perhaps (to the right, as snb did for the account combining)
    Last edited by mperrah; 06-16-2015 at 11:08 AM.

  13. #33
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Hmm, getting there.

    So my steps were, open the document, open developer vba, ran the code, looked in Sheet2, created a button, assigned the macro, filtered Address 1 A-Z, ran the code.

    Now, when I filter by Address1, it still shows two 0000's and a 5001, I thought it would delete it? (or notify me that it would be deleted like a previous version of code)

    A 100704-001 company ABC 10324 152A ST 0000
    A 101121-001 company ABC 10324 152A ST 5001
    A 101154-000 company ABC 10324 152A ST 0000

    End result would still want to be 1 line though, so those 3 would be 1, but put into their proper columns..


    Quote Originally Posted by mperrah View Post
    another question,
    on the first pass we consolidate companies by account number regardless of address, loosing them (address) in the process
    if this is a mailing / emailing address function wont that defeat part of the purpose?
    Maybe we should sort and combine by address first,
    then combine the account number / pricing structure and find a way to keep the alternate address,
    in designated new column perhaps (to the right, as snb did for the account combining)
    or are the multiple address irrelevant?
    I might be a bit confused.

    If the address is the same for any accounts, consolidate on 1 line. (consolidate meaning, find the other account #'s with same address, and put into the applicable column based on price code)
    If the address is different than any other account then it can stay on a line by itself.

    One objective of this is for direct (snail) mailing promo's out to our customers, if we have 15 accounts with the same address, then we only need to send 1 promo out, except it would appear right now that we need to send 15.
    Second objective of this is for e-mailing promos out to our customers, if we have 15 accounts with the same address, then the emails will be the same also, (I export these emails and will put into this database at a later time).
    - side note for emails, depending on the input, maybe 1 of 15 accounts has an email associated with it, so if we picked #4 of 15, there may not be an email there, so if this was all on 1 line, they would have an email associated with it, even though it was only entered for 1 account.
    - I can then backwards import those emails/contact names back into the database where I exported this, and it's up to date, so all 15 will have the same info.

    Does that clarify? I didn't notice snb's code deleting anything, but if it does, then yes it is not useful in this application.

  14. #34
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    With the method I use it is impossible to get ''doubles'. If they seem identical it's because of sloppy data (cheack for spaces. invisible code, etc.).

    If identicalness is defined by company name & company address1 only: see the attachment.
    Attached Files Attached Files

  15. #35
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Ok yeah the duplicate I saw is now gone with that new code.

    However - I have no idea how to edit that code to work with the realsample I attached this morning. I would like to match up 33 price classes, and input them into the appropriate columns like you have done. are you able to comment in the code 'this is where your price classes are matched

    etc?

    i'm so out to lunch with this unfortunately.

    identicalness is defined by company address 1 actually, but when you added in company, the duplicate issue i found was gone.

    edit - I can se like j,3 is taking from Company column, and.Item(sn(j, 32)) is the price class column.

    if any combo of # is in there and matches, will it do what I want automatically?

    I will want to put List price #1 in slot 1, list price #2 in slot 2, etc. - and do that type of sorting for all of my price classes as indicated in the price code attachment earlier too.

    ive re-attached both
    Attached Files Attached Files

  16. #36
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Did you try my code in your really real sample ?
    It creates all different price classes automatically (provided they reside in column 32).

    Take your time to study the code.
    The more you understand the better you can use it.

  17. #37
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by snb View Post
    Did you try my code in your really real sample ?
    It creates all different price classes automatically (provided they reside in column 32).

    Take your time to study the code.
    The more you understand the better you can use it.
    I did try now...

    I am trying to understand a bit more, but not sure how it's going to work with the 1-14 like i said. I tried starting at column 10 which is the start of the first column 10 and doesn't work. I also tried with the end, and that also did not work..

    run-time error '1004: application-defined or object defined error

    doesnt highlight anything in the code

    Sub M_snb()
        Sheet1.Columns(5).Replace "  ", " "
        Sheet1.Columns(5).Replace "- ", "-"
        Sheet1.Columns(5).Replace " -", "-"
        
        sn = Sheet1.Cells(1).CurrentRegion
        
        With CreateObject("scripting.dictionary")
           For j = 3 To UBound(sn)
              .Item(sn(j, 132)) = sn(j, 133)
           Next
           Sheet1.Cells(1, 10).Resize(, .Count) = .items
           sq = .keys
           .RemoveAll
           
            sn = Sheet1.Cells(1).CurrentRegion
           
            For j = 3 To UBound(sn)
                c00 = sn(j, 3) & "_" & sn(j, 5)
                 
                If .exists(c00) Then
                    sp = .Item(c00)
                Else
                    sp = Application.Index(sn, j, [transpose(row(1:133))])
                End If
                sp(Application.Match(sn(j, 132), sq, 0) + 133) = sn(j, 2)
                 
                .Item(c00) = sp
            Next
            
            Sheet2.Cells(1).Resize(, UBound(sn, 2)) = Sheet1.Cells(1).CurrentRegion.Rows(1).Value
            Sheet2.Cells(2, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
        End With
    End Sub
    I also tried this, and switched them 1 for 1, and tried to see if they would add after column 133, still error

    Sub M_snb()
        Sheet1.Columns(5).Replace "  ", " "
        Sheet1.Columns(5).Replace "- ", "-"
        Sheet1.Columns(5).Replace " -", "-"
        
        sn = Sheet1.Cells(1).CurrentRegion
        
        With CreateObject("scripting.dictionary")
           For j = 3 To UBound(sn)
              .Item(sn(j, 132)) = sn(j, 133)
           Next
           Sheet1.Cells(1, 134).Resize(, .Count) = .items
           sq = .keys
           .RemoveAll
           
            sn = Sheet1.Cells(1).CurrentRegion
           
            For j = 3 To UBound(sn)
                c00 = sn(j, 3) & "_" & sn(j, 5)
                 
                If .exists(c00) Then
                    sp = .Item(c00)
                Else
                    sp = Application.Index(sn, j, [transpose(row(1:133))])
                End If
                sp(Application.Match(sn(j, 132), sq, 0) + 133) = sn(j, 2)
                 
                .Item(c00) = sp
            Next
            
            Sheet2.Cells(1).Resize(, UBound(sn, 2)) = Sheet1.Cells(1).CurrentRegion.Rows(1).Value
            Sheet2.Cells(2, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
        End With
    End Sub

  18. #38
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Use F8 to step through the code, to learn what it does and to provide detailed feedback.

  19. #39
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    error occurs at this point

     Sheet1.Cells(1, 134).Resize(, .Count) = .items
           sq = .keys

  20. #40
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    RealSample_snb_002b.xlsm
    This has the results of snb's macro relocated to the sheet you modified to house the data (sorted by address)
    some address info has been modified to conform.
    I just moved it manually for now, there are still the duplicates in the columns, but if this is the start of what you want I believe we can move forward.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •