Building a Product Selection Tool With Excel - Advice Please

[sibley14]

Hi,

Perhaps this is a straightforward one for you experts here....

I'm trying to find the best way of building a product selection tool for around 3-500 Adhesive Tape products. These are quite technical and have many criteria associated with them. For example:

Adhesive Thickness Side 1 & 2
Temperature Performance (Min & Max)
UV Resistance (rating from low to high)
Weather Resistance (rating from low to high)
High Surface Energy Performance (rating from low to high)
Low Surface Energy Performance (rating from low to high)
Colour
Type of Product
Carrier Type
Product Description
Total Caliper
Product Code
Plus more

The issue i have is that rather than just searching hard data, which only gives me half of the story, it would be much more beneficial to search the product description or even better 'word tags'. For example, we might know that some products will bond very well to glass or board, so we could tag these words to the product so that they are searcheable. Otherwise we'll end up with 100's of columns of searchable data.

We could tag a product as 'Excellent on Glass', 'High Temp Resistance', 'Peels Clean' etc.

My idea at this stage is that the products would have to be in some sort of table or DB, then on another tab we would have the search tool. This would allow you to fill in 1 or more of the most important criteria to narrow down your products, as you do this you either hit search or it does it live for you, much like an online search works.

i appreciate that this might be a little complex, so i'm hoping that you might be able to give me some pointers.

It would be used by 3-4 people, so ideally looking for something stable and secure that can't be overwritter. Perhaps access is the right tool for this job.

Really appreciate you help and advice (in advance).

Cheers

 

[Taul]

Hi
You could use Auto-Filter to filter the data you want to display.

Assuming you have 300 to 500 rows of data, one row per product, with the header columns for each of the features you want to list.

Keep the feature names short so your column widths are not too wide, nothing worse than scrolling left to right.

The limits on the filtered list are 10,000
details taken from here (near the bottom of the list)

Applies To: Excel 2016Excel 2013Excel 2010Excel 2007
https://support.office.com/en-us/ar...=en-US&rs=en-US&ad=US&ocmsassetID=HP010073849

One problem, the Auto Filter does not like empty cells, so if you are missing data, just use a zero or dash "-", so the auto filter can include all the rows of data in a particular column.
You can select (Filter) more than one column at a time, just remember to turn each of the filters off when you have finished, so the next user doesn't get confused.

Try it with a sample set of data first.

cheers
Paul



Edit
If you need a tutorial for Auto Filters
http://www.contextures.com/xlautofilter01.html

 

[FDibbins]

Lets see if this will get you started.

Assuming your data looks something like this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Blah 1[/td][td]Blah 2[/td][td]Blah 3[/td][td]Blah 4[/td][/tr]

[tr][td]

2​

[/td][td]this is one[/td][td]aa[/td][td]

10​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td]that was two[/td][td]bb[/td][td]

20​

[/td][td]

200​

[/td][/tr]

[tr][td]

4​

[/td][td]there goes three[/td][td]cc[/td][td]

30​

[/td][td]

300​

[/td][/tr]

[tr][td]

5​

[/td][td]That was one[/td][td]aa[/td][td]

40​

[/td][td]

400​

[/td][/tr]

[tr][td]

6​

[/td][td]There goes two[/td][td]bb[/td][td]

50​

[/td][td]

500​

[/td][/tr]

[tr][td]

7​

[/td][td]This is three[/td][td]cc[/td][td]

60​

[/td][td]

600​

[/td][/tr]

[tr][td]

8​

[/td][td]there goes one[/td][td]aa[/td][td]

70​

[/td][td]

700​

[/td][/tr]

[tr][td]

9​

[/td][td]this is two[/td][td]bb[/td][td]

80​

[/td][td]

800​

[/td][/tr]

[tr][td]

10​

[/td][td]that was three[/td][td]cc[/td][td]

90​

[/td][td]

900​

[/td][/tr]
[/table]

For extracting data based on a term/s in F2...
[Table="width:, class:grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Blah 1[/td][td]Blah 2[/td][td]Blah 3[/td][td]Blah 4[/td][/tr]

[tr][td]

2​

[/td][td]this[/td][td]this is one[/td][td]aa[/td][td]

10​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td][/td][td]This is three[/td][td]cc[/td][td]

