+ Reply to Thread
Results 1 to 10 of 10

how to mix six columns of numbers

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    crema italy lombardia
    MS-Off Ver
    Excel 2003
    Posts
    9

    how to mix six columns of numbers

    Can anyone help me mixed in order the 6 columns , need to put tham like 111112,111113, so on so for up to 38 54 60 56 50 34, i cant do
    =$A3&$B3&$C3&$D3&$E3&F3 end drag it down, the ar other way more easy to do it?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: how to mix six columns of numbers

    I don't quite know what you are after as that seems fine as a solution, but you can try =CONCATENATE(A3,B3,C3,D3,E3,F3)

  3. #3
    Registered User
    Join Date
    10-11-2013
    Location
    crema italy lombardia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Wink Re: how to mix six columns of numbers

    the ar 292 numbers on 6 columns i what to mix thes way:
    =$A$3&$B$3&$C$3&$D$3&$E$3&F4
    010101010102
    010101010103
    010101010104
    010101010105
    010101010106
    i make only last 2 columns ar more easy to to it; if it to long let make it 10 numbers on 6 columns but the idee it same ,how to mix thes 6 columns on easy way?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: how to mix six columns of numbers

    Are you trying to get all possible combination of numbers from the 6 columns? So if you use 10 numbers from each column you end up with 10^6 combinations (1 million combinations)?

  5. #5
    Registered User
    Join Date
    10-11-2013
    Location
    crema italy lombardia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: how to mix six columns of numbers

    Yes 1 mil. comb. but if it 3 columns at 10 number (it only 1000 comb.), how to mix tham? Is there any formula that can mix these numbers?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: how to mix six columns of numbers

    A macro would probably give you more control over what you want. However if you want to mix 10 rows of the last three columns try:
    =A$3&B$3&C$3&OFFSET(D$3,MOD(INT(ROW()-3)/100,10),0)&OFFSET(E$3,MOD(INT(ROW()-3)/10,10),0)&OFFSET(F$3,MOD(ROW()-3,10),0)

    it should create the result in a single column. You may have to play around with the formula if you change the cells in your spreadsheet

  7. #7
    Registered User
    Join Date
    10-11-2013
    Location
    crema italy lombardia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: how to mix six columns of numbers

    =A$3&B$3&C$3&OFFSET(D$3;MOD(INT(ROW()-3)/100;10);0)&OFFSET(E$3;MOD(INT(ROW()-3)/100;10);0)&OFFSET(F$3;MOD(ROW()-3/100;10);0) i change the , in to ; idont know why it thes way on me Excel, any way it no working it gave me 0

  8. #8
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: how to mix six columns of numbers

    the ";" instead of "," is based on your windows settings, you can change it if you want by changing the 'list separator' symbol i the language->date & time settings.
    In the formula you have changed for formula in two parts where "/100" is given, the first is /100, the second is /10 and third does not divide at all. See above. You will need to copy to all cells you are using for the output
    appreciate someone's help? then click *

  9. #9
    Registered User
    Join Date
    10-11-2013
    Location
    crema italy lombardia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Thumbs up Re: how to mix six columns of numbers

    Thank you so much for your help, it working i no make attention on the /100 , but now i nkow what i cant change if i whan more than 10 number's on column , how i put solved???

  10. #10
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: how to mix six columns of numbers

    Below is the solution if you want to expand the number of rows to 15. You can change all instances of 15 to however many number of rows you need
    =A$3&B$3&C$3&OFFSET(D$3,MOD(INT(ROW()-3)/15^2,15),0)&OFFSET(E$3,MOD(INT(ROW()-3)/15,15),0)&OFFSET(F$3,MOD((ROW()-3),15),0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 2 separate columns with numbers separated by commas and single numbers not, need sum
    By Slothbob in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2013, 04:33 PM
  2. Replies: 1
    Last Post: 03-09-2013, 02:55 PM
  3. Replies: 6
    Last Post: 01-07-2013, 08:58 PM
  4. Comparing numbers in lists/columns of numbers
    By Onesimus in forum Excel General
    Replies: 3
    Last Post: 12-15-2007, 02:19 PM
  5. [SOLVED] Find similar numbers from two columns of numbers
    By Dennis Andrews in forum Excel General
    Replies: 1
    Last Post: 11-30-2005, 04:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1