1

I have a list of products in A2:A21 (A1:M1 is the titlebar). Behind these products are the names of 12 owners (B2:M21) who can repair these products. BUT... not all owners can repair every product. That's why there are empty cells. I want to create a automatic second sheet that has the same product list, but now only with ONE owner behind it, selected from the available owners. I would also want the selection to be devided equaly. So that all owners have the same amount of products (or as close as possible). Thank you!

2
  • I tried, what I describe above, with this formula: <<< =INDEX('Sheet1'!O2:Z2,RANDBETWEEN(1,12)) >>> And pull it over all cells, but I can't seem to exclude the empty cells. And I am not sure if the divide is equal.
    – FrankieS
    Feb 11, 2015 at 13:37
  • The divide will not be equal if you use a random function. There is no worksheet function solution for this, except maybe by using circular references but that's a bit risky.
    – freekvd
    Feb 12, 2015 at 15:08

2 Answers 2

1

What you're describing is a simple Linear Programming (or Integer Programming) problem. Excel has an add-in for that called Solver, that is disabled by default. You can enable it under Excel Options > Add-Ins > Manage Excel Add-Ins (Go). This adds the solver to your ribbon, under the Data tab.

Your objective for solving would be to maximize the total number of different repairmen. Your constraints are whether or not someone can repair a certain product, and that each product needs at least one repairman.

For more on how the solver works, you could Google, but this tutorial looks good to me.

1

Say we start with:

enter image description here

The following macro first copies the first column to the second sheet. The macro then begins with the first product (cell phone) and scans across the columns beginning with column B looking for an "X." When the "X" is found the owner is transferred to the second sheet.

The macro then moves down to the second product (charger) and repeats the scan across columns. But it now begins with column C rather than column B ...........this is to help distribute the owners evenly. Here is the macro:

Sub Owner()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, j As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A:A").Copy s2.Range("A1")
    j = 2
    For i = 2 To 21
loopit:
        If s1.Cells(i, j) = "X" Then
            s2.Cells(i, 2).Value = s1.Cells(1, j).Value
            j = j + 1
            If j = 14 Then j = 2
            GoTo exloopit
        Else
            j = j + 1
            If j = 14 Then j = 2
        End If
        GoTo loopit
exloopit:
    Next i
End Sub

and here is an example of the output sheet:

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .