I have the following example values in Column A and I want to concatenate each unique combination to a list.
A
B
C
D
Example necessary output:
A vs. B
A vs. C
A vs. D
B vs. C
B vs. D
C vs. D
Notice there are no duplicates (e.g., A vs. B / B vs. A). I have found many macros that will create all the duplicates but none that will create only unique.
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
I have a list for example:
apple
pear
grape
orange
i'm after some code that will create every possible combination of the list.
The examples i've found in the archives give all possible combinations of the same list in a different order
e.g apple, pear, orange, grape.......apple, orange, pear, grape.........apple, grape, orange, pear etc etc etc... THIS IS NOT WHAT I'M AFTER.
i'm after every possible combination starting right at the beginning with single words
e.g
apple
apple, pear
apple, pear, orange
apple, pear, orange, grape
pear, orange,
pear, orange, grape
orange,
orange, apple
etc etc you get the idea..... but i do not want repititions like
apple, pear.........pear, apple.
apple, orange, pear ...........pear, orange, apple.
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.
I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001
Joe A 1002
Joe A 1003
Jill B 1001
Jill B 1002
Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range
[Code]....
I need a formula that will make every make every cell in column A unique. This formula needs to concatenate columns H, M, and N and display the result in column A. As you can see, you can get two instances of the same value (e.g., Dog:Brown:Small for A2, A9 and A10). For this reason, we then need to concatenate a counter number on the end that increases by 1 based on the total number of matching values ABOVE the cell in column A.
For example, I need a formula in cell A12 that concatenates H12:M12:N12 and then counts the number of "Dog:Brown:Small" instances above and then appends the largest incremented value +1. This would result in "Dog:Brown:Small:0004" in cell A12 and "Cat:Black:Large:0003" in cell A13. I will then drag this formula down column A and it will dynamically execute the formula against all column A cells above it.
I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
View 3 Replies View RelatedI am trying to come up with a user defined function to accomplish several things at once.
First, I need to lookup a reference value in one column and determine the value from another column (on the same worksheet) in the same row. Then, concatenate each "return" value (that isn't blank).
I have the following code so far, but my formula results in a zero. If I remove the On Error Resume Next, the formula results in a #VALUE error.
Public Function ConcatUnique(Separator As String, Ref As Variant, LkupCol As Range, _
RetCol As Range)
Dim lkup As Range
Dim ret As Range
Dim colDif As Long
Dim mCollect As New Collection
Dim i As Integer
Dim b As Variant
' Determine the number of columns difference between
' the lookup column and the return value column.
colDif = RetCol.Column - LkupCol.Column
On Error Resume Next
' Determine which lookup values in the lookup range match the reference value.
' When the lookup value matches the reference value, set the return range object
' to the cell in the return range (column) in the same row as the lookup value.
' Note we use the difference between the lookup column and the return column to
' determine the location of the return range object.
For Each lkup In LkupCol
If lkup.Value = Ref.Value Then Set ret = Range(Cells(lkup.Row, lkup.Column _
+ colDif))
' Store the return value in the collection object. Ignore any blank return
' values. Note we use the range value converted to a string as the key
' value.
If ret.Value <> "" Then mCollect.Add ret.Value, CStr(ret.Value)
' Loop through each cell in the lookup column range.
Next lkup
' Write each item from the collection and the separator to the final result,
' writing each value and the separator after the previous value and separator.
For i = 1 To mCollect.Count
b = b & mCollect(i) & Separator
Next i
ConcatUnique = Left$(b, Len(b) - Len(Separator))
End Function
I need a mcaro to concatenate my project. I have 1 number in six separate cells that I need to be concatenated into a 3 digit number without duplicates. My data is in cells A4:C5 (six numbers) note that I have two duplicate numbers so the results should only yield (10) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly.
My data in cells A11:C12 (six numbers) are all different so the results should yield (20) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly. I also wanted to know will I be able to edit the macro to concatenate 5, 6, 7, 8 or even 9 numbers into 3 digit numbers without duplicates. If so, how can this been done? The results would be 5#'s:10 results; 6#'s:20 results; 7#'s:35 results; 8#'s:56 results;
9#'s:81 results.
I have included a sample file.
I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?
View 9 Replies View RelatedI have a table with 3 columns and n number of rows looking like this:
A1 A2 A3
B1 B2 B3
C1 C2 C3
D1 D2 D3
...
I need to create a macro that gives all the sum combinations like this:
A1+B1+C1+D1
A1+B1+C1+D2
A1+B1+C1+D3
A1+B1+C2+D1
A1+B1+C2+D2
A1+B1+C2+D3
A1+B1+C3+D1
A1+B1+C3+D2
A1+B1+C3+D3
A1+B2+C1+D1
A1+B2+C1+D2
A1+B2+C1+D3
...
What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.
EG: if I choose 2,9,11,13,15,17&26, it would look something like this
2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.
If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.
Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.
I would like it to be in one sheet but if that can not be done on as many as it takes.
It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.
as an example i will use the national lottery. numbers 1 to 49 inclusive. i need a formula that will list all the possibile 6 number combinations not repeating any.
View 6 Replies View RelatedI have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
I'm trying to write a macro concatenate a report but the report outputs data on different rows when ideally I'd like it to all be on one row. The C column will have something in on the top line but blank for the rest until it gets to the next set of data.
Unfortunately it's not always the same amount of lines every time.
I have got the general principles of macros and VBA downpath.
Excel Example.xlsx
The document I'm working with is confidential, so this is an example. Basically, I'd like to string together the content of all cells from the second column associated with each company in the first column (separated by comma + space). In the original document, the companies are located in B2 to B8486, and the key words are located in D2 to D8486. My example uses ampersands, but I seem to understand Concatenate would yield the same results.
I have some records. I want to merge them through macro in a singel cell, like I have selected a range then I want to merge them in active cell with a comma.
View 9 Replies View RelatedSampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
I am attempting to concatenate several columns and I am getting the Type Mismatch error. I understand why I am getting this error, but I do not know any other way to accomplish what I am seeking.
[Code] ......
Is it possible to run a macro (concatenate) automatically on a sheet when data is refreshed on that sheet from access database? It there a code that has to be written in vb?
View 5 Replies View RelatedI have attached a spreadsheet illustrating my query.
I would like to run a sub procedure called generatedataset then I'd like to enter a date from column J (starting from J4) into G12 ( cell G12 is a named range called effective_date). Then enter the concatenation of the adjacent cells K4 and L4 and place this into cell G9 (cell G9 is a named range called dataset_name). Then call the procedure again. Repeating for all values in range starting at J4 extending down, and for all corresponding concatenated values in K and L.
Data would always be taken from columns J or K&L and placed in the same cells ie G12 (named range effective_date) and G9 (named range dataset_name) respectively.
Need to take column J20:J255 and column K20:K255 and concatenate into activesheet K20:K255. This needs to happen when OptionButton1.Value=True. The information in each cell will be different. The following code works well, but it will not allow me to put a space in between the two strings.
Private Sub OptionButton2_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("D20:D54")
If OptionButton2.Value = True Then
With DescriptionCell
.NumberFormat = General
.Formula = "=CONCATENATE('Bill of Materials-3'!F20,'Bill of Materials-3'!I20)"
End With
End If
End Sub
I need to write a line of code which puts the following into a cell:
=+C4 & " - " & 1
The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"
So my code will be something like:
Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"
This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
What I want is a macro that will give me the total of unique values within a given column. I would like the macro to give the total either at the end of the column or with a popup displaying the total.
Suppose in Column F
1
2
3
1
2
3
1
2
3
----
The macro would place 3 at the end of the column as the total of unique values or have a pop up that says 3.
I have the following table A
1
2
B
1
1
B
2
1
[Code] .....
I would like to extract all the unique rows from the above table so the outcome will be
A
1
2
B
1
1
[Code] .....
However I can't think of a simple macro to do it.
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
Is there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.
They should find uniques using all 3 columns, not find them within each column.
I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).