Database/List Manipulation

[DarinM]

Database Manipulation Sample for Forum.xlsm

Hi all,

I have a very very large list that I need edited, and I don't think IF statements are going to cut it!

I have attached an example spreadsheet to show you possibly better than I can explain it.

Column B has account #s, they are always different, sometimes the same but then a dash number would be different. Example below
012345-000
012345-001

Column C has company names, generally all the same. Example below
ABC Shop
ABC shop

Column D has address 1 - this is where I need help. This can be different or be the same. Example below
012345-000 ABC Shop 123 Fake Street
012345-001 ABC Shop 123 Fake Street

OR

012345-000 ABC Shop 123 Fake Street
012345-001 ABC Shop 456 Fake Street

Column E has Address 2 - generally used for PO boxes or suite/rooms
STE 204
STE 204

or

STE 204
STE 402

I would like Column F, G, H, become "extra account #'s", but they would only be used if the name, address 1 and address 2 were the exact same.

However, each column F,G,H would have to be organized based on Column J.

Column J has different price classes
0000 = normal pricing
0004 = discount pricing
5001 = government pricing

So if Row 3 were the same as Row 2, the spreadsheet would put Row 3's account # into column G because it gets discount pricing, based on the comparison of column J.

It would be extremely helpful and basically required, that if after matching between Row 2 and Row 3, Row 3 would be deleted.

Is this even possible, or should i do everything manually?

Appreciate your comments and suggestions

[SamT]

So each company can have up to three accounts depending on the pricing level.
A company is defined by the company name and the complete address
Is not the customer also defined by the first 6 digits of the account number?

Would there ever be two companies with the same first 6 digit account number

012345-000	ABC Shop	123 Fake Street
012345-001	ABC Shop	234 Next Street	STE H

Finally, think about this and consider that it might work better for you

Account #	Company Name	Address 1	Address 2	Norm Pricing	Discount Pricing	Government Pricing
012345	ABC Shop	123 Fake Street	STE 204	012345-000		012345-001
056789	DBC Shop	425 Fake St			56789-002
051256	DEF Shop	100 Street		51256-005

[DarinM]

hi Sam,

thanks for replying.

Yeah, up to 3 in this example but in reality it could be 15 classes, we are looking at increasing it to have a better database and filter-ability (is that a word?). However if I get help with 3 I can adapt the code to the other 15 or however many we will end up at.

The first 6 digits are the BILLTO, the next 3 are the SHIP to. However addresses can be the same or different and that is what makes it tricky. Sometimes the companies have a headoffice where it's billed, but shipped to their separate companies.

so yes your 000 and 001 example of same first 6 digit and different addresses can happen. that is a preferred situation, it's when they are the same is what is annoying and would like them on 1 line instead of 2.

As for your chart, it would work, I was just brainstorming with a coworker and they brought up another obstacle. But to continue with your chart idea, I always look at the main 6 digits, then look for the price class (level). So i would look for 012345 then shoot my eyes across to 0000 or 0004. so your method would work. I would look for the first 6 digits, then for the norm, discount or government column to see the dash 000 or 001 etc.

my coworkers obstacle is that, sometimes the account numbers can be 012345 and 234567 but they have the exact same company name and address 1/2. so I would also want that in the same row, but now what do we do if we have 2 or more account #'s with price class (level) of 0000?

Is it possible to have those account #s in 1 row, but ALT+Entered into a cell? so if you click on the cell, they are on separate lines, but in the same cell.

I hope that made sense. I have tried to adapt your table to visually show what I mean.

Account #	Company Name	Address 1	Address 2	Norm Pricing	Discount Pricing	Government Pricing
012345 234567	ABC Shop	123 Fake Street	STE 204	012345-000 234567-000	234567-002	012345-001 234567-001
056789	DBC Shop	425 Fake St			56789-002
051256	DEF Shop	100 Street		51256-005

I have attached a real sample from my database to show you.

[SamT]

Jumpin' Jehosephat! How can one company have that many account numbers?

Not my business. Anyway I recommend three database on 3 separate sheets, named accordingly.

Main DataBase, AKA Bill To
Account #	Company Name	Address 1	Address 2	fistname	LastNamee	Phone	Notes
12234	ABC Shop	123 Fake Street	STE 204
56769	ABC Shop	123 Fake Street	STE 204
Ship To DataBase
Account #	Company Name	Address 1	Address 2	fistname	LastNamee	Phone	Notes
12234-001	ABC Shop	123 Fake Street	STE 204
12234-002	ABC Shop	234 Nexte Street
56769-002	ABC Shop	123 Fake Street	STE 204
Price DataBase
Account #	Type
012345-000	Normal
012345-001	Government
234567-000	Normal
234567-001	Government
234567-002	Discount
51256-005	Normal
56789-002	Discount

For business advice from a bunch of really nice guys and girls, try this place: BreakTime 3

[mperrah]

datasetup.jpg
what about something like this
so every possible value can be accounted for in one row per customer?
2 (or more) values for Acct, 2 shipto, 2 billto, 2pricing per 3 types, etcs
that way you can populate as much as is needed

[DarinM]

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

[mperrah]

I was just suggesting for future proofing your layout by accounting for any future additions now.
It sounds like you have some companies with multiple account numbers and multiple extensions (pricing groups)
I thought having a place for each item would allow all info for each company to be on one line, I thought that was the goal.
I suggested each item could have 2, 3 or 4 entries available knowing some may be not used,
if you have a place for everything makes it easier to program (vlookup and others)

I would look at all the variables for the company with the most added parts and plot your data around that.
You may even consider having each part of the company ID be sectioned
first six unique company, 2nd two number shipping preference, 3rd two numbers bill to preference, last 2 pricing structure
Like SamT was pointing out. then you can have a library sheet with the named ranges for each category tp pull from.

