Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Exclamation Unanswered: Consolidate duplicate rows with one varying column

    Hi,

    I have multiple tables that contains the same 3 column headings , Site, Part, and Customer. I can't do this manually as the list is in the thousands and exceeds Excel capacity...see below for what I would like to happen

    Table 1
    Site Part Customer
    Site 1 Part 1 Customer 1
    Site 1 Part 2 Customer 1
    Site 2 Part 3 Customer 1
    Site 3 Part 4 Customer 1
    Site 3 Part 5 Customer 1

    Table 2
    Site Part Customer
    Site 1 Part 1 Customer 2
    Site 1 Part 2 Customer 2
    Site 2 Part 6 Customer 2
    Site 3 Part 4 Customer 2
    Site 3 Part 5 Customer 2


    I want it to become

    Site Part Customer
    Site 1 Part 1 Customer 1, Customer 2
    Site 1 Part 2 Customer 1, Customer 2
    Site 2 Part 3 Customer 1
    Site 2 Part 6 Customer 2
    Site 3 Part 4 Customer 1, Customer 2
    Site 4 Part 5 Customer 1, Customer 2

    What's the SQL code that would do that?


    Thank you!!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I don't think you can do it with SQL. However, you can do it with VBA, using a simple concatenating method to generate the varied length field. You might also consider consolidating all the tables before you start, so you don't have to go crazy making sure you didn't miss any tables.

    BTW, if you can, get rid of the word "Site" in the site field, "Part" in the part field, and "Customer" in the customer field. If it's in that field, it has to be a 'site,' 'part,' or 'customer.' You can then make the Site and Part fields numeric. You have to keep Customer as string because of the new commas you're adding.

    So your ultimate table will look like this:

    Code:
    Site    Part     Customer
    1        1        1, 2
    1        2        1, 2
    2        3        1
    2        6        2
    3        4        1, 2
    4        5        1, 2
    Sam

  3. #3
    Join Date
    Jun 2012
    Posts
    4
    that's the thing...I have the code in VBA but I'm facing a stack overflow error when I try to run it as I have 70K+ of rows... here's the VBA code

    Code:
    Option Explicit
    
    Sub ertert()
    Dim x(), i&, j&, k&, s$
    
    With Range("A1").CurrentRegion
        x = .Value: .ClearContents
    End With
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(x)
            s = x(i, 1) & "|" & x(i, 2)
            If .Exists(s) Then
                k = .Item(s): x(k, 3) = x(k, 3) & ", " & x(i, 3)
            Else
                j = j + 1: .Item(s) = j
                x(j, 1) = x(i, 1)
                x(j, 2) = x(i, 2)
                x(j, 3) = x(i, 3)
            End If
        Next i
    End With
    
    Range("A1:C1").Resize(j).Value = x()
    End Sub
    Also, I did take away the words in the first two columns, thanks!

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Excuse me for asking, but if you're doing this in Excel, why don't you post it in the Excel forum?

    My reply was for Access, not for Excel.

    Sam

  5. #5
    Join Date
    Jun 2012
    Posts
    4
    I am trying to do it in SQL because I'm facing troubles with Excel.
    Since you said it might not work in SQL but might in VBA that's why I posted the code...

    I have it in Access as well

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    My advice: use the Access and lose the Excel, especially if you have over 64K records. Import all your Excel tables, and, as I said in my original posting, put it all into one Access table. Re-do the VBA, and try it again.

    BTW, I don't see in your original VBA anywhere that you DIMmed the x() array with a number of elements. That's probably why you had a stack error, not because of the >64K of records.

    The proper way to DIM an array is
    Code:
    Dim x(put the number(s) here, separated by commas) [optional as String (or whatever)]
    Out of curiosity, why didn't you remove the word "Customer" from that field?

    By the way, having said all the above, this is not the correct way to do this project; your data will not be normalized. The proper way to handle this follows:

    Create a new table with 3 fields: ID (long, autonumber), site and port.
    Also, create a new table with 2 fields: ForeignID (long, not autonumber) and Customer (string)

    After importing all the data into Access and consolidating into one table, make a Totals append query to the new site table with just the first two fields (site and port), grouping on both fields. The ID will generate automatically.

    Now make an append query (not totals) to create the records in the customer table. The query should be based on an inner join between the two original tables. Wherever you see "Customer 1" in the original field, put the ID (from the new site field) in the ForeignID field, and 1 in the customer field, in the new customer table.

    Now change the append query for "Customer 2" but with a change. Now you must add the new customer table into the joins, with a left join between the site field ID# and the ForeignID. Make a criteria where the new customer table's Customer field is Null. (You may have trouble with this query, I'm not too sure this query is updateable. Tell us about problems; they can be rectified.)

    Now make an update query to add the ", 2" to site/port combos that have multiple customers. That will complete the project, and your data will be properly normalized.

    Have fun and good luck,

    Sam
    Last edited by Sam Landy; 06-20-12 at 19:57. Reason: Address normalization issue

  7. #7
    Join Date
    Jun 2012
    Posts
    4
    your advice worked! I put all the data in one Access table, exported it to Excel then ran the VBA again.

    The contents of the Customer column are Customer Names so I didn't want to lose the string.

    thank you Sam!

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're very welcome.

    However, I edited my reply to address a different issue. You may want to study it. Please re-visit my previous reply. You'll see it's much longer now.

    Sam

Tags for this Thread

Posting Permissions

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