List all possible combinations of (up to 36 columns)

Razr

New Member
Joined
Jan 13, 2009
Messages
35
Hello,

I have a quite large dataset 72501 rows and 36 columns.

An example is as follows:

10268|32999226|15351|10272|7662755|||||||||||||||||||||||||||||||
32999226|||||||||||||||||||||||||||||||||||
15351|||||||||||||||||||||||||||||||||||
10272|||||||||||||||||||||||||||||||||||
7662755|||||||||||||||||||||||||||||||||||
31480|||||||||||||||||||||||||||||||||||
16915|41954587||||||||||||||||||||||||||||||||||
41954587|||||||||||||||||||||||||||||||||||
12776005|12045|42106241|186441|12679178|79583|12676630|42106251|12636808|17353|59360488|59363938|78459|12679394|12714173|12681521|12028|1941|143127|12019|17491|12018|59363230|12020|79783|33155051|16213|59360488|68369970|59360381|59359167|68370458|59360316|68370350|68372693|59363226

(where | = column)

What I would like is to get all possible combinations of 2 pairs (SHOULD ON THE SAME ROW) with a (#) in between them

For example
10268#32999226
32999226#10268
10268#15351
10268#10272
12776005#42106251

etc
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
VBA or formula ?

36 columns implies all 36 columns could be filled in (there's nothing to say they couldn't).
That's 36 factorial

36 factorial is

3.7199333e+41

You'd need that many rows just to generate all combinations for just one given row if they were all filled in.

According to Excel stats maximum rows in an Excel spreadsheet is 1048576 rows.

Ok, not all 36 columns have been filled in from your example data.
But you'd still be generating (in theory) an amount of rows which may not be reperesented on a spreadsheet.
 
Last edited:

Forum statistics

Threads
1,148,291
Messages
5,745,892
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top