# List All Possible Combinations Of Single Range In Two Separate Columns

Apr 25, 2013
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.

Example Desired Output:

Column A:

Column B:

Column C

0001

0001

0001

[Code] ......

View 7 Replies
ADVERTISEMENT
Nov 29, 2013

New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:

(NOTE: does not contain actual names or info) Book1.xlsx

This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?

While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.

View 6 Replies
View Related
May 9, 2014

Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:

Example

Column A contains : 1,2,3

Column B contains : 7,8,11,15

Column C contains : 12,16,18,19

Column D contains : 17,30,31

Column E contains : 30,31,32,33,34

Column F contains : 37,39,40

The rules are:

In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.

show me a formula to arrive the results.

View 3 Replies
View Related
May 8, 2014

I am trying to put something together on Excel.

1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.

2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.

3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9.

If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.

The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").

As a bonus, I'd like all cells not present in the first column to appear as a "0".

View 14 Replies
View Related
Aug 17, 2006

I have a long list of 2 columns containing data as follows:

country1 date1-1

country1 date1-2

country1 date1-3

country2 date2-1

country2 date2-2

country3 date3-1

country4 date4-1

country4 date4-2

country4 date4-3

which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g.

country1 country2 country3 country4

date1-1 date2-1 date3-1 date4-1 etc.

date2-1 date2-2

date3-1

I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part

View 4 Replies
View Related
May 2, 2014

I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?

[Code] .....

For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?

View 2 Replies
View Related
Jun 2, 2014

I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.

Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".

I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.

View 9 Replies
View Related
Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:

DCM1005-1

DCM1005-10

DCM1005A

MTR1005-1

MTR1005-10

MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)

A1, B1, C1, D1

DCM1005-1, DCM, 1005, 1

DCM1005-10, DCM, 1005, 10

DCM1005A, DCM, 1005, A

MTR1005-1, MTR, 1005, 1

MTR1005-10, MTR, 1005, 10

MTR1005A, MTR, 1005, A

View 5 Replies
View Related
Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO

406AMZN

451AMRC

404AMAD

605ANCC

405ADRC

The result would be:

302 ALTO

406 AMZN

451 AMRC

404 AMAD

605 ANCC

405 ADRC

View 6 Replies
View Related
Jan 19, 2009

A user would input 4 integers.

How can I generate a list of all possible combinations of the integers?

View 9 Replies
View Related
Feb 5, 2007

I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?

View 5 Replies
View Related
Feb 3, 2008

I know the COMBIN function, i want to list all combinations from a given set of cells (numbers), each item of the combination - in separate cell. Example:

1 2 3 4 5 a b 6 ....

after combin (x,3) i want to list me this: 123........but each number must be in separate cell (1-one cell, 2 -one cell, 3 -one cell)

124

125

12a

12b

126

234

etc

View 3 Replies
View Related
Sep 3, 2009

I need a vba that can generate all set of 6 number combinations with the total sum of 100.

For example: 3, 6, 4, 15, 35, 37 = 100.

View 9 Replies
View Related
Aug 9, 2008

I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:

A B

1 1

2 1

2 2

2 3

3 1

3 2

3 3

3 4

3 5

...

I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................

View 2 Replies
View Related
Feb 14, 2013

I have two columns of data in a worksheet, and I need a third column that shows all of the possible combinations of the data in those two columns. For example:

A

A

AA

B

B

AB

[Code] .....

I don't need permutations so, for example, the third column should only give the combination "AB" and exclude "BA".

View 4 Replies
View Related
Apr 23, 2013

I need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-

1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc

a b c a a b a a b b

If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.

I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.

View 4 Replies
View Related
Mar 29, 2013

I have two columns of data, one with an ID # and the other one with a date. Some ID# + date combinations are repeated and I need to delete the duplicates. Here is a visual:

ID# date

54 19720211

54 19720211

54 19810512

67 19590312

89 19931104

89 19931104

I need to keep only unique ID/date combinations. I would need the final list to look like this:

ID# Date

54 19720211

54 19810512

67 19590312

89 19931104

View 2 Replies
View Related
May 30, 2008

I have a spreadsheet with two columns:

The first one will always have a 'W' or a 'E'.

