Split 2 columns into smaller equal value columns.

[digimob]

Hi Guys,
I am new to this and hope someone can help with this:
I have 75 batteries, each has an ID no. In column A and a value in column B.

Ihave 2 columns A and B
A is an ID number, 1-75
B is a numerical value ranging from 1480 to 2300

I need to split this data into 15 pairs of columns, so 30 in total.
I want the value of the 15 value columns to be as equal as possible, with the ID column alongside.

Happy to send data file.


Thanks for reading

 

[JoeMo]

Hi Guys,
I am new to this and hope someone can help with this:
I have 75 batteries, each has an ID no. In column A and a value in column B.

Ihave 2 columns A and B
A is an ID number, 1-75
B is a numerical value ranging from 1480 to 2300

I need to split this data into 15 pairs of columns, so 30 in total.
I want the value of the 15 value columns to be as equal as possible, with the ID column alongside.

Happy to send data file.


Thanks for reading

"Equal as possible" is rather subjective. Here's some code to help get you started. The code assumes you have the header "ID" in A1 and "Value" in B1 and the IDs from 1-75 and associated values in A2:B76. It produces an output of 30 consecutive columns with headers "ID1" and "Val1" in D1:E1, ...... "ID15" and "Val15" in AF1:AG1 and 5 IDs and associated values in each pair of columns. You can decide if the value columns are as equal as possible.

Sub Digimob()
Dim d As Object, Ct As Long, T As Long, R As Range, ColNum As Long
Set d = CreateObject("Scripting.dictionary")
Set R = Range("A2:B76")
Application.ScreenUpdating = False
Range("D1").Value = "ID1"
Range("E1").Value = "Val1"
Range("D1:E1").AutoFill Range("D1:AG1"), xlFillDefault
For ColNum = 4 To 32 Step 2
    Ct = 0
    Do
        T = WorksheetFunction.RandBetween(1, 75)
        If Not d.exists(T) Then
            d.Add T, d.Count + 1
            Ct = Ct + 1
            With Columns(ColNum).Cells(Ct + 1, 1)
                .Value = T
                .Offset(0, 1).Value = Application.VLookup(T, R, 2, 0)
            End With
        End If
    Loop While Ct < 5 And d.Count < 75
Next ColNum
Application.ScreenUpdating = True
End Sub

 

[digimob]

"Equal as possible" is rather subjective. Here's some code to help get you started. The code assumes you have the header "ID" in A1 and "Value" in B1 and the IDs from 1-75 and associated values in A2:B76. It produces an output of 30 consecutive columns with headers "ID1" and "Val1" in D1:E1, ...... "ID15" and "Val15" in AF1:AG1 and 5 IDs and associated values in each pair of columns. You can decide if the value columns are as equal as possible.

Sub Digimob()
Dim d As Object, Ct As Long, T As Long, R As Range, ColNum As Long
Set d = CreateObject("Scripting.dictionary")
Set R = Range("A2:B76")
Application.ScreenUpdating = False
Range("D1").Value = "ID1"
Range("E1").Value = "Val1"
Range("D1:E1").AutoFill Range("D1:AG1"), xlFillDefault
For ColNum = 4 To 32 Step 2
    Ct = 0
    Do
        T = WorksheetFunction.RandBetween(1, 75)
        If Not d.exists(T) Then
            d.Add T, d.Count + 1
            Ct = Ct + 1
            With Columns(ColNum).Cells(Ct + 1, 1)
                .Value = T
                .Offset(0, 1).Value = Application.VLookup(T, R, 2, 0)
            End With
        End If
    Loop While Ct < 5 And d.Count < 75
Next ColNum
Application.ScreenUpdating = True
End Sub

Hi Joe,
I finally got around to complete this task, I would like to thank you very much as the code worked very well. As you said"Equal as possible is rather subjective" however once I ran the macro and totaled the columns I was able to manually swap some of the batteries with each other and ended up with a max of around 50ma margin between columns. With an average column value of 9500ma, 50 milliamp difference is well within the margin of error I needed. I have since built and charged the battery pack and all worked perfectly. Thank you again for your input!

Michael

 

[JoeMo]

Hi Joe,
I finally got around to complete this task, I would like to thank you very much as the code worked very well. As you said"Equal as possible is rather subjective" however once I ran the macro and totaled the columns I was able to manually swap some of the batteries with each other and ended up with a max of around 50ma margin between columns. With an average column value of 9500ma, 50 milliamp difference is well within the margin of error I needed. I have since built and charged the battery pack and all worked perfectly. Thank you again for your input!

Michael

You are welcome - thanks for the reply.