CODEWITHSUNDEEP
vbaloopsfor-loopexcel-2007
Kenny Bones
Kenny Bones

Reputation: 5139

Excel vba - comparing three ranges

I'm in need of some assistance in traversing three ranges (sheets) and check data back and forth. But these For loops are killing me.. Three loops is a bit too much for me and I'm not even sure how to logically spread out the code, what goes where etc.

Ok, so basically I collect these three ranges into their own Variants arrays:

I have the sheet here, for online viewing. Don't need to download it or anything. I have macro code there that collect the data if the summary sheet is empty. And this is working as it's supposed to do. https://skydrive.live.com/view.aspx?cid=5D018DB0458F03ED&resid=5D018DB0458F03ED%21164

The spreadsheet has some Norwegian words in it, but that shouldn't matter much :) It's basically Oppsummering(Summary), Brukere(Users) and Artikler(Articles).

Anyway, I want to make sure the summary sheet contains each user as many times as there are articles. Basically it's supposed to be mirrored. If I remove a line (name) from the Users sheet, those lines should be removed from the Summary sheet as well. But at the same time I want to keep the data from column D in the summary sheet as well. So I need to keep that data along with the User ID and Article ID to make sure the data in column D is in it's right place.

This is my (bad) code as it is now..

    With shtSummary
    ReDim tempArr(1 To UBound(arrUsers) * UBound(arrarticles), 1 To 6)
    If Not .Range("A2") = "" Then
        arrsummary = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Resize(, 6)

        ' Start collecting data, make sure summary sheet contains refreshed data while keeping the amount of each item for that particular user
        For u = 1 To UBound(arrsummary)
            For i = 1 To UBound(arrUsers)
                For p = 1 To UBound(arrarticles)
                    j = j + 1
                    ' Check ID
                    If arrsummary(u, 1) = arrUsers(i, 1) And arrsummary(u, 3) = arrarticles(p, 1) Then
                        ' ID
                        tempArr(j, 1) = arrUsers(u, 1)
                        ' Name
                        tempArr(j, 2) = arrUsers(u, 2)
                        ' Article Number
                        tempArr(j, 3) = arrarticles(i, 1)
                        ' Article Name
                        tempArr(j, 4) = arrarticles(i, 2)
                        ' Amount of that item
                        tempArr(j, 5) = arrsumary(u, 5)
                        ' Price of that article
                        tempArr(j, 6) = arrarticles(i, 3)
                    End If
                Next
            Next
        Next

    Else
        ' If summary sheet is empty, add all data from the two other sheets
        For u = 1 To UBound(arrUsers)
            For i = 1 To UBound(arrarticles)
                j = j + 1
                ' ID
                tempArr(j, 1) = arrUsers(u, 1)
                ' Name
                tempArr(j, 2) = arrUsers(u, 2)
                ' Article number
                tempArr(j, 3) = arrarticles(i, 1)
                ' Article name
                tempArr(j, 4) = arrarticles(i, 2)
                ' Article price
                tempArr(j, 6) = arrarticles(i, 3)
            Next
        Next
        ' Finally add data to spreadsheet Summary
        .Range("A2").Resize(j, 6).Value = tempArr
    End If
End With

Notice how there are three For loops and that's a bit too much for me I think.. I had a similar thread before here at Stackoverflow and somebody recommended I added another sheet to basically copy the data from the Summary sheet. That way I could just wipe the data from the Summary sheet at the end. But I don't see why one would need to do that, I mean, I keep all the data from the Summary sheet in shtSummary anyway, it's just in an Array. But that's the same data anyway?

Edit: So simplify how this thing works. Repeat each username for each item in Articles sheet. Meaning, if there are 10 articles, repeat the username 10 times. Articles are found in Articles sheet and Users are found in Users sheet. BUT, the data in column D in the Summary sheet is not collected from another sheet, it's entered directly into the Summary Sheet. And here's the problem, that data is lost if I just empty the Summary sheet and re-import the data from the Users and Articles sheet. Cause that's easy (done in macro above). It's just remembering the amount data for each item for each user that's the problem. If userID "2", articleNumber "452" has the value 2 in Column D, then I want to preserve that and make sure the value "2" in ColumnD is "glued" to the row of userID "2", articleNumber "452".

Upvotes: 1

Views: 630

Answers (1)

PersonalNexus
PersonalNexus

Reputation: 1302

One quick note: in your question it says the amount was in column D, but in your sample file, it was in column E, so I will be using column E, when referring to that column.

I agree with the comments, that it is probably easier to do in Access, but seeing how most users love to use Excel, here is the algorithm I would use to solve this:

  • Copy Columns A (User ID), C (Article ID) and E (Amount) to a temporary sheet to preserve the amounts so we can add them back later
  • Clear the contents of the summary sheet (this is easier than manually checking which users and/or articles need to be deleted)
  • Using two nested for-loops to iterate over the users and articles sheets' contents and enter all combinations of User IDs (with the associated name) and Article IDs (with the associated name and price) into columns A, B, C and F of the summary sheet
  • Iterate over the summary sheet and look up the original amount in the temporary sheet using user ID and article ID as search keys to fill in column E

I hope this helps, even though I didn't work out the exact VBA code. If there are specific pieces you need assistance with in the code, let me know.

Upvotes: 1

Related Questions