Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and matching cells
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 BEFO 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; -- jfeil ------------------------------------------------------------------------ jfeil's Profile: http://www.excelforum.com/member.php...o&userid=31095 View this thread: http://www.excelforum.com/showthread...hreadid=507662 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and matching cells
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 -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107 View this thread: http://www.excelforum.com/showthread.php?threadid=50766 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and matching cells
could you post or send me a small worksheet showing this. I am programmer jsut ecel is really weird to m -- jfei ----------------------------------------------------------------------- jfeil's Profile: http://www.excelforum.com/member.php...fo&userid=3109 View this thread: http://www.excelforum.com/showthread.php?threadid=50766 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and matching cells
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 -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107 View this thread: http://www.excelforum.com/showthread.php?threadid=50766 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and matching cells
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 : [i] 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 BEFO 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[1]; counter++; found = true; } i++; } if counter%5=0 cout << “Found “ << counter << “ so far” <<; } }end; |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-Sort Won't Sort All Column Cells | Excel Discussion (Misc queries) | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
How to sort data according two matching cells? | Excel Worksheet Functions | |||
A 2-Column Matching Sort | Excel Discussion (Misc queries) |