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

	A​	B​	C​	D​
1​	Blah 1	Blah 2	Blah 3	Blah 4
2​	this is one	aa	10​	100​
3​	that was two	bb	20​	200​
4​	there goes three	cc	30​	300​
5​	That was one	aa	40​	400​
6​	There goes two	bb	50​	500​
7​	This is three	cc	60​	600​
8​	there goes one	aa	70​	700​
9​	this is two	bb	80​	800​
10​	that was three	cc	90​	900​

For extracting data based on a term/s in F2...

	F​	G​	H​	I​	J​
1​		Blah 1	Blah 2	Blah 3	Blah 4
2​	this	this is one	aa	10​	100​
3​		This is three	cc	60​	600​
4​		this is two	bb	80​	800​
5​
6​

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;

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

#

Product

Width (mm)

Length (Mts)

Adh Colour

Liner Col.

Thickness Side A (mm)

Thickness Side B (mm)

Adh. Type Side A

Adhesi Type Side B

Max Temp Perf. ©

Bonds well To

metal

Glass

2​

1

FT666

1000​

50

Clear Scrim

Yellow

0.25

0.25

Rubber

Rubber

100

Glass, Rubber, Foams

0

3​

2

FT7206

1000​

50

Clear Tissue

White

0.5

0.5

Rubber

Rubber

150

Cardboard, Packaging

0

4​

3

FM7600

1000​

50

White Foam

White

1

1

Rubber

Rubber

80

Cardboard, Packaging

0

5​

7

AFB6610C

500​

33

Clear Foam

Red

1

1

Acrylic

Acrylic

200

glass, Rubber, metal, low surface energy

1

6​

8

AFB6640G

900​

33

Grey Foam

Red

0.4

0.4

Acrylic

Acrylic

200

glass, Rubber, metal, high surface energy

2

7​

9

AFB6650C

500​

33

Clear Foam

Red

0.5

0.5

Acrylic

Acrylic

200

glass, Rubber, metal, low surface energy

3

8​

10

AFB6625C

500​

33

Clear Foam

Red

0.25

0.25

Acrylic

Acrylic

200

glass, Rubber, metal, low surface energy

4

9​

​

10​

Product

Width (mm)

Length (Mts)

Adh Colour

Liner Col.

11​

AFB6610C

500​

33​

Clear Foam

Red

12​

AFB6640G

900​

33​

Grey Foam

Red

13​

AFB6650C

500​

33​

Clear Foam

Red

14​

AFB6625C

500​

33​

Clear Foam

Red

15​

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