search help

[beng986]

hey,

Could someone help me with a search function.

In one workbook i have

Sally Red
Peter Blue
Darren Red
Ben Red
Ollie Pink
alan Blue

is there a function that will put all those that liked a colour into a column?

i.e

Red Blue

Sally Peter
Darren Alan
Ben


thanks

 

[sheetspread]

pivot table

 

[sheetspread]

Actually I tried doing this in a pivot table and couldn't get the names to appear (just a sum or count of numbers). Perhaps it can be done, but here's a macro:

Excel 2003
	A	B
1	Name	Color
2	Sally	Red
3	Peter	Blue
4	Darren	Red
5	Ben	Yellow
6	Ollie	Pink
7	alan	Blue
8	bill	Red
9	biff	Orange
10	ned	Green
11	paul	White
12	norris	Pink
13	porter	Blue
14	dean	White
Sheet1 (5)

Sub groupnamesbyadjacentnamewithselectcopy()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim LR As Integer
Dim formulastring As String
Dim LC As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
y = 0
For x = 2 To LR
If Application.WorksheetFunction.CountIf(Range("B2:B" & x), Range("B" & x).Value) = 1 Then y = y + 1
If Application.WorksheetFunction.CountIf(Range("B2:B" & x), Range("B" & x).Value) = 1 Then Cells(1, 3 + y).Value = Range("B" & x).Value
Next
LC = 3 + y
For z = 4 To LC
For x = 2 To LR

If Cells(x, 2) = Cells(1, z) Then formulastring = formulastring & "," & Cells(x, 1).Address(False, False)
Next x
Range(Mid(formulastring, 2, 100)).Select
Selection.Copy
Cells(2, z).Select
ActiveSheet.Paste
formulastring = ""
Next z
End Sub

Excel 2003
	A	B	C	D	E	F	G	H	I	J
1	Name	Color		Red	Blue	Yellow	Pink	Orange	Green	White
2	Sally	Red		Sally	Peter	Ben	Ollie	biff	ned	paul
3	Peter	Blue		Darren	alan		norris			dean
4	Darren	Red		bill	porter
5	Ben	Yellow
6	Ollie	Pink
7	alan	Blue
8	bill	Red
9	biff	Orange
10	ned	Green
11	paul	White
12	norris	Pink
13	porter	Blue
14	dean	White
Sheet1 (5)

 

[beng986]

thanks for your efforts. I shall see if it works.

 

[Sandeep Warrier]

Using formula.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Name	Color		Red	Blue	Yellow	Pink	Orange	Green	White
2	Sally	Red		Sally	Peter	Ben	Ollie	biff	ned	paul
3	Peter	Blue		Darren	alan		norris			dean
4	Darren	Red		bill	porter
5	Ben	Yellow
6	Ollie	Pink
7	alan	Blue
8	bill	Red
9	biff	Orange
10	ned	Green
11	paul	White
12	norris	Pink
13	porter	Blue
14	dean	White
Sheet1

Cell Formulas
Range	Formula
D2	{=IF(ROWS(D$1:D1)<=COUNTIF($B$2:$B$14,D$1),INDEX($A$2:$A$14,SMALL(IF($B$2:$B$14=D$1,ROW($B$2:$B$14)-ROW($B$2)+1),ROWS(D$1:D1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.