+ Reply to Thread
Results 1 to 16 of 16

Combine data from two columns

  1. 04-28-2021, 04:38 AM #1
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Combine data from two columns

    Hi,

    I need to update a column with raw data from insurance company together with a column I sometimes update manually. They are both a list of customers, but the data from the insurance company is not always up to date. So I therefor need to have a column where I automatically find the customers that I manually put in together with the customers from the insurance company (raw data). The result will be one column with all customers (together with the comments in another column). This will then be pasted back to a column that has all the updated data. Pls see the file for more information.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-28-2021, 05:53 AM #2
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    It may sound like a circle reference thing, but as long as I perform the operations in specific order it should make sense.

    Pls let me know if you need additional information.
    Reply With QuoteRegister To Reply
  3. 04-28-2021, 01:33 PM #3
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    Does anyone feel up to the challenge?
    Reply With QuoteRegister To Reply
  4. 04-29-2021, 01:51 AM #4
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    I guess I am overcomplicating it. Trying to simplify: All I need in column D is to add the customers in column A that are not in column G.

    Pls see the new attachment.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-29-2021, 03:32 AM #5
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Combine data from two columns

    Maybe try

    =LET(g,G5:G16,a,A5:B16,c,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,g,INDEX(a,,1))&"</m></x>","//m")),CHOOSE({1,2},c,VLOOKUP(c,a,2,0)&""))
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  6. 04-29-2021, 03:36 AM #6
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    Wow - that is one crazy formula I will try it out.
    Reply With QuoteRegister To Reply
  7. 04-29-2021, 05:58 AM #7
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    _xlpm and _xlfn are included in the formula when I open the file. And when I remove them I get an error message.
    Reply With QuoteRegister To Reply
  8. 04-29-2021, 06:28 AM #8
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Combine data from two columns

    Update your MS365 and try this


    =CHOOSE({1,2},UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,G5:G16,A5:A16)&"</m></x>","//m")),VLOOKUP(UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,G5:G16,A5:A16)&"</m></x>","//m")),A5:B16,2,0)&"")

    https://support.microsoft.com/en-us/...a-40204fb85e1e
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-29-2021, 06:29 AM #9
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    This is weird. The let formula works on my personal computer running Office 365, but it doesn't work on my work computer which runs Office 365 Pro Plus. It is on my work computer I need the formula. Does anyone know how to solve this?
    Reply With QuoteRegister To Reply
  10. 04-29-2021, 06:30 AM #10
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    Oh, I must have posted right after you did Bo_Ry.
    Reply With QuoteRegister To Reply
  11. 04-29-2021, 06:33 AM #11
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    That one worked Thank you so much!!!
    Reply With QuoteRegister To Reply
  12. 04-29-2021, 08:56 AM #12
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    Then again... I seem to have problems when I drag the formula down to include more rows. I have locked each cell and dragged. But says I can't change part of an array...
    Reply With QuoteRegister To Reply
  13. 04-29-2021, 09:38 AM #13
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Combine data from two columns

    This is a dynamic spill array, no need to lock drag formula down, only place the formula in 1 cell at D5 and increase the range.

    https://support.microsoft.com/en-us/...%20rows%20tall.


    =CHOOSE({1,2},UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,G5:G99,A5:A99)&"</m></x>","//m")),IFERROR(VLOOKUP(UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,G5:G99,A5:A99)&"</m></x>","//m")),A5:B99,2,0)&"",""))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 04-29-2021, 10:58 AM #14
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    I see. Pretty cool. Thanks!
    Reply With QuoteRegister To Reply
  15. 04-29-2021, 11:42 AM #15
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    One last question is it a way to have the list in column G just the way it is instead of unique values? But from column A I can add just the unique values in addition? Sometimes the customers are listed multiple times in column G and I kind of need that.
    Reply With QuoteRegister To Reply
  16. 04-30-2021, 06:06 AM #16
    Even
    Even is offline
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: Combine data from two columns

    It is so close that I have this ready, and I appreciate it if you could look at it.

    Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Combine multiple columns of data into one column, where the number of columns may vary
    By jeremymosst in forum Excel General
    Replies: 6
    Last Post: 11-25-2015, 01:01 PM
  2. [SOLVED] How do I combine data from 4 columns to one?
    By jim0000 in forum Excel General
    Replies: 5
    Last Post: 07-01-2015, 03:11 PM
  3. [SOLVED] Combine 2 Columns of Data
    By Karen615 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-21-2014, 02:05 PM
  4. [SOLVED] combine many columns data into half columns data by creating rows
    By killerware in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-08-2013, 07:53 AM
  5. Duplicate rows, delete columns w/same data, combine columns w/unique data, Mac Excel 2011
    By msmcoin in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-03-2013, 02:10 PM
  6. How can I combine data from 3 columns into one?
    By slywuf in forum Excel General
    Replies: 4
    Last Post: 03-09-2009, 05:24 PM
  7. Combine data in two columns
    By ucf1020 in forum Excel General
    Replies: 3
    Last Post: 02-10-2005, 03:36 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1