sorting two columns

J

jdimino

Hi

I would like to know if there is a way to sort two different culumn
side by side. my problem is that in column one there is 8700 record
and the second has 3700 records,
what command can I use to make the second column records line up sid
by side to the first column?
Regards JD

example: a b
b d
c e
d
 
P

Pete_UK

Do you mean that you want to sort the two columns independently of one
another?

If so, highlight the first column and click Data | Sort - Excel will
present a message asking if you want to expand the selection but you
should click No and carry on with the existing data. Then in the
dialogue box select ascending order and indicate if you have a header
row or not, then click OK.

Then highlight the second column of data and repeat, choosing not to
expand the data selection again.

Hope this helps.

Pete
 
J

jdimino

Hi

I'm sorry I knew I would make confusion in explaining, but what
really want is to match what's on column two with column one. m
problem is, like I've mentioned before that the first column has 870
records and the second has less then half the records. so in order t
make a match a=a I need to shift cells around to make a name matc
with the first column. I need to know if there is a formula or a way t
do this
 
J

jdimino

Hi Chris

I'm sorry, I did not understand your answer, my knowledge on exel i
very limited. I'm sure your suggestion would work but I dont have th
foggiest idea on how to go about it. thank you for your hel
 
D

Dave Peterson

I'd use a macro. But if you're not too familiar with excel, you'll have to do
some work.

Are you up for it?

If yes, I've saved this from a few previous posts:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub


This is where your homework will begin:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top