sorting two columns

[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

 

[Chris]

If I'm understanding you correctly you want to sort the first column
then show any matching values in the second column next to the first.

Personally I would 'Group' this data up using a Pivottable and then
produce the final output from that. I would arrnage you data in two
column Name and Score.
In Name put BOTH of your columns of a,b,c,d,e etc. In Score put the
value 1 alongside each of your 12,400 records.

Build a PivotTable from this so you have the Names as the Row field and
the Score as the Data Items area. What you get out will be a Sum of
Score Report with each Name having a match scoring 2 and each one not
scoring only 1.

You can then convert this into the raw values - Copy and Paste Special
Values - and from there - if you need to - build and drag down a
formula something like "=IF(RC[-1]=1,"",RC[-2])" so you have "re-built"
the second column but only where there is a match.

 

[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

 

[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

 

[Chris]

jdimino

Have you tried - or do you understand - my post?

 

[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

 

[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