logo
down
shadow

Excel - List all the combinations of two words from one list, in two other columns


Excel - List all the combinations of two words from one list, in two other columns

By : user2948441
Date : November 14 2020, 04:51 PM
This might help you Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code),
code :
Sub name_by_name()
    Dim i As Long, j As Long, lr As Long
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To lr
            For j = i + 1 To lr
                .Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = _
                  .Cells(i, 1).Value & ", " & .Cells(j, 1).Value
            Next j
        Next i
    End With
End Sub


Share : facebook icon twitter icon
How to get ALL combinations of a list of words using ANY number of words

How to get ALL combinations of a list of words using ANY number of words


By : user25157
Date : March 29 2020, 07:55 AM
I hope this helps . I have searched but I can't find anything that matches my query. I have seen lots of solutions where people want all combinations of numbers/words that use ALL the options, but none like this... , FINAL ANSWER AT THE BOTTOM
code :
$str = "apple pear banana";
$str_splode = explode(' ',$str);

echo showCombo($str_splode[0], $str_splode);

function showCombo($str, $arr){
    $ret = '';
    foreach($arr as $val){
       if($val != $str)
           $ret .= $str.showCombo($val, $arr);
    }
    return $ret;
}
<?php

$str = "apple pear banana orange";
$str_splode = explode(' ',$str);
print_r(showCombo(array(), $str_splode));

function showCombo($str_arr, $arr){
    $ret = array();
    foreach($arr as $val){
       if(!in_array($val, $str_arr)){
           $temp = $str_arr;
           $temp[] = $val;
           print_r($temp);
           $comb = showCombo($temp, $arr);
           if(count($comb) > 0)
              $ret[] = $comb;
       }
    }
    return $ret;
}
?>
<?php

$str = "apple pear banana orange";
$str_splode = explode(' ',$str);
print_r(showCombo(array(), $str_splode));

function showCombo($str_arr, $arr){
    $ret = array();
    foreach($arr as $val){
       if(!in_array($val, $str_arr)){
           $temp = $str_arr;
           $temp[] = $val;
           $ret[$val] = $temp;
           $ret[$val][] = showCombo($temp, $arr);
       }
    }
    return $ret;
}

?>
<?php

$str = "apple pear banana orange";
$str_splode = explode(' ',$str);
$combos = showCombo(array(), $str_splode);
foreach($combos as $key=>$array){
    echo $key.PHP_EOL;
    displayArrayByKey($key, $array);
}


function displayArrayByKey($str, $arr){
    foreach($arr as $key=>$array){
          $string = $str. " " . $key;
          echo $string . PHP_EOL; 
          if(count($array)> 0){
              displayArrayByKey($string, $array);
          }
    }
}

function showCombo($str_arr, $arr){
    $ret = array();
    foreach($arr as $val){
       if(!in_array($val, $str_arr)){
           $temp = $str_arr;
           $temp[] = $val;
           $ret[$val] = showCombo($temp, $arr);
       }
    }
    return $ret;
}

?>
Apply List of functions on List of columns based on different combinations

Apply List of functions on List of columns based on different combinations


By : user3879304
Date : March 29 2020, 07:55 AM
will be helpful for those in need I have a dataframe df with three categorical variables cat1,cat2,cat3 and two continuous variables con1,con2. I would like to compute list of functions sd,mean on list of columns con1,con2 based on different combinations of list of columns cat1,cat2,cat3. I have done them explicitly subsetting all different combinations. , This is a simple one-line base solution:
code :
> do.call(cbind, lapply(funs, function(x) aggregate(cbind(con1, con2) ~ cat1 + cat2 + cat3, data = df, FUN = x, na.rm = TRUE)))
   sd.cat1 sd.cat2 sd.cat3  sd.con1   sd.con2 mean.cat1 mean.cat2 mean.cat3 mean.con1 mean.con2
