Matching Problem Possible?
 

Set out below are three columns of data starting at A1, B1, C1 respectively.
Is it possible to match column B and C to A? The columns are identical except
that the order is different.

The data does vary in name and number of course.

HOLBECK GHYLL INDIAN TRAIL 94
SPANISH ACE PACIFIC PRIDE 80
HARRY UP MAGIC GLADE 55
COSEADROM TEXAS GOLD 83
PIC UP STICKS MISARO 96
RACCOON MR WOLF 94
MR WOLF SPANISH ACE 79
CONTINENT HARRY UP 56
CANADIAN DANEHILL TALBOT AVENUE 94
MAGIC GLADE CONTINENT 96
MISARO LITTLE EDWARD 80
LITTLE EDWARD PEOPLETON BROOK 56
SAFARI MISCHIEF DIANE´S CHOICE 94
GEORGE THE SECOND SAFARI MISCHIEF 96
PEOPLETON BROOK ORANMORE CASTLE 92
BOND BOY HOLBECK GHYLL 66
DIANE´S CHOICE RACCOON 61
TALBOT AVENUE BOND BOY 45
RAINBOW BAY COSEADROM 65
PACIFIC PRIDE CANADIAN DANEHILL 69
INDIAN TRAIL PIC UP STICKS 36
ORANMORE CASTLE RAINBOW BAY 78
TEXAS GOLD GEORGE THE SECOND 45

Matching Problem Possible?
 

??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saxman" wrote in message
...
Set out below are three columns of data starting at A1, B1, C1
respectively.
Is it possible to match column B and C to A? The columns are identical
except
that the order is different.

The data does vary in name and number of course.

HOLBECK GHYLL INDIAN TRAIL 94
SPANISH ACE PACIFIC PRIDE 80
HARRY UP MAGIC GLADE 55
COSEADROM TEXAS GOLD 83
PIC UP STICKS MISARO 96
RACCOON MR WOLF 94
MR WOLF SPANISH ACE 79
CONTINENT HARRY UP 56
CANADIAN DANEHILL TALBOT AVENUE 94
MAGIC GLADE CONTINENT 96
MISARO LITTLE EDWARD 80
LITTLE EDWARD PEOPLETON BROOK 56
SAFARI MISCHIEF DIANE´S CHOICE 94
GEORGE THE SECOND SAFARI MISCHIEF 96
PEOPLETON BROOK ORANMORE CASTLE 92
BOND BOY HOLBECK GHYLL 66
DIANE´S CHOICE RACCOON 61
TALBOT AVENUE BOND BOY 45
RAINBOW BAY COSEADROM 65
PACIFIC PRIDE CANADIAN DANEHILL 69
INDIAN TRAIL PIC UP STICKS 36
ORANMORE CASTLE RAINBOW BAY 78
TEXAS GOLD GEORGE THE SECOND 45

Matching Problem Possible?
 

I assume you want to keep the order in current column A
insert new helper column A
fill new column A with 1,2, etc.
select new columns A and B and sort by column B
select new columns C and D and sort by column D
Select new columns A:D ands sort by column A
Delete helper column A

you couls also use various forms of index(match()) to achieve what you want
which would be better if there is not a hundred percent match for column A
and B

"Saxman" wrote:

Set out below are three columns of data starting at A1, B1, C1 respectively.
Is it possible to match column B and C to A? The columns are identical except
that the order is different.

The data does vary in name and number of course.