The second one will have a three digit code. Like this:

W 507

W 507

W 507

E 504

E 504

W 505

W 505

W 504

W 504

Is it possible to have output that show how many of each combination there is? Like this(for the example above):

W 504 2

W 505 2

W 507 3

E 504 2

View 14 Replies
View Related
May 9, 2008

Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:

A 1

A 1

A 2

A 3

B 2

B 1

B 3

B 3

B 4

Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?

View 3 Replies
View Related
Jan 29, 2013

I have been sent an export from Outlook in a .csv file. The Body of the Email is all contained in one cell for each row (each email detail) and is in the following vertical format within the cell:

Column Header

blank row

Data

blank row

Column Header

blank row

Data

blank row

....and so on. I've tried to do something using Transpose and Text to Columns, but I've not been able to successfully seperate out the data into a useful format in Excel. The data are responses to a website and contain Account/Contact information which I hope to be able to import into a CRM system as new Leads. way of extracting this information, so that I can get each row into a format that gives me the column header with the related data below, as you'd normally expect to see in Excel?

View 5 Replies
View Related
Oct 15, 2012

formula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.

ALMADEN STATION* 900 227 168 182 176

OAKRIDGE STATION* 900 276 332 362 349

OHLONE‐CHYNOWETH STATION* 900 469 480 522 504

ALUM ROCK STATION 901 947 925 1007 972

BAYPOINTE STATION 901 181 196 213 205

BONAVENTURA STATION 901 137 140 152 147

CAPITOL STATION 901 650 680 741 715

CONVENTION CENTER STATION 901 788 814 886 856

CURTNER STATION 901 569 571 621 600

ORCHARD STATION 901 87 94 91

View 6 Replies
View Related
May 10, 2014

I need to be able to create a list of all combinations (where position doesn't matter, therefore its not a permutation) of a list of numbers with repetition enabled. I need to provide anywhere up to at least 10-15 numbers if possible and they are integers normally between 1 and 72. Example: (for numbers 1, 2, 3)

VB:

1

2

3

[Code]....

View 5 Replies
View Related
Feb 26, 2011

How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.

View 9 Replies
View Related
Dec 6, 2011

I have 15 different words that I want to order in as many ways as possible, but only 3 at a time.

So let's say the words are:

cat

dog

elf

clown

monkey

rock

bananas

(etc)

then I want a list that has all of these possible combinations:

cat dog elf

cat dog clown

cat dog monkey

cat dog rock

cat dog bananas

cat elf clown

cat elf monkey

(etc)

until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.

I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.

View 9 Replies
View Related
Dec 20, 2012

An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.

I would like results for my request of 50 matches to be displayed as follows.

HHH

HHD

HHA

HDH

HDD

HDA

[Code] ...........

Where:

H=HOME

D=DRAW

A=AWAY

Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.

View 9 Replies
View Related
Dec 19, 2012

What I am trying to achieve using the example below:

ID Subject 1 Subject 2 Subject 3

1 Italian French German

2 Italian Art Physics

3 German French Italian

4 French Italian German

the result:

Italian French German 3

Italian Art Physics 1

As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.

I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.

View 6 Replies
View Related
Feb 25, 2014

Is it possible to have a single userform put data into two separate sheets within the same workbook.

I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)

A1, A2, A3, A4, A5, A6, A7, A8, A9

What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.

The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc

View 2 Replies
View Related
Feb 9, 2008

I have 7 cells containing strings but not numbers on a row.

Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.

For example, I got 7 cells like this.

A B C D E F G (each letter in ONE cell)

And I want to list out all the combinations like this:

ABC DEFG

ABD CEFG

ABE CDFG

ABF CDEG

etc.

For more details, please refer to the attached sample (an .xls file being zipped).

View 14 Replies
View Related
Jul 17, 2012

I breed snakes as a hobby and have around 30 females and 15 males Different morphs of Royal Python

There are a few things i am trying to do and will start of by asking the most simple one

If i have a column of females and a column of males can a formula be made to show all the possible pairings between male and female?

View 8 Replies
View Related
Oct 20, 2013

I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.

I'm struggling to do this in a Macro but I don't get it.

View 9 Replies
View Related