Vlookup on duplicate entries

[herpasymplex10]

I've seen some others ask this question, but I think mine is a little simpler.

Column A has a part number
Next to it, in Column B is a component of that part number.

No problem.

However! Sometimes a part number has more than one component.
In this terrible spreadsheet I have to pull the data from
the part number will be listed as many times as needed in Column A with the component
in Column B

A vlookup with find the first time the part number appears in Column A and report what is next to it in Column B

No problem.

I want a formula that will continue on in Column A and return ALL components for that duplicated part number.

 

[Aladin Akyurek]

I've seen some others ask this question, but I think mine is a little simpler.

Column A has a part number
Next to it, in Column B is a component of that part number.

No problem.

However! Sometimes a part number has more than one component.
In this terrible spreadsheet I have to pull the data from
the part number will be listed as many times as needed in Column A with the component
in Column B

A vlookup with find the first time the part number appears in Column A and report what is next to it in Column B

No problem.

I want a formula that will continue on in Column A and return ALL components for that duplicated part number.

Have a look at my contrib in:

http://www.mrexcel.com/forum/showthread.php?p=1133932

 

[Matty]

Hi,

I would set this up like this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:80px;"><col style="width:73px;"><col style="width:12px;"><col style="width:80px;"><col style="width:84px;"><col style="width:84px;"><col style="width:84px;"><col style="width:84px;"></colgroup><tbody><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></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Part Number</td><td>Component</td><td>
</td><td>Part Number</td><td>Component 1</td><td>Component 2</td><td>Component 3</td><td>Component 4</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">123456</td><td>A1</td><td>
</td><td style="text-align:right; ">123456</td><td>A1</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">123457</td><td>B1</td><td>
</td><td style="text-align:right; ">123457</td><td>B1</td><td>B2</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">123458</td><td>C1</td><td>
</td><td style="text-align:right; ">123458</td><td>C1</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">123457</td><td>B2</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Array formula in E2 is:

=IFERROR(INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D2,ROW($B$2:$B$5)-ROW($B$2)+1),COLUMNS($E2:E2))),"")

Which can then be copied down and across.

Matty

 

[T. Valko]

I've seen some others ask this question, but I think mine is a little simpler.

Column A has a part number
Next to it, in Column B is a component of that part number.

No problem.

However! Sometimes a part number has more than one component.
In this terrible spreadsheet I have to pull the data from
the part number will be listed as many times as needed in Column A with the component
in Column B

A vlookup with find the first time the part number appears in Column A and report what is next to it in Column B

No problem.

I want a formula that will continue on in Column A and return ALL components for that duplicated part number.

There is an example here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3