HOLBECK GHYLL INDIAN TRAIL 94
SPANISH ACE PACIFIC PRIDE 80
HARRY UP MAGIC GLADE 55
COSEADROM TEXAS GOLD 83
PIC UP STICKS MISARO 96
RACCOON MR WOLF 94
MR WOLF SPANISH ACE 79
CONTINENT HARRY UP 56
CANADIAN DANEHILL TALBOT AVENUE 94
MAGIC GLADE CONTINENT 96
MISARO LITTLE EDWARD 80
LITTLE EDWARD PEOPLETON BROOK 56
SAFARI MISCHIEF DIANE´S CHOICE 94
GEORGE THE SECOND SAFARI MISCHIEF 96
PEOPLETON BROOK ORANMORE CASTLE 92
BOND BOY HOLBECK GHYLL 66
DIANE´S CHOICE RACCOON 61
TALBOT AVENUE BOND BOY 45
RAINBOW BAY COSEADROM 65
PACIFIC PRIDE CANADIAN DANEHILL 69
INDIAN TRAIL PIC UP STICKS 36
ORANMORE CASTLE RAINBOW BAY 78
TEXAS GOLD GEORGE THE SECOND 45

Matching Problem Possible?
 

On 02/08/2007 14:14:21, "Don Guillett" wrote:
??


Basically, I need to maintain column A. Column B needs sorting so it matches
column A (name for name), together with the data in column C.

Matching Problem Possible?
 

In helper columns just use VLOOKUP or a macro to reorder.
Or a macro assuming your data starts in col G to make helper columns. You
could then delete the old h & i cols.

Sub reorderdata()
For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saxman" wrote in message
...
Set out below are three columns of data starting at A1, B1, C1
respectively.
Is it possible to match column B and C to A? The columns are identical
except
that the order is different.

The data does vary in name and number of course.

HOLBECK GHYLL INDIAN TRAIL 94
SPANISH ACE PACIFIC PRIDE 80
HARRY UP MAGIC GLADE 55
COSEADROM TEXAS GOLD 83
PIC UP STICKS MISARO 96
RACCOON MR WOLF 94
MR WOLF SPANISH ACE 79
CONTINENT HARRY UP 56
CANADIAN DANEHILL TALBOT AVENUE 94
MAGIC GLADE CONTINENT 96
MISARO LITTLE EDWARD 80
LITTLE EDWARD PEOPLETON BROOK 56
SAFARI MISCHIEF DIANE´S CHOICE 94
GEORGE THE SECOND SAFARI MISCHIEF 96
PEOPLETON BROOK ORANMORE CASTLE 92
BOND BOY HOLBECK GHYLL 66
DIANE´S CHOICE RACCOON 61
TALBOT AVENUE BOND BOY 45
RAINBOW BAY COSEADROM 65
PACIFIC PRIDE CANADIAN DANEHILL 69
INDIAN TRAIL PIC UP STICKS 36
ORANMORE CASTLE RAINBOW BAY 78
TEXAS GOLD GEORGE THE SECOND 45

Matching Problem Possible?
 

On 02/08/2007 17:07:52, "Don Guillett" wrote:
In helper columns just use VLOOKUP or a macro to reorder.
Or a macro assuming your data starts in col G to make helper columns. You
could then delete the old h & i cols.

Sub reorderdata()
For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub

I think the problem is best sorted with a macro to sort column A, A-Z and
then column B, A-Z together with the information in column B.

Matching Problem Possible?
 

Please TOP post in this forum. You did not say so in the first post. If you
want to sort the 1st column then use this

Sub reorderdata()
lr = Cells(Rows.Count, "g").End(xlUp).Row
Range("g2:g" & lr).Sort key1:=Range("g2"), Order1:=xlAscending
For i = lr To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saxman" wrote in message
...
On 02/08/2007 17:07:52, "Don Guillett" wrote:
In helper columns just use VLOOKUP or a macro to reorder.
Or a macro assuming your data starts in col G to make helper columns. You
could then delete the old h & i cols.

Sub reorderdata()
For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub

I think the problem is best sorted with a macro to sort column A, A-Z and
then column B, A-Z together with the information in column B.

Matching Problem Possible?
 

"Saxman" wrote...
On 02/08/2007 14:14:21, "Don Guillett" wrote:
??

Basically, I need to maintain column A. Column B needs sorting so it
matches column A (name for name), together with the data in column
C.

Simplest way is to add a column of formulas in col D. Assuming the
table spans A1:C23, use the following formulas.

