Split 2 columns into smaller equal value columns.

digimob

New Member
Joined
Aug 30, 2016
Messages
11
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
Code:
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
 
Upvote 0
"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.
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,847
Messages
6,181,321
Members
453,032
Latest member
Pauh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top