Complex Excel search 1

[mchanguion]

Hi All

I am trying to search a 4 column range (approx 5000 rows)for all matches on a specific value and for each match, I need all the fields in the row (4 fields) to be put into an output range. This output range should never return more than 20 matches. I need to return every matching row that is found, even if the match is identicle to the input field.


I have tried different lookup functions in Excel but nothing seems to work and I suspect we might be in the domain of a VB macro?

Thanks in advance for any help.

Regards

Marc

 

[yogia]

Hi Marc:

How about posting ...

1) a few rows of your sample data
2) the valure to be searched for
3) and the resulting output

It will help in seeing what exactly you are working with and what you are trying to accomplish.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mchanguion]

Hi yogia

These are a few sample rows from a price list. I would do a search on say LC04 and am then looking for all matching rows in an output range. In this example, there would be 4 matching rows.

Is there a way this can be solved by a macro?


QI Q-BR02B LC02B Brand New Compatible Brother Black Ink Cartridge $3.80
QI Q-BR02C LC02C Brand New Compatible Brother Cyan Ink Cartridge $3.60
QI Q-BR02M LC02M Brand New Compatible Brother Magenta Ink Cartridge $3.60
QI Q-BR02Y LC02Y Brand New Compatible Brother Yellow Ink Cartridge $3.60
QI
QI Q-BR04B LC04B Brand New Compatible Brother Black Ink Cartridge $3.80
QI Q-BR04C LC04C Brand New Compatible Brother Cyan Ink Cartridge $3.60
QI Q-BR04M LC04M Brand New Compatible Brother Magenta Ink Cartridge $3.60
QI Q-BR04Y LC04Y Brand New Compatible Brother Yellow Ink Cartridge $3.60
QI
QI Q-BR21B LC21B Brand New Compatible Brother Black Ink Cartridge $3.80
QI Q-BR21C LC21C Brand New Compatible Brother Cyan Ink Cartridge $3.60
QI Q-BR21M LC21M Brand New Compatible Brother Magenta Ink Cartridge $3.60
QI Q-BR21Y LC21Y Brand New Compatible Brother Yellow Ink Cartridge $3.60

 

[yogia]

Hi Marc:

Thanks for posting your data. Yes it can be done with a Macto but use of a macro may not be necessary. Following is a solution using Advanced Filter ...

Now if you do want to automate process, you have a couple of choices ...

1) write a macro that executes the AdvancedFilter operation
2) use a formula based solution

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mchanguion]

Thank you so much for your post. I have macros written to automate the advanced filter operation and it works exactly as I needed it to.
Thank you once again
Marc

 

[yogia]

You Are Very Welcome Marc ... now let us keep EXCELing!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com