sorting one column against another

[inakafc]

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.

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

 

[inakafc]

SORRY, image is here!

Looks like I could do with some help on posting to forums too....

Book2
	B	C	D	E	F	G	H	I	J	K
1	Master	Test			Master	Test			Master	Test
2	a	c			a				c	c
3	b	k			b				k	k
4	c				c	c
5	d				d
6	e				e
7	f				f
8	g				g
9	h				h
10	I				I
11	j				j
12	k				k	k
13	l				l
Sheet1

 

[Harvey]

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]

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]

I hope this image explains it more clearly...sorry for any confusion.

Book1.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	MasterList	Test-A	Test-B	Test-C		MasterList	Test-A	Test-B	Test-C		MasterList	Test-A	Test-B	Test-C
4	a	b	a	g		a		a			a		a
5	b	d	f	h		b	b				b	b
6	c	h	j	j		c					d	d
7	d		k			d	d				f		f
8	e					e					g			g
9	f					f		f			h	h		h
10	g					g			g		j		j	j
11	h					h	h		h		k		k
12	I					I
13	j					j		j	j
14	k					k		k
15
16
17	Initially,placeTestlistsnextto					Q1:IwantthevaluesineachTest					Q2:Finally,Iwanttodeleterows
18	theMasterList					listtolineupnexttotheir					withnovaluesinanyoftheTest
19						correspondingMasterListvalues					lists,inthiscaserowsc,eandi.
Sheet1

[/img]

 

[inakafc]

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 = 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 = 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 = 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