1        A       C       B       NA        NA         A         C         B  25.52641  37.40603
2        B       C       B 32.67192  6.966547         B         C         B  46.70387  34.85437
3        A       D       B 31.05224  6.530313         A         D         B  37.91553  37.13142
4        B       D       B 23.80335  6.001468         B         D         B  59.75107  30.29681
5        A       E       B 22.79285  1.526472         A         E         B  38.54742  25.23007
6        B       E       B 32.92139  2.621067         B         E         B  51.56253  29.52367
7        A       C       C 26.98661  5.710335         A         C         C  36.32045  36.42465
8        B       C       C 20.22217  8.117184         B         C         C  60.60036  34.98460
9        A       D       C 33.39273  7.367412         A         D         C  40.77786  35.03747
10       B       D       C 12.95351  8.829061         B         D         C  49.77160  33.21836
11       A       E       C 33.73433  4.689548         A         E         C  55.53135  32.38279
12       B       E       C 25.38637  9.172137         B         E         C  46.69063  31.56733
13       A       C       D 36.12545  6.323929         A         C         D  48.34187  32.36789
14       B       C       D 30.01992  7.130869         B         C         D  53.87571  33.12760
15       A       D       D 15.94151 11.756115         A         D         D  35.89909  31.76871
16       B       D       D 10.89030  6.829829         B         D         D  22.86577  32.53725
17       A       E       D 24.88410  6.108631         A         E         D  47.32549  35.22782
18       B       E       D 12.73711  8.151424         B         E         D  33.95569  36.70167
Finding all combinations of words of length 2 from list of words using python

Finding all combinations of words of length 2 from list of words using python


By : DoraNicole
Date : March 29 2020, 07:55 AM
With these it helps For combinations you can use the itertools module.
code :
import itertools
temp1 = itertools.combinations(filtered_tokens, 2)
temp1 = list(temp1)
How to list all possible combinations of the values in three columns in excel?

How to list all possible combinations of the values in three columns in excel?


By : Ajay Sonawane
Date : March 29 2020, 07:55 AM
I wish this helpful for you I have three columns, each of which has different kinds of master data as shown below: , You can do this with a formula:
code :
=IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))&" "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&" "&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),"")
Create a table with all potential combinations from a given list with two columns (excel)

Create a table with all potential combinations from a given list with two columns (excel)


By : Anandraj D.s
Date : September 28 2020, 02:00 AM
With these it helps This vba module should solve your problem. Just copy the code to a new module, declare the input and output columns and the number of the first row of your list. Note that the code will stop once it hits a line where the "Unique Identifier" Cell is empty. Also, it requires that your list is sorted with respect to your "Unique Identifier". If a Unique Identifier only appears once, it will still be written into the output list, but only once and with the outColNation2 staying empty in that row. If this is not desired and it should be left out entirely, just delete the commented if-statement.
Example Image of output
code :
Option Compare Text

Sub COMBINATIONS()

Dim i As Long, j As Long, k As Long, l As Long, n As Long
Dim arr(100) As String
Dim UI As String

Dim inColUI As Integer, inColNation As Integer
Dim outColUI As Integer, outColNation1 As Integer, outColNation2 As Integer
Dim FirstRowOfData As Integer
Dim YourWS As Worksheet

inColUI = 1  'Column of the "Unique Identifier"
inColNation = 2 'Column of the "Nations" in your example

outColUI = 4
outColNation1 = 5   'output columns
outColNation2 = 6

FirstRowOfData = 2  'First Row of data

Set YourWS = Application.Worksheets("Sheet1") 'Put in your Worksheet Name here.

i = FirstRowOfData
n = FirstRowOfData
With YourWS
    Do Until .Cells(i, inColUI) = ""
        j = 0
        UI = .Cells(i, inColUI)
        Do Until .Cells(i - 1, inColUI) <> .Cells(i, inColUI) And j > 0 Or .Cells(i, inColUI) = ""
            arr(j + 1) = .Cells(i, inColNation)
            i = i + 1
            j = j + 1
        Loop
        If j = 1 Then '<- remove this if-statement and the following marked lines if single appearing UIs should be omitted entirely
            .Cells(n, outColUI) = UI '<---
            .Cells(n, outColNation1) = arr(1) '<---
            n = n + 1 '<---
        Else '<---
            For k = 1 To j
                For l = 1 To j
                    If arr(k) <> arr(l) Then
                        .Cells(n, outColUI) = UI
                        .Cells(n, outColNation1) = arr(k)
                        .Cells(n, outColNation2) = arr(l)
                        n = n + 1
                    End If
                Next l
            Next k
        End If '<---
    Loop
End With

End Sub
Related Posts Related Posts :
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org