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[i][0]
{
final[counter][0] = reso[i][0];
final[counter][1] = reso[i][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 an indication of the code
sort columns 2 and 3
then use a range object (dim rngColA as range) and set it to the first 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 macro 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 on it. This is my preferred method if there is lots of data to work with

regards

[jfeil]

could you post or send me a small worksheet showing this. I am a programmer just excel is really weird to me

[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

regards

[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 :

>
> 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[i][0]
> {
> final[counter][0] = reso[i][0];
> final[counter][1] = reso[i][1];
> counter++;
> found = true;
> }
> i++;
> }
> if counter%5=0
> cout << “Found “ << counter << “ so far” <<;
> }
> }end;