sort and matching cells

[jfeil]

have list of numbers(col 1) then another list of number (col 2) with
initials attached in col3

need a way to orgonize them so match up

so
BEFORE:

col1 col2 col 3
23 45 cd
45 12 cd
98 75 ag
56 84 cj
75
84
12

AFTER:

col1 col2 col3

12 12 cd
23
45 45 cd
56
75 75 ag
84 84 cj
98

I wrote it out in c++ code as

Array sales [400]
matrix reso[1000x2] //1000 rows 2 col
final[400x2]

int
counter;
search;
I;
Boolean
Found

Begin{

counter = 0;
for search=0; 0 to 400; search++;
{
i = 0;
found = false ;
while found = false
{
if sales[search] = = reso[0]
{
final[counter][0] = reso[0];
final[counter][1] = reso[1];
counter++;
found = true;
}
i++;
}
if counter%5=0
cout << “Found “ << counter << “ so far” <<;
}
}end;

 

[tony h]

various ways to do it:

1.
sort the first column. Use record macro to record the sort to give a
indication of the code
sort columns 2 and 3
then use a range object (dim rngColA as range) and set it to the firs
cell in column a
Use a second range object to point to the first cell in column B then
if rngColA=rngColB move the pointers down (use set rng=rng.offset(1))
if they don't match insert cells moving rows down(again a record macr
should show you how to do this)
when you get to the end all is ok.

2. sort the first column and use a vlookup to match the other valuse

3. open the spreadsheet using ADO or DAO and execute an SQL query o
it. This is my preferred method if there is lots of data to work with

regard

 

[jfeil]

could you post or send me a small worksheet showing this. I am
programmer jsut ecel is really weird to m

 

[tony h]

Option Explicit

Sub a()
'by Tony Henson

Dim wks As Worksheet
Dim rngA As Range
Dim rngB As Range

Set wks = ActiveSheet
Set rngA = wks.Columns("A:A")
rngA.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
Orientation:=xlTopToBottom
Set rngB = wks.Columns("B:C")
rngB.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
Orientation:=xlTopToBottom

Set rngA = wks.Range("A2")
Set rngB = wks.Range("B2")
Do Until rngA = ""
If rngB > rngA Then
rngB.Resize(, 2).Insert Shift:=xlDown
End If
Set rngA = rngA.Offset(1)
Set rngB = rngA.Offset(, 1)
Loop
MsgBox "done"
End Sub

regard

 

[keepITcool]

Try

Sub DataMatch()
Dim lRow As Long
Dim vDat As Variant
Dim vPos As Variant

With Range("A3").CurrentRegion
'Put the current values in an array
vDat = .Value

'Match up
For lRow = 2 To UBound(vDat)
vPos = Application.Match(.Cells(lRow, 1), .Columns(2), 0)
If Not IsError(vPos) Then
vDat(lRow, 2) = .Cells(vPos, 2)
vDat(lRow, 3) = .Cells(vPos, 3)
ElseIf Not IsEmpty(.Cells(lRow, 2)) Then
vDat(lRow, 2) = Empty
vDat(lRow, 3) = Empty
End If
Next

'Dump & Sort the result
With .Offset(, .Columns.Count + 1)
.Value = vDat
.Sort .Cells(1), xlAscending, Header:=xlYes
End With
End With

End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


jfeil wrote :