Vlookup and return multiple values horizontally

[galmond1010]

I have PARTS listed in column A and CUSTOMER NAMES in column C. I need to look up column A and return all customers associated with a PART and display it horizontally.
Any help would be greatly appreciated.

 

[kweaver]

I put the part to find in D1, then ran this macro to show the customers starting in E1:

Sub PartCust()
Dim cp As Integer, cn As Integer, LR As Long, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
cp = WorksheetFunction.CountIf(Range("A:A"), Range("C1"))
cn = 5
For i = 2 To LR
  If Range("A" & i) = Range("D1") Then
  Cells(1, cn) = Range("C" & i)
  cn = cn + 1
  Else
  End If
Next i
End Sub

Rerunning this would require clearing E1:Z1, for example.

Sub PartCust()
Dim cp As Integer, cn As Integer, LR As Long, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("E1:Z1").ClearContents
cp = WorksheetFunction.CountIf(Range("A:A"), Range("C1"))
cn = 5
For i = 2 To LR
  If Range("A" & i) = Range("D1") Then
  Cells(1, cn) = Range("C" & i)
  cn = cn + 1
  Else
  End If
Next i
End Sub

 

[Fluff]

How about

Book1
	A	C	D	E	F	G	H	I
1	County	Ward
2	Cambridgeshire	Downham Villages			Dorset	Poole Town	Parkstone	Town Centre
3	Norfolk	Upwell and Delph
4	East Riding of Yorkshire	Kings Park
5	East Riding of Yorkshire	Sutton
6	Cambridgeshire	Roman Bank
7	Kent	Walland & Denge Marsh
8	Dorset	Poole Town
9	Greater London	Thamesmead Moorings
10	Greater London	Abbey Wood
11	Greater London	Thamesmead East
12	Greater London	Evelyn
13	Devon	Teignmouth East
14	Dorset	Parkstone
15	Dorset	Town Centre
16	East Sussex	Devonshire
Sheet2

Cell Formulas
Range	Formula
G2	=IFERROR(INDEX($C$2:$C$37,AGGREGATE(15,6,(ROW($A$2:$A$37)-ROW($A$2)+1)/($A$2:$A$37=$F2),COLUMNS($A$1:A$1))),"")

Formula in G2 copied to the right

 

[DanteAmor]

Try this array formula.
Put the clients in column D, copy the formula to the right and down.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#b2a1c7; ">Parts</td><td style="background-color:#b2a1c7; ">customer</td><td > </td><td style="background-color:#b2a1c7; ">customer</td><td style="background-color:#b2a1c7; ">Part</td><td style="background-color:#b2a1c7; ">Part</td><td style="background-color:#b2a1c7; ">Part</td><td style="background-color:#b2a1c7; ">Part</td><td style="background-color:#b2a1c7; ">Part</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a</td><td style="background-color:#ffff00; ">cust1</td><td > </td><td style="background-color:#ffff00; ">cust1</td><td >a</td><td >e</td><td >i</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >b</td><td style="background-color:#92d050; ">cust2</td><td > </td><td style="background-color:#92d050; ">cust2</td><td >b</td><td >f</td><td >j</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >c</td><td >cust3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >d</td><td >cust4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >e</td><td style="background-color:#ffff00; ">cust1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >f</td><td style="background-color:#92d050; ">cust2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >g</td><td >cust3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >h</td><td >cust4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >i</td><td style="background-color:#ffff00; ">cust1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >j</td><td style="background-color:#92d050; ">cust2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >k</td><td >cust3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >{=IFERROR(INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$D2,ROW($B$2:$B$12)-1),COLUMNS($E$1:E1))),"")}</td></tr></table></td></tr></table>

 

[galmond1010]

Thank you all for your help. I like the options. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback

 

[galmond1010]

Fluff-
I re-created what you have above and I am getting a 0 in cell F2. When copying the formula over to the right and down, I get a 0 as a return. Thoughts? The example you have above is exactly what I am trying to accomplish. Thanks again.

 

[DanteAmor]

Thank you all for your help. I like the options. Have a great day.

I'm glad to help you. Thanks for the feedback.

 

[Fluff]

You need to manually input the value in F2 for the formula to work off.
If you want a formula to create as list of unique values use
=IFERROR(INDEX($A$2:$A$37,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$37),0),0)),"")
In F2 downwards with the other formula in G2