If this whole project is to decide pricing, bill to, and ship to, having the ID numbers hold that info I think might streamline this process.
just a suggestion

[DarinM]

That is true, having some empty columns before the rest of the information would work fine. Account #1 - 6 maybe, then the rest as discussed.

The whole project is to make an easier managed mailing list and e-mail mailing list, based on account #s without having to remove duplicates etc. Right now it's a very big tedious effort 3-4 times a year and figured I would be able to get some help coding a quicker fix. (longer initial start up obviously, then faster in the long run).

We agree to your option mperrah, it does make the most sense for us.

Do you have a starting template that I can try and edit?

[snb]

I'd suggest:

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, 10)

      .Item(c00) = sp
    Next
       
    Sheet2.Cells(1).Resize(.Count, 8) = Application.Index(.items, 0, 0)
  End With
End Sub

[DarinM]

I tried that in the example excel I gave you, and it didn't do anything...added one more row to the top and mismatched.

[mperrah]

Start with something like this setting up variables for each part of the Account number by section (name, discount, bill to, ship to anything else you need to designate)
datasetup1.jpg
Then you can produce a unique Account ID that is meaningful
datasetup2.jpg

[snb]

I tried that in the example excel I gave you, and it didn't do anything...added one more row to the top and mismatched.

You can't be serious.
Look in sheet2.

Or in the attachment.

[mperrah]

Not to throw a monkey wrench in this whole thread, but I re-read your first attached example.
This code will look for matching account numbers (first 6 numbers) then adds that number to the row above in the pricing column,
then removes the duplicate and moves to next, going from bottom up...
Sorry I didnt pursue this earlier.

Sub combineShops()
Dim x, lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = lr To 2 Step -1
    If Left(Cells(x, 2), 6) = Left(Cells(x - 1, 2), 6) Then
        Select Case Cells(x, "J").Value
            Case "0000"
                Cells(x - 1, "F").Value = Cells(x, 2).Value
            Case "0004"
                Cells(x - 1, "G").Value = Cells(x, 2).Value
            Case "5001"
                Cells(x - 1, "H").Value = Cells(x, 2).Value
        End Select
    Cells(x, 2).EntireRow.Delete
    End If
Next x

End Sub

[DarinM]

Mperrah, that was suggested by my co-worker too, I suppose we could do a unique ID, except that will only be useful when referring to this document, everything else is based on account #.

Snb - wow, that was a fail on my part!

I like it, however in the government pricing column, it would have to put the -xxx number, not the 5001 number.

so 012345-001 would put -001 in the government pricing column instead of 5001. The -xxx number can be different, but it will always be classified as a certain account based on its price class column.

If you can make that change then i'll take a look at your old code vs new and see if I can spot the differences. My real export data has maybe 30 columns, so I will have to adapt your code into that and make sure it copies over the entire thing.

baby steps..

[DarinM]

Not to throw a monkey wrench in this whole thread, but I re-read your first attached example.
This code will look for matching account numbers (first 6 numbers) then adds that number to the row above in the pricing column,
then removes the duplicate and moves to next, going from bottom up...
Sorry I didnt pursue this earlier.

Just saw this, will try, give me a sec!

That works, but can you put the exported data into Sheet 2 like snb did?

I am thinking, it deleted the price code so I can't see if it worked (it looks like it did, but I would like to compare).

hope that is not difficult. I like that code though, it's doing what I want.

[mperrah]

This just copies the last 3 of shop number

Sub combineShopsLast3()
Dim x, lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = lr To 2 Step -1
    If Left(Cells(x, 2), 6) = Left(Cells(x - 1, 2), 6) Then
        Select Case Cells(x, "J").Value
            Case "0000"
                Cells(x - 1, "F").Value = Right(Cells(x, 2), 3)
            Case "0004"
                Cells(x - 1, "G").Value = Right(Cells(x, 2), 3)
            Case "5001"
                Cells(x - 1, "H").Value = Right(Cells(x, 2), 3)
        End Select
    Cells(x, 2).EntireRow.Delete
    End If
Next x

End Sub

[mperrah]

This leaves the rows that will be deleted and adds "Will Be Deleted" in column "K"
then adds the values in the pricing columns so you can see what changes

Sub combineShopsLast3()
Dim x, lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = lr To 2 Step -1
    If Left(Cells(x, 2), 6) = Left(Cells(x - 1, 2), 6) Then
        Select Case Cells(x, "J").Value
            Case "0000"
                Cells(x - 1, "F").Value = Right(Cells(x, 2), 3)
            Case "0004"
                Cells(x - 1, "G").Value = Right(Cells(x, 2), 3)
            Case "5001"
                Cells(x - 1, "H").Value = Right(Cells(x, 2), 3)
        End Select
'cells(x, 2).entirerow.Delete    
Cells(x, "K").Value = "Will be Deleted"
    End If
Next x

End Sub

[DarinM]

Gotcha. I like keeping the entire account #, and the will be deleted, I modified your first code with your second .

Ok so not sure if this is another wrench, but we could have different account #'s, same price class, same address... we would need a few columns like "normal pricing 1, 2, 3" etc. it doesn't happen often, but it could.

I suggested the ALT+Enter method, so you would have 5 account #'s in 1 cell, but that does not work for sorting (useful part of a database).

Ideas?


I seriously appreciate your help with this!

[mperrah]

could you attach another sample worksheet,
that helps me code for all scenarios
take last years raw data from the source and remove any proprietary or sensitive stuff.
leave 40 to 50 rows if possible so we can get a good batch of possible combinations.
Or you can make fake data but keep its formatting the same so we can see how it all fits together

[mperrah]

re post #18
maybe add an if statement in the loop so If account doesn't match but address does, combine those ?