Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Problem Possible?
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column matching - sorting. Fairly hard problem, I think. | Excel Discussion (Misc queries) | |||
Matching problem | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel | |||
A matching problem | Excel Worksheet Functions | |||
matching problem using VBA | Excel Worksheet Functions |