Allpairs Testing in Excel

[msbrouse]

Hello - how can I create a testing matrix (all-pairs testing) using non-numeric values in Excel. I have 4 columns of data and would like to all-pairs output. I was trying to use the Data Analyzer tools, but it keep kicking me out because the data is text. Hope this makes sense.

 

[shg]

Welcome to the board.

Perhaps a few more words to leave less to our imagination.

 

[Ruddles]

Yes, welcome to the board.

Maybe you could post a sample of your data and show us how you want the output to look?

 

[msbrouse]

Thanks for responding! I need to test a web site and want to make sure I can test everything correctly. Here are the possible options and I want to get all the combinations possible to test. So example would be "NON, CIO, AC12FEDR" as one test case.

<TABLE style="WIDTH: 148pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=197><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 47pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=63>Reg Type</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=56>Options</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=78>Comp Code</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>NON</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">ABS</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12FEDR</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>HMB</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">ABT</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12FGCC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>COL</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">CIO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12GOVT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>GVT</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">CIOF</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12LFTF</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>STU</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">EX1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12LIFE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">EX2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12MHS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">EX3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12NAVY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">EX4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12ORXA</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">HIT</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">AC12PROS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">LFF</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

 

[Ruddles]

Assuming those data are in columns A-C of the first worksheet and column D is available to use, this should do the job:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub AllCombinations()[/FONT]
 
[FONT=Fixedsys] Dim ws As Worksheet[/FONT]
[FONT=Fixedsys] Dim aPtr As Long[/FONT]
[FONT=Fixedsys] Dim bPtr As Long[/FONT]
[FONT=Fixedsys] Dim cPtr As Long[/FONT]
[FONT=Fixedsys] Dim iOut As Long[/FONT]
 
[FONT=Fixedsys] Set ws = ThisWorkbook.Sheets(1)[/FONT]
[FONT=Fixedsys] ws.Columns("D").ClearContents[/FONT]
[FONT=Fixedsys] iOut = 1[/FONT]
 
[FONT=Fixedsys] For aPtr = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Fixedsys]   For bPtr = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row[/FONT]
[FONT=Fixedsys]     For cPtr = 2 To ws.Cells(ws.Rows.Count, "C").End(xlUp).Row[/FONT]
[FONT=Fixedsys]       iOut = iOut + 1[/FONT]
[FONT=Fixedsys]       ws.Cells(iOut, "D") = ws.Cells(aPtr, "A") & ", " _[/FONT]
[FONT=Fixedsys]                           & ws.Cells(bPtr, "B") & ", " _[/FONT]
[FONT=Fixedsys]                           & ws.Cells(cPtr, "C")[/FONT]
[FONT=Fixedsys]     Next cPtr[/FONT]
[FONT=Fixedsys]   Next bPtr[/FONT]
[FONT=Fixedsys] Next aPtr[/FONT]
 
[FONT=Fixedsys] MsgBox Format(iOut - 1, "#,###") & " combinations generated" & Space(10), _[/FONT]
[FONT=Fixedsys]        vbOKOnly + vbInformation[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

 

[shg]

There's a workbook that will do that with formulas at http://www.box.net/shared/ubd28tissi