D1:
=MATCH(B1,A$1:A$23,0)

Fill D1 down into D2:D23. Select B1:D23 (yes, EXCLUDE col A), and run
Data Sort, select No header row and sort on col D in ascending
order. Once sorted, you can clear D1:D23.

And post in whatever style makes the most sense TO YOU.

Matching Problem Possible?
 

Thanks for the feedback and advice.

I thought I stated in my original post that I wanted to match columns B & C
to A?

It does not really matter in what order the columns are, as long as A & B
match and that the data in C is tied to B.

On 02/08/2007 20:23:25, "Don Guillett" wrote:
Please TOP post in this forum. You did not say so in the first post. If
you want to sort the 1st column then use this

Sub reorderdata()
lr = Cells(Rows.Count, "g").End(xlUp).Row
Range("g2:g" & lr).Sort key1:=Range("g2"), Order1:=xlAscending
For i = lr To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub

Matching Problem Possible?
 

I'm getting old so maybe I missed something. If your original data looked
like this
G H I
C D 4
A C 3
D B 2
B A 1

Then the last macro I sent would result in this
G H I J K
A D 4 A 1
B C 3 B 2
C B 2 C 3
D A 1 D 4

You could then delete col H and col I to get
G H K
A D 1
B C 2
C B 3
D A 4

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saxman" wrote in message
...
Thanks for the feedback and advice.

I thought I stated in my original post that I wanted to match columns B &
C
to A?

It does not really matter in what order the columns are, as long as A & B
match and that the data in C is tied to B.

On 02/08/2007 20:23:25, "Don Guillett" wrote:
Please TOP post in this forum. You did not say so in the first post. If
you want to sort the 1st column then use this

Sub reorderdata()
lr = Cells(Rows.Count, "g").End(xlUp).Row
Range("g2:g" & lr).Sort key1:=Range("g2"), Order1:=xlAscending
For i = lr To 2 Step -1
x = Columns(8).Find(Cells(i, "g")).Row
Cells(i, "j") = Cells(x, "h")
Cells(i, "k") = Cells(x, "i")
Next i
End Sub

Matching Problem Possible?
 

If my original data looked like this:-
G H I

C D 4
A C 3
D B 2
B A 1

I really want it to end up like this:-

CC3
AA1
DD4
BB2

Alternatively:-

AA1
BB2
CC3
DD4


On 02/08/2007 23:40:07, "Don Guillett" wrote:
I'm getting old so maybe I missed something. If your original data looked
like this
G H I
C D 4
A C 3
D B 2
B A 1

Then the last macro I sent would result in this
G H I J K
A D 4 A 1
B C 3 B 2
C B 2 C 3
D A 1 D 4

You could then delete col H and col I to get
G H K
A D 1
B C 2
C B 3
D A 4

Matching Problem Possible?
 

Then a simple sort on the 1st column and then sort the 2-3 columns. I wonder
why you need the 2nd col?
Sub reorderdataSIMPLESORT()
lr = Cells(Rows.Count, "g").End(xlUp).Row
Range("g2:g" & lr).Sort key1:=Range("g2"), Order1:=xlAscending
Range("H2:I" & lr).Sort key1:=Range("H2"), Order1:=xlAscending
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saxman" wrote in message
...
If my original data looked like this:-
G H I

C D 4
A C 3
D B 2
B A 1

I really want it to end up like this:-

CC3
AA1
DD4
BB2

Alternatively:-

AA1
BB2
CC3
DD4


On 02/08/2007 23:40:07, "Don Guillett" wrote:
I'm getting old so maybe I missed something. If your original data looked
like this
G H I
C D 4
A C 3
D B 2
B A 1

Then the last macro I sent would result in this
G H I J K
A D 4 A 1
B C 3 B 2
C B 2 C 3
D A 1 D 4

You could then delete col H and col I to get
G H K
A D 1
B C 2
C B 3
D A 4