Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 68

Thread: Database/List Manipulation

  1. #41
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Quote Originally Posted by DarinM View Post
    Hi Sam,

    I know - it's insane. Partially the problem because our CRM was created in Germany for their database, which only has one account # and one pricing. The USA has the same database, and way less discounts. I am in Canada where we have 'free' healthcare or heavily discounted, hense all the -000's. However, some clinics have different accounts for different purchases, which I think is insane, because we are doing their accounting for them...but whatever haha. This is why I am trying to manipulate the exported data into something a bit more useful for my department.

    I believe everything on 1 line would be much better because eventually when I do v-lookups to edit this database, i won't be able to do that as well with different tabs. Also, I'll be using this list from a marketing perspective where I need their e-mail addresses and different mailing addresses.

    I will see if that would be useful but I am slightly skeptical .

    Mperrah,

    that is what I envisioned, except yours looks a bit redundant? Instead of account 1/2/3/4, and listing the same account # in the pricing columns, why wouldn't we just put the 'main' (whatever we determine is the main) in account 1, then all the rest account #s into the pricing columns? again this is really only for the accounts that have the same mailing address.

    There may only be ~50-75 accounts that have different account #s, with different pricing with same mail address, but there are 1000's of same account #s but with different pricing. Almost every account will have 3 pricing accounts, regular, discounted and government.

    012345-000 , -001, -004 etc.

    Does that make anymore sense?


    edit - also to note, the database where I am exporting this from - gets updated if not daily, then weekly. so whatever we do, having a way to export and then re-organize it into this fashion is the most important part, but we can look at that later, figuring out what the layout will be and seeing if we can copy values into certain columns then deleted the row we just copied from is a starting point...!

    Darin
    I see I left some confusion there.

    Those suggested tables are not what you separate your incoming data into, rather they are permanent tables that only get updated when your client list changes.

    Your incoming data would be processed against those lookup databases. The only pre-processing needed on the incoming data would be to split the account numbers on the "-" into 2 new columns (3 columns: Full Acctt#, 6 digit # and 3 digit suffix.

    For Marketing purposes have an Email database.

    According to the KISS Principle:
    The accounting calculations only need to know the Full acct # and the Pricing Classification.
    Only above that, do they need to know the Billing and Ship To addresses
    Marketing needs to know the Email addresses.

    I m sure that I left out a lot, but break your needs down that way.

    I looked at your RealSample book, but I didn't see any Accounting figures in there, so I am still in the dark, or at least, Twilight about what you do and need. We here at VBAX have a combined total of hundreds of years experience in many different fields of business. Don't be afraid to ask for the best way to use Office to handle a particular business task. Someone will know.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  2. #42
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    DarinM,
    I think snb was trying to say his code will look at all the different price types in the column and generate a new column at the end of the row for each type.

    How you adjusted your sheet to accommodate the 33 types and the 100 variances is not necessary (and would change each quarter anyway I believe),
    his code will do it with what you have in your raw data.

    I think I see what you are talking about with 1-14. For same account numbers, with same address, same price type > if more than one match:
    you need a separate column added for each (sometimes up to 14) and stay in same row.

    snb: could you use/make a header with a unique name as an index to find the column for inserting the matches, and if not empty create an iterated copy List(i), WCB BC(i) etc
    like:
    fill array with unique price types = headernames
    get match for account# and address then copy row 
    for x = lbound(headernames) to ubound(headernames)
    if headernames(x) isEmpty then
    paste matched.row there
    else 
    insert column 
    cells(1, x +1).value = headername(x) & i
    i = i +1
    paste matched.row now
    this is just an idea map to try coding, if you get the thought process??

  3. #43
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    In VBA
    lesson 1: never use merged cells
    lesson 2: avoid 'Select' and 'Activate'
    lesson 3: avoid interaction with the workbook/worksheet as much as possible

    Your file doesn't meet the criteria in lesson 1.

    I think I provided all the information to answer your question.
    Since the information you provided changed very often I think you best do the finetuning yourself.
    If you prefer this commercial assignment to be developed further by me, you can use the paypal button in my website.
    If I were your (not very poor) employer I'd prefer the latter.

    PS. The inaccuracy/inconsistency of the data in your CRM-system baffles me.

  4. #44
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by mperrah View Post
    Attachment 13712
    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.
    Yes that is exactly what I want, and just to reinforce ... when finished the 2 lines below, would become 1, and "102179-015" would be in list 2 column

    A 090169-000 company ABC 1115-C AUSTIN AVE 000169-000
    A 102179-015 company ABC 1115-C AUSTIN AVE 102179-015

  5. #45
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by mperrah View Post
    DarinM,
    I think snb was trying to say his code will look at all the different price types in the column and generate a new column at the end of the row for each type.

    How you adjusted your sheet to accommodate the 33 types and the 100 variances is not necessary (and would change each quarter anyway I believe),
    his code will do it with what you have in your raw data.

    I think I see what you are talking about with 1-14. For same account numbers, with same address, same price type > if more than one match:
    you need a separate column added for each (sometimes up to 14) and stay in same row.

    snb: could you use/make a header with a unique name as an index to find the column for inserting the matches, and if not empty create an iterated copy List(i), WCB BC(i) etc
    like:
    fill array with unique price types = headernames
    get match for account# and address then copy row 
    for x = lbound(headernames) to ubound(headernames)
    if headernames(x) isEmpty then
    paste matched.row there
    else 
    insert column 
    cells(1, x +1).value = headername(x) & i
    i = i +1
    paste matched.row now
    this is just an idea map to try coding, if you get the thought process??
    Hi mperrah,

    these wouldn't change each quarter, they're account #s and only rarely will they close, or new ones be opened. - if I understand your point correctly.

    I would run this macro from an entire database dump, which would always include those 1-14, so having new code that would add columns in 'if empty', isn't really necessary. would be handy though.

  6. #46
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by snb View Post
    In VBA
    lesson 1: never use merged cells
    lesson 2: avoid 'Select' and 'Activate'
    lesson 3: avoid interaction with the workbook/worksheet as much as possible

    Your file doesn't meet the criteria in lesson 1.

    I think I provided all the information to answer your question.
    Since the information you provided changed very often I think you best do the finetuning yourself.
    If you prefer this commercial assignment to be developed further by me, you can use the paypal button in my website.
    If I were your (not very poor) employer I'd prefer the latter.

    PS. The inaccuracy/inconsistency of the data in your CRM-system baffles me.
    I appreciate your help snb. It's almost there, your last file is doing what I want, minus accounting for the multiple 0000 price classes as I showed in the RealSample excel with 14 columns.

    The inaccuracies baffle me too, I don't know how they are entered but I am assuming manually, or copy/paste directly from the clinics application. So if they decided to say 115-C fake street instead of 115C fake street, they just entered it. This system is so backwards and not how it should be designed. Apparently we are getting a 'new' one, but people have been saying that for years apparently. I'm 8 months fresh here.

    My next task would of been to get rid of the inconsistencies based on postal code, then copy address 1 and paste over all the same postal code addresses to remove any differences. However I have just learned that we can't import a mass address change, so going 1 by 1 manually is just plain stupid. I could modify the addresses in excel then do this macro I was hoping to use.

    I don't know VBA so I wouldn't know those lessons, nor did I see any merged cells, thanks for letting me know. Summer is a slower time for my department which is why I am trying to figure out how to streamline some broken manual processes for when it picks back up in the fall.

    Thanks again.

  7. #47
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I went thru your RealSample and found 5 errors (typos) in the Address1 Column

    1. Doublespaces
    2. Dash errors (Assuming X-X is desired)
      1. X- X
      2. X -X
      3. X - X

    3. 360 MAIN ST vs 360 MAIN ST E


    In that small sample there were multiple instances of 25 distinct address1's with those errors.
    I did not analyze Column Address2 but I did notice a RM 204 vs STE 204 error.

    This is using the KISS Principle. It can be sped up using arrays for the OldString and the NewString

    Sub FixTypos()
    
    'With Application
      '.ScreenUpdating = False
      '.DisplayAlerts = False
    'End With
    
    With ActiveSheet
      'Reset Parameters and replace doublespaces
      .Cells.Replace What:="  ", Replacement:=" ", _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:= _
      False, ReplaceFormat:=False
      
      .Cells.Cells.Replace What:=" -", Replacement:="-"
      .Cells.Cells.Replace What:="- ", Replacement:="-"
      .Cells.Cells.Replace What:="-", Replacement:="-" 'Style choice
      .Cells.Cells.Replace What:="360 MAIN ST", Replacement:="360 MAIN ST E", _
       LookAt:=xlWhole 'To avoid 360 MAIN ST EE
      
    End With
    
    With Application
      .ScreenUpdating = True
      .DisplayAlerts = True
    'End With
    
    End Sub
    
    'Cells.Replace What:=OldString, _
    Replacement:=NewString, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:= _
    False, ReplaceFormat:=False
    If you very many specific errors (like Main St E and RM vs STE, You may want to list the errors and corrections on a hidden sheet and create the Arrays from those lists.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #48
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by SamT View Post
    I went thru your RealSample and found 5 errors (typos) in the Address1 Column

    1. Doublespaces
    2. Dash errors (Assuming X-X is desired)
      1. X- X
      2. X -X
      3. X - X

    3. 360 MAIN ST vs 360 MAIN ST E


    In that small sample there were multiple instances of 25 distinct address1's with those errors.
    I did not analyze Column Address2 but I did notice a RM 204 vs STE 204 error.

    This is using the KISS Principle. It can be sped up using arrays for the OldString and the NewString

    If you very many specific errors (like Main St E and RM vs STE, You may want to list the errors and corrections on a hidden sheet and create the Arrays from those lists.
    Thanks Sam, that will help me when the times comes!

    There are a lot of errors indeed, which we do know about but sadly everyone goes "oh well". Until it affects them of course. It's now affecting me so I will try and fix on my end. The export will always have those issues because we can't import the changes in 1 lump file, don't get me started on why! haha

    That is a secondary issue right now, my first would be to get the code working for my application, which I believe is almost there, but at a stand still... I have asked my boss if he wants to invest any $ and he didn't really give me an answer, I'd have to see how much it would cost to finish the code.

  9. #49
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Quote Originally Posted by snb View Post
    The inaccuracy/inconsistency of the data in your CRM-system baffles me.
    Data processing rule # 1: Clean up your data before trying to process it.

    I don't offer ineffectual or unnecessary solutions. If you complete the code for all typos and run it on all incoming data. you will have consistent data. Your sample has almost 500 rows. I ran that code on the entire sheet and it was done literally in the blink of an eye and eliminated 25 of at least 26 duplicate sets of data. If you create a dictionary array list on a sheet as I suggest, it will very easy to maintain as new typos appear. Which they will.
    Last edited by SamT; 06-17-2015 at 11:41 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #50
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    I assume 98% of the data will be fine, and i'll work on fixing the inconsistencies - however without working code to do the rest, that part is useless. Unfortunately like I said, HQ can't batch import my changes, which makes this task quite tedious.

  11. #51
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Quote Originally Posted by DarinM View Post

    ---- nor did I see any merged cells - - - -
    So what about DV1:EA1 ?

  12. #52
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by snb View Post
    So what about DV1:EA1 ?
    Gotcha - didn't know that mattered in vba like I said. thanks for pointing that out

    It isn't used for this situation but if its an issue we can unmerge, i won't be looking at those columns

  13. #53
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Unfortunately like I said, HQ can't batch import my changes, which makes this task quite tedious.
    I don't understand the issue. Aren't you working on data that you will be processing at your own location? IIRC, you said that you received the dirty data from elswhere and were then rearranging/editing it for processing locally. If you don't mind my asking, what is your office's product?

    I assume 98% of the data will be fine,
    More like less than 82%. You have ~149 unique dirty record sets, but only ~122 unique clean record sets, in just that small sample of 470 records.

    I have to use aproximations because I only looked at Address1.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #54
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    We sell hearing aids.

    We have a local CRM we update/pull from, however the people who can truly administer the background are in New Jersey.

    Yeah that looks rougher than estimated, but like I said, that is the easier issue than not having a working macro

  15. #55
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    the people who can truly administer the background are in New Jersey.
    But we're not talking about "administering the background," are we? I was under the impression that this was all for your computer at your desk.

    Yeah that looks rougher than estimated, but like I said, that is the easier issue than not having a working macro
    Maybe from your viewpoint.

    From my viewpoint as a programmer, it must be done before starting to write code to manipulate a data base..
    Last edited by SamT; 06-17-2015 at 01:15 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  16. #56
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Quote Originally Posted by DarinM View Post
    It isn't used for this situation but if its an issue we can unmerge, i won't be looking at those columns
    Thats isn't correct. How can VBA point to cells(1,134) if some cells in row 1 have been merged ?

  17. #57
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Data processing rule # 1: Clean up your data before trying to process it.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #58
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Quote Originally Posted by snb View Post
    Thats isn't correct. How can VBA point to cells(1,134) if some cells in row 1 have been merged ?
    Now I see why your point makes sense. Thank you.

  19. #59
    VBAX Regular
    Joined
    Apr 2015
    Posts
    72
    Location
    Good morning

    I got the code to partially work in my RealSample, however not starting at column 10 like intended, just at row 134 to 140.

    I also noticed that one list price 0000 was deleted, there was 2 but only 1 was kept. That is what I need to avoid, need to keep both of them.

    I will spend some time this morning cleaning up my RealSample data to make sure there are no errors.


    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


    turned into



    A 100704-001 company ABC 10324 152A ST 000122-000 090122-000

    which is weird because I don't see 090122-000 in the original data under that address?

    hmm..

  20. #60
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    Making progress,
    this finds matches of the price type and copies the account number to the appropriate column,
    I'm working on then copying each row "J to DE" up a row if the address matches, but it overwrites the contents, work in progress.
    I have made a table with price types as they appear on the sheet in Row 1 and another as they appear in Column EC on Sheets("Pricing")
    I have code that builds an array with the pricing values without the indexing numbers, not sure yet how to utilize both parts.
    My thought is to copy up the values one row at a time and if the cell above is full, offset 1 to right...
    not sure if im on the right track, but this at least gets the account number in the appropriate columns....
    (I had to convert the values in Row 1 and column EC to uppercase)
    Sub combineAccounts()
    Dim x, lr As Long
    Dim ws As Worksheet
    Dim aPtype As Variant
    
    Set ws = Worksheets("S1-var")
        
        With ws
            lr = .Cells(Rows.Count, "A").End(xlUp).Row
            
            For x = lr To 3 Step -1
                
                Set pFnd = .Range("I1:DE1").Find(Left(Cells(x, "EC"), Len(Cells(x, "EC")) - 3), , Excel.xlValues)
                pFnd.Offset(x - 1).Value = Cells(x, 2).Value
                
            Next x
            'For p = lr To 3 Step -1
            '    If .Cells(p, "E") = .Cells(p - 1, "E") Then
            '    Range("J" & p & ":DE" & p).Copy Range("J" & p - 1)
            '     Cells(p, "D").Value = "To Delete"
            '    End If
            '
            'Next p
            
        End With
    End Sub
    Copy of vbax52868c.xlsm
    Last edited by mperrah; 06-19-2015 at 12:33 PM.

Posting Permissions

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