60​

[/td][td]

600​

[/td][/tr]

[tr][td]

4​

[/td][td][/td][td]this is two[/td][td]bb[/td][td]

80​

[/td][td]

800​

[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
G2=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$10)),ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Then copy across as needed and then copy down

 

[sibley14]

Hi guys,

Thank you both for your feedback and advice. i'm a little slow coming back to this as i have been super busy.

Taul, thanks for your advice/pointers. i'm going to investigate that option, however i think i need something slightly more complex than the auto filters. It's a little fiddly when you have so much data.

FDibbins, thanks for your suggestion too....however, i'm a little confused as to where i place the formula. In cell g2?

I have updated your table with the type of data we would have in it;

[TABLE="width: 1201"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]#[/TD]
[TD]Product
[/TD]
[TD]Width (mm)
[/TD]
[TD]Length (Mts)[/TD]
[TD]Adh Colour
[/TD]
[TD]Liner Col.[/TD]
[TD]Thickness Side A (mm)[/TD]
[TD]Thickness Side B (mm)
[/TD]
[TD]Adh. Type Side A
[/TD]
[TD]Adhesi Type Side B
[/TD]
[TD]Max Temp Perf. ©
[/TD]
[TD]Bonds well To
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]FT666[/TD]
[TD="align: right"]1000[/TD]
[TD]50[/TD]
[TD]Clear Scrim[/TD]
[TD]Yellow[/TD]
[TD]0.25[/TD]
[TD]0.25
[/TD]
[TD]Rubber[/TD]
[TD]Rubber[/TD]
[TD]100[/TD]
[TD]Glass, Rubber, Foams[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]FT7206[/TD]
[TD="align: right"]1000[/TD]
[TD]50[/TD]
[TD]Clear Tissue
[/TD]
[TD]White[/TD]
[TD]0.5
[/TD]
[TD]0.5
[/TD]
[TD]Rubber[/TD]
[TD]Rubber[/TD]
[TD]150[/TD]
[TD]Cardboard, Packaging[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]FM7600[/TD]
[TD="align: right"]1000[/TD]
[TD]50[/TD]
[TD]White Foam
[/TD]
[TD]White[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Rubber[/TD]
[TD]Rubber[/TD]
[TD]80[/TD]
[TD]Cardboard, Packaging[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]AFB6610C[/TD]
[TD="align: right"]500[/TD]
[TD]33[/TD]
[TD]Clear Foam
[/TD]
[TD]Red[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[TD]Acrylic[/TD]
[TD]Acrylic[/TD]
[TD]200[/TD]
[TD]glass, Rubber, metal, low surface energy[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]AFB6640G[/TD]
[TD="align: right"]900[/TD]
[TD]33[/TD]
[TD]Grey Foam
[/TD]
[TD]Red[/TD]
[TD]0.4
[/TD]
[TD]0.4[/TD]
[TD]Acrylic[/TD]
[TD]Acrylic[/TD]
[TD]200[/TD]
[TD]glass, Rubber, metal, high surface energy[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]AFB6650C[/TD]
[TD="align: right"]500[/TD]
[TD]33[/TD]
[TD]Clear Foam
[/TD]
[TD]Red[/TD]
[TD]0.5
[/TD]
[TD]0.5[/TD]
[TD]Acrylic[/TD]
[TD]Acrylic[/TD]
[TD]200[/TD]
[TD]glass, Rubber, metal, low surface energy[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]AFB6625C[/TD]
[TD="align: right"]500[/TD]
[TD]33[/TD]
[TD]Clear Foam
[/TD]
[TD]Red[/TD]
[TD]0.25
[/TD]
[TD]0.25[/TD]
[TD]Acrylic[/TD]
[TD]Acrylic[/TD]
[TD]200[/TD]
[TD]glass, Rubber, metal, low surface energy[/TD]
[/TR]
</tbody>[/TABLE]

The data up until the 'Bonds well to column' is pretty straight forward to search either in a table or using a seperate worksheet search box. However, what i'm trying to achieve is a type of tag search. So for quick reference i can find a product that mght bond well to Glass and then rubber. Ideally i need to be able to specify the two surfaces that we need to look at and then conduct a quick search of the data to find products that match that criteria.

So a typical example would be: I need a 2mm foam tape that bond well to rubber and cardboard.

With that info i can enter the key points into my search box and it will search the product DB and give me some suggestions.

Perhaps access might be a better fit for this type of query.

Let me know what you think.

thanks and rgds

 

[FDibbins]

Based on your sample data, I have adjusted my approach and added a helper column. (your sample doesn't match your "answer" though)

The method below will work for 2 different materials
[Table="width:, class:grid"][tr][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][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][/tr]
[tr][td]

1​

[/td][td]#[/td][td]Product[/td][td]Width (mm)[/td][td]Length (Mts)[/td][td]Adh Colour[/td][td]Liner Col.[/td][td]Thickness Side A (mm)[/td][td]Thickness Side B (mm)[/td][td]Adh. Type Side A[/td][td]Adhesi Type Side B[/td][td]Max Temp Perf. ©[/td][td]Bonds well To[/td][td]metal[/td][td]Glass[/td][/tr]

[tr][td]

2​

[/td][td]1[/td][td]FT666[/td][td]

1000​

[/td][td]50[/td][td]Clear Scrim[/td][td]Yellow[/td][td]0.25[/td][td]0.25[/td][td]Rubber[/td][td]Rubber[/td][td]100[/td][td]Glass, Rubber, Foams[/td][td]0[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]2[/td][td]FT7206[/td][td]

1000​

[/td][td]50[/td][td]Clear Tissue[/td][td]White[/td][td]0.5[/td][td]0.5[/td][td]Rubber[/td][td]Rubber[/td][td]150[/td][td]Cardboard, Packaging[/td][td]0[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]3[/td][td]FM7600[/td][td]

1000​

[/td][td]50[/td][td]White Foam[/td][td]White[/td][td]1[/td][td]1[/td][td]Rubber[/td][td]Rubber[/td][td]80[/td][td]Cardboard, Packaging[/td][td]0[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]7[/td][td]AFB6610C[/td][td]

500​

[/td][td]33[/td][td]Clear Foam[/td][td]Red[/td][td]1[/td][td]1[/td][td]Acrylic[/td][td]Acrylic[/td][td]200[/td][td]glass, Rubber, metal, low surface energy[/td][td]1[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]8[/td][td]AFB6640G[/td][td]

900​

[/td][td]33[/td][td]Grey Foam[/td][td]Red[/td][td]0.4[/td][td]0.4[/td][td]Acrylic[/td][td]Acrylic[/td][td]200[/td][td]glass, Rubber, metal, high surface energy[/td][td]2[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]9[/td][td]AFB6650C[/td][td]

500​

[/td][td]33[/td][td]Clear Foam[/td][td]Red[/td][td]0.5[/td][td]0.5[/td][td]Acrylic[/td][td]Acrylic[/td][td]200[/td][td]glass, Rubber, metal, low surface energy[/td][td]3[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]10[/td][td]AFB6625C[/td][td]

500​

[/td][td]33[/td][td]Clear Foam[/td][td]Red[/td][td]0.25[/td][td]0.25[/td][td]Acrylic[/td][td]Acrylic[/td][td]200[/td][td]glass, Rubber, metal, low surface energy[/td][td]4[/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td]Product[/td][td]Width (mm)[/td][td]Length (Mts)[/td][td]Adh Colour[/td][td]Liner Col.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td]AFB6610C[/td][td]

500​

[/td][td]

33​

[/td][td]Clear Foam[/td][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td]AFB6640G[/td][td]

900​

[/td][td]

33​

[/td][td]Grey Foam[/td][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td]AFB6650C[/td][td]

500​

[/td][td]

33​

[/td][td]Clear Foam[/td][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td]AFB6625C[/td][td]

500​

[/td][td]

33​

[/td][td]Clear Foam[/td][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

The 2 materials would go into M1 and N1 - these can be moved, adjust the helper formula as needed
Column M2 is the helper column
M2=COUNTIFS($L$2:L2,"*"&$M$1&"*",$L$2:L2,"*"&$N$1&"*")
copied down as needed

For the extract...
B11=IFERROR(INDEX(B:B,MATCH(ROWS($A$1:A1),$M:$M,0)),"")
copied down and across as needed

Again, this can be moved to where you need the answer to be