Retrieve/Display all matched data in a column

[Dying]

Hi all,

I would like to retrieve or display all match data in a column through either vba or formulas without using long formulas that causes the sheet to lag.

For example:

raw data with Input ("Dog")
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]5[/TD]
[TD]Input:[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]3[/TD]
[TD]Result(s):[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Output data
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]5[/TD]
[TD]Input:[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]3[/TD]
[TD]Result(s):[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As of my previous project, i had to use a formula that looks something like this:
'Retrieve data of the matched value in other column in another page (very messy I know)
={IF(ISERROR(INDEX(Data5!$B:$B,SMALL(IF($A$1=Data5!$A:$A,ROW(Data5!$A:$A)-ROW(Data5!$A$1)+1),COLUMN(B1)))),"",INDEX(Data5!$B:$B,SMALL(IF($A$1=Data5!$A:$A,ROW(Data5!$A:$A)-ROW(Data5!$A$1)+1),COLUMN(B1))))}

Honestly it is very messy and slow when it retrieves multiple data but I couldn't find any other way to make the code more consistent. Does anyone have any way with using formulas or even vba commands? I'm fine with any as long as the code does not go as long as above. If you have an idea for the the one shown in the example, do share! Any comments is well appreciated.

Thanks!

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Dog	5	Input:	Dog
2	Cat	3	Result(s):	5
3	Dog	12	3	12
4	Duck	5		7
5	Dog	7
Sheet1

In C3 enter:

=COUNTIFS(A1:A5,D1)

In D2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$2:D2)>$C$3,"",INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5=$D$1,ROW($B$1:$B$5)-ROW($B$1)+1),ROWS($D$2:D2))))

 

[Dying]

Woah thanks Aladin! This helps in reducing the need for an IF(ISERROR( to remove the unnecessary data (which I think is causing the lag).

Since I'm dealing with over 10k of data and a matched item may have 100+ values, I would need to fill down to 200 prior to selecting the data. Thanks for the help! Will try this with the large data I'm dealing with.

 

[Aladin Akyurek]

You are welcome.

 

[Dying]

Alright, tested with the large data I have and the lag reduced tremendously. Your help is greatly appreciated!!

 

[Aladin Akyurek]

Alright, tested with the large data I have and the lag reduced tremendously. Your help is greatly appreciated!!

Great to hear that. Thanks.