Excel Comparisons (formula or other)

mattcc1989

New Member
I'm hoping someone would be kind enough to help me with an excel formula that i'm struggling to get my head around.

I have a spreadsheet of products e.g. column A = product name, B = Price, C = description, D = key features and so on.

Row 1 would be product 1, row 2 product 2 and so on.

I have around 300 products and want to create new rows which contain all possible combinations of products.

So that I end up with:

A = product 1 name, B = Price, C = description, D = key features, E = product 2 name, F = Price, G = description, H = key features.

Which is easy enough, but is there a way to automate every possible combination based on the product name?

So if I only had 4 product, the formula would create rows for each combination of products (but obviously keeping the name, price, descriptions etc.

product 1 | product 2
product 2 | product 3
product 1 | product 3
product 3 | product 4
product 1 | product 4
product 2 | product 4

Essentially what I'm trying to achieve is a comparison of all my different products, but each comparison needs to be on a single row.

Thanks

pgc01

MrExcel MVP
Hi
Welcome to the board

A simple solution is to build a list of the combinations. You can then use INDEX() to get all the properties of the products.

This is a simple formula solution to build the list of the combinations.

Write the number of products in B1 (in the example I post I used 8 products)
Write A3=1, B3=2

Use the formulas
In A4: =IF(B3<>\$B\$1,A3,IF(A3=\$B\$1-1,"",A3+1))
In B4: =IF(A4="","",IF(B3=\$B\$1,A4+1,B3+1))

Copy the formulas down

mattcc1989

New Member
Amazing. Thanks for the response and welcome.

I'm struggling with your comment on "You can then use INDEX() to get all the properties of the products."

Is there any way you can clarify how I would pull out the properties in one row for product 1 and 3 for example (in your example row 4).

So I'd need to pull out the price, description, name, etc etc for product 1 and product 3. (in reality there are much more columns than price, description and name but I guess whatever answer you help me with would translate.

pgc01

MrExcel MVP
Hi

This is an example.

In D3: =INDEX(Products!\$A:\$C,1+\$A3,COLUMNS(\$D:D))

Copy down and across in columns D:F

in H3: =INDEX(Products!\$A:\$C,1+\$B3,COLUMNS(\$H:H))

Copy down and across in columns H:J

Example:

1,081,860
Messages
5,361,737
Members
400,653
Latest member

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...