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?

	B	C	D	E	F	G	H	I	J	K
2	Product Name	Serial #	Serial #	Serial #	Serial #		Product Name	Serial #		List Of Serial #'s
3	Name1	93	80	75	68		Name1	93		93
4	Name3	77	3	56			Name1	80		80
5	Name4	25	100				Name1	75		75
6	Name2	87					Name1	68		68
7							Name2	87		77
8							Name3	77		3
9							Name3	3		56
10							Name3	56		25
11							Name4	25		100
12							Name4	100		87
13							Name5	51
14							Name5	58
15							Name5	72
16							Name5	96

<tbody>
</tbody>

</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

=IFERROR(INDEX($H$2:$H$15,MATCH(0,COUNTIF($A$1:A1,$H$2:$H$15),0))," ") Formula 2 is =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 =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))," ") Name Serial# Serial# Serial# Serial# List of Serials Name Serial # a 1 2 3 4 1 a 1 b 5 2 a 2 c 6 7 8 3 a 3 d 9 10 4 a 4 e 11 12 13 14 5 b 5 6 c 6 7 c 7 8 c 8 9 d 9 10 d 10 11 e 11 12 e 12 13 e 13 14 e 14 <colgroup><col span="5"><col><col span="3"></colgroup><tbody> </tbody> <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>