PDA

View Full Version : Solved: Balance 2 columns



QuietRiot
08-10-2007, 08:56 AM
I have 2 columns that contain numbers. Say A and C

I need to look down A and C and if any numbers are in C but not A then put them at the bottom of A. If any numbers are in A but not in C then I need to put those numbers at the bottom of C.

any help greatly appreciated

QuietRiot
08-10-2007, 08:58 AM
and there could be more than one instance of the number

example
if A has
1
1
1

and C has no 1's then I need
1
1
1
at the bottom of C

p45cal
08-10-2007, 10:48 AM
Try
Sub blah()
For myCol = 1 To 3 Step 2 'col A to C step 2, so just A and C
myRow = 1
Do
CurrVal = Cells(myRow, myCol).Value
NoOfCurrValsInA = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(1, 1).End(xlDown)), CurrVal)
NoOfCurrValsInC = Application.WorksheetFunction.CountIf(Range(Cells(1, 3), Cells(1, 3).End(xlDown)), CurrVal)
MyDiff = NoOfCurrValsInA - NoOfCurrValsInC
If MyDiff > 0 Then Cells(1, 3).End(xlDown).Offset(1, 0).Resize(MyDiff) = CurrVal
If MyDiff < 0 Then Cells(1, 1).End(xlDown).Offset(1, 0).Resize(-MyDiff) = CurrVal
myRow = myRow + 1
Loop Until Cells(myRow, myCol) = "" 'stop on first empty cell
Next myCol
End Sub No blanks in either list, starts at row 1. Sheet to be processed must be the active sheet. Works OK here xl2003, xp home.
p45cal

QuietRiot
08-10-2007, 11:09 AM
Thank YOU,

works !!

QuietRiot
08-10-2007, 11:12 AM
actually just noticed something
For myCol = 1 To 3 Step 2 'col A to C step 2, so just A and C


what if i want to change it to A and H or any other 2 columns.

Bob Phillips
08-10-2007, 11:22 AM
Change the 3 to that column number, H is 8, etc.

QuietRiot
08-10-2007, 11:28 AM
Change the 3 to that column number, H is 8, etc.


I need to change the step too. so its just the 2 columns.. I get it :)

p45cal
08-10-2007, 11:29 AM
re:"For myCol = 1 To 3 Step 2 'col A to C step 2, so just A and C"

Column A is 1, Column B is 2 etc.
Column H is 8, so for columns A and H
For myCol = 1 To 8 Step 7
If you wanted to compare columns D and G then
For myCol = 4 To 7 Step 3

Also change the numbers in...tell you what, wait a mo., and I'll do it..

It was only a rough and ready way to tell the code which columns to process, if it's something you do regularly then post again and I'll suggest an easier/clearer way.
p45cal

QuietRiot
08-10-2007, 11:42 AM
yah, i tried 5 To 13 Step 7.. then step 8.. trying to get E and M and it just doesnt do anything

this would be something I do regularly EDIT.. just say the cells(1,3) etc. haha need to change that in order for it to work.

p45cal
08-10-2007, 11:50 AM
Sub blah()
FirstCol = "C"
SecondCol = "H"
StartCol = Columns(FirstCol).Column
EndCol = Columns(SecondCol).Column
For myCol = StartCol To EndCol Step EndCol - StartCol
myRow = 1
Do
CurrVal = Cells(myRow, myCol).Value
NoOfCurrValsInA = Application.WorksheetFunction.CountIf(Range(Cells(1, StartCol), Cells(1, StartCol).End(xlDown)), CurrVal)
NoOfCurrValsInC = Application.WorksheetFunction.CountIf(Range(Cells(1, EndCol), Cells(1, EndCol).End(xlDown)), CurrVal)
MyDiff = NoOfCurrValsInA - NoOfCurrValsInC
If MyDiff > 0 Then Cells(1, EndCol).End(xlDown).Offset(1, 0).Resize(MyDiff) = CurrVal
If MyDiff < 0 Then Cells(1, StartCol).End(xlDown).Offset(1, 0).Resize(-MyDiff) = CurrVal
myRow = myRow + 1
Loop Until Cells(myRow, myCol) = "" 'stop on first empty cell
Next myCol
End Sub

Edit the first two lines
FirstCol = "C"
SecondCol = "H"
to suit.

Again, if it is really often you have to do it, then we could base it on the current selection.
p45cal

QuietRiot
08-10-2007, 12:06 PM
thank you sir,

works awesome!