Return Multiple Values From Each Lookup In Range

[Lewzerrrr]

Hi,

i'm I'm currently using an INDEX SMALL IF ROWS to return multiple values horizontally for each cell in A1:A100.. I then have to manually TRANSPOSE each row to make it into a verticle list.

Is there a way to do this all in one go?

I'm at a computer to give an example but it would be something like this:

A. B
Product1 Product1 Product2 Product3 Product4
Product5 Product5 Product6 Product7 Product8

Result
A
Product1
Product2
Product3
Product4
Product5
Product6


Thanks

 

[FDibbins]

You show col A and B, but it looks like you have more than just those 2?
Is each Product in it's own cell?
How many columns of data will you have?
Will all columns have data in?

 

[Lewzerrrr]

You show col A and B, but it looks like you have more than just those 2?
Is each Product in it's own cell?
How many columns of data will you have?
Will all columns have data in?

Hi,

Let me give you a better example.. this just a sample data. What I need to do is input the product name in to column B and for it to then find all the serial numbers and list them all in a single column K.

<columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1)))) confirmed="" with="" cse="" dragged="" down="" and="" across="" so="" this="" finds="" all="" serial="" #'s="" then="" i="" can="" do="" a="" manual="" transpose="" which="" take="" extremely="" long="" the="" array="" slow="" performance.="" is="" there="" perhaps="" vba="" method="" for="" this?
<columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))<columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))
=IF(COUNTIF($H$3:$H$16,$B3)<columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))
Confirmed with CSE. - Apologies the formula isn't pasting but it's just an INDEX SMALL IF ROW

This returns multiple serial #s for each product name I input in column B, I then perform a manual transpose array for each row which could take a while if the data needed is a lot PLUS the arrays could slow down performance..

Is there a VBA method for this?

[TABLE="width: 808"]
<tbody>[TR]
[TD][/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]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product Name[/TD]
[TD]Serial #[/TD]
[TD]Serial #[/TD]
[TD]Serial #[/TD]
[TD]Serial #[/TD]
[TD][/TD]
[TD]Product Name[/TD]
[TD]Serial #[/TD]
[TD][/TD]
[TD]List Of Serial #'s[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name1[/TD]
[TD]93[/TD]
[TD]80[/TD]
[TD]75[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD]93[/TD]
[TD][/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name3[/TD]
[TD]77[/TD]
[TD]3[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD]80[/TD]
[TD][/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name4[/TD]
[TD]25[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name2[/TD]
[TD]87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name2[/TD]
[TD]87[/TD]
[TD][/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name3[/TD]
[TD]77[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name3[/TD]
[TD]56[/TD]
[TD][/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name4[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name4[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name5[/TD]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name5[/TD]
[TD]58[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name5[/TD]
[TD]72[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name5[/TD]
[TD]96[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))
</columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))<columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))
</columns($c$3:c3),"",index($i$3:$i$16,small(if($h$3:$h$16=$b3,row($i$3:$i$16)-row($i$3)+1),column(a1))))>

 

[Mike Szczesny]

Can you something like this. I was able to duplicate your format with my example. My example can show a maximum of 4 serial #'s for a letter. The first formula produces all the letters. The second formula produces the all the serial #'s for a letter. The third formula produces the list of serial #'s. You need use Cntrl+Shift+Enter. Copy down (and across) when applicable. When you copy across formula 2, you will see blank spaces, but still have a formula. You need to delete these formula in the blank spaces. Otherwise, formula 3 will not work.
Formula 1 is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($H$2:$H$15,MATCH(0,COUNTIF($A$1:A1,$H$2:$H$15),0))," ")

Formula 2 is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($I$2:$I$15,SMALL(IF($H$2:$H$15=$A2,ROW($I$2:$I$15)-ROW($I$2)+1),COLUMNS($B$2:B2)))

Formula 3 is [TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"] =IFERROR(INDEX($B$2:$E$6,INT(SMALL(IF($B$2:$E$6<>"",(ROW($B$2:$E$6)-ROW($B$2)+1)*10^9+COLUMN($B$2:$E$6)-COLUMN($B$2)+1),ROWS($F$2:F2))/10^9),MOD(SMALL(IF($B$2:$E$6<>"",(ROW($B$2:$E$6)-ROW($B$2)+1)*10^9+COLUMN($B$2:$E$6)-COLUMN($B$2)+1),ROWS($F$2:F2)),10^9))," ")


[TABLE="width: 600"]
<colgroup><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]Serial#[/TD]
[TD]Serial#[/TD]
[TD]Serial#[/TD]
[TD]Serial#[/TD]
[TD]List of Serials[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Serial #[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]d[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]d[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]