Matching Problem Possible?

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

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
 
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
 
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
 
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
Click to expand...

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.
 
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
 
Saxman said:
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.
Click to expand...

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.
 
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.
 
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
 
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
 
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
 
Back
Top