search help

beng986

Board Regular
Joined
Jan 10, 2012
Messages
88
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
AB
1NameColor
2SallyRed
3PeterBlue
4DarrenRed
5BenYellow
6OlliePink
7alanBlue
8billRed
9biffOrange
10nedGreen
11paulWhite
12norrisPink
13porterBlue
14deanWhite
Sheet1 (5)


Code:
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
ABCDEFGHIJ
1NameColorRedBlueYellowPinkOrangeGreenWhite
2SallyRedSallyPeterBenOlliebiffnedpaul
3PeterBlueDarrenalannorrisdean
4DarrenRedbillporter
5BenYellow
6OlliePink
7alanBlue
8billRed
9biffOrange
10nedGreen
11paulWhite
12norrisPink
13porterBlue
14deanWhite
Sheet1 (5)
 
Upvote 0
Using formula.


Excel 2010
ABCDEFGHIJ
1NameColorRedBlueYellowPinkOrangeGreenWhite
2SallyRedSallyPeterBenOlliebiffnedpaul
3PeterBlueDarrenalannorrisdean
4DarrenRedbillporter
5BenYellow
6OlliePink
7alanBlue
8billRed
9biffOrange
10nedGreen
11paulWhite
12norrisPink
13porterBlue
14deanWhite
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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