sorting one column against another

inakafc

New Member
Joined
Jan 21, 2005
Messages
5
Hi there

I was hoping that somebody here might be able to help me with this. I need to do two things and I've labeled them Q1 and Q2 on the image below. I have a master list and lots of other lists that are subsets of the master list.

Q1. I want to arrange the data into columns where each value sits in the row of the master list. Is this possible in Excel?

Q2. I want to delete any rows that don't have at least one value in a subset.

Thanks a million for any help you can provide. This has been driving me nuts. :oops:

C:\Documents and Settings\Declan\Local Settings\Temp\Excel\Book2.htm
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

inakafc

New Member
Joined
Jan 21, 2005
Messages
5
SORRY, image is here!

Looks like I could do with some help on posting to forums too.... :confused:
Book2
BCDEFGHIJK
1MasterTestMasterTestMasterTest
2acacc
3bkbkk
4ccc
5dd
6ee
7ff
8gg
9hh
10II
11jj
12kkk
13ll
Sheet1
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
what are you trying to do?
Do you want to pick everything from 'master' that has a value on the same row in 'test' ?
 

inakafc

New Member
Joined
Jan 21, 2005
Messages
5
Hi Harvey

I want to place my Test column next to the Masterlist column and then (somehow?) have the values in Test line up beside their corresponding values in Masterlist. In fact, I want to place a few test columns next to the Masterlist and have the process work for them all.

Finally, I want to delete any Masterlist rows that do not have a Test value matching in any of the Test columns. Note: I've only included one Test column in the image above.

Any advice would be greatly appreciated...
 

inakafc

New Member
Joined
Jan 21, 2005
Messages
5
I hope this image explains it more clearly...sorry for any confusion.
Book1.xls
BCDEFGHIJKLMNO
3MasterListTest-ATest-BTest-CMasterListTest-ATest-BTest-CMasterListTest-ATest-BTest-C
4abagaaaa
5bdfhbbbb
6chjjcdd
7dkddff
8eegg
9fffhhh
10gggjjj
11hhhhkk
12II
13jjjj
14kkk
15
16
17Initially,placeTestlistsnexttoQ1:IwantthevaluesineachTestQ2:Finally,Iwanttodeleterows
18theMasterListlisttolineupnexttotheirwithnovaluesinanyoftheTest
19correspondingMasterListvalueslists,inthiscaserowsc,eandi.
Sheet1
[/img]
 

inakafc

New Member
Joined
Jan 21, 2005
Messages
5
Just to let you know that Dangelor posted code to solve this problem at;

http://www.ozgrid.com/forum/showthread.php?p=145725#post145725


Here is that code:


Sub SortList()

Dim EntireList As Range
Dim MList, TestA, TestB, TestC
Dim x As Long, y As Long

'Adjust range to TopLeft cell
Set EntireList = Range("B3").CurrentRegion
EntireList.CreateNames _
Top:=True, Left:=False, Bottom:=False, Right:=False

MList = Range("masterlist")
TestA = Range("test_a")
TestB = Range("test_b")
TestC = Range("test_c")

Range("test_a").ClearContents
Range("test_b").ClearContents
Range("test_c").ClearContents

For x = 1 To UBound(TestA)
For y = 1 To UBound(MList)
If MList(y, 1) = TestA(x, 1) Then _
Range("test_a").Cells(y) = TestA(x, 1)
Next y
Next x

For x = 1 To UBound(TestB)
For y = 1 To UBound(MList)
If MList(y, 1) = TestB(x, 1) Then _
Range("test_b").Cells(y) = TestB(x, 1)
Next y
Next x

For x = 1 To UBound(TestC)
For y = 1 To UBound(MList)
If MList(y, 1) = TestC(x, 1) Then _
Range("test_c").Cells(y) = TestC(x, 1)
Next y
Next x

TestA = Range("test_a")
TestB = Range("test_b")
TestC = Range("test_c")

For x = 1 To UBound(MList)
If IsEmpty(TestA(x, 1)) And _
IsEmpty(TestB(x, 1)) And _
IsEmpty(TestC(x, 1)) Then _
Range("masterlist").Cells(x).ClearContents
Next x

EntireList.Sort Key1:=EntireList.Cells(1), Order1:=xlAscending, Header:=xlYes

End Sub
 

Forum statistics

Threads
1,148,216
Messages
5,745,440
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top