+ Reply to Thread
Results 1 to 2 of 2

compiling values from 5 columns into a new column

  1. #1
    Registered User
    Join Date
    03-27-2021
    Location
    dublin ireland
    MS-Off Ver
    21.02
    Posts
    2

    compiling values from 5 columns into a new column

    Hi

    I joined multiple sheets in a outer join,
    I now would have 5 columns with country names, and would like to compile them into a new column and delete the old ones.

    I used concat to join them, but When I delete the old columns the formula stopped working.

    How do I permanently move the values?
    The dataset has a large number of rows and it's hard to say which column has the values.

    Thanks,
    Shane
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: compiling values from 5 columns into a new column

    What version of Excel do you have? What does it say in the green recatngle that appears when you launch Excel?

    If it is befor O365,

    =IFERROR(INDEX($1:$1048576,ROUND(10^6*MOD(AGGREGATE(15,6,(ROW($B$2:$Z$500)/10^6+COLUMN($B$2:$Z$500))/($B$2:$Z$500<>""),ROWS(A$2:A2)),1),0),INT(AGGREGATE(15,6,(COLUMN($B$2:$Z$500)+ROW($B$2:$Z$500)/10^6)/($B$2:$Z$500<>""),ROWS(A$2:A2))/1)),"")

    You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



+ 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. Compiling a list from a column of data.
    By nostrum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 04:35 AM
  2. Compiling several columns into one, eliminating gaps!
    By Tayloroid in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-18-2013, 11:02 AM
  3. identifying multiple text and compiling values
    By CLIFFORD LEONG in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-24-2013, 02:34 AM
  4. Compiling data from one column into separate columns
    By Leinad7x in forum Excel General
    Replies: 4
    Last Post: 11-10-2012, 09:42 AM
  5. [SOLVED] Compiling values based on information in secondary column
    By thisisabicycle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2012, 04:43 PM
  6. Compiling data entered in rows and columns
    By nosirrah_nad in forum Excel General
    Replies: 2
    Last Post: 09-09-2008, 10:50 AM
  7. Compiling macro based on cell values
    By simonsmith in forum Excel General
    Replies: 1
    Last Post: 05-16-2006, 03:31 PM

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