+ Reply to Thread
Results 1 to 2 of 2

How do I concatenate 2 lists and their data into a macro?

  1. #1
    Registered User
    Join Date
    10-12-2018
    Location
    london
    MS-Off Ver
    2016
    Posts
    1

    Unhappy How do I concatenate 2 lists and their data into a macro?

    Hi, I'm hoping someone could help. I'll explain what I'm trying to achieve and then what I've tried below that.

    I have Sheet1 with 2 account columns and actuals data in column 3. I want to bring in the forecast and budget data from Sheet2, however there may be additional combinations in sheet 2 that don't exist on sheet1.

    Sheet1
    Account SubAccount Actual Fcst Budget
    100000 9000 500.00
    200000 5000 250.00
    300000 7000 1000.00


    Sheet 2 will have Fcst and Budget data against the same and additional account combinations. Account 200000 from Sheet 1 doesn' t exist and the last 2 lines are new combinations that don't exist in Sheet 1.

    Sheet2
    Account SubAccount Fcst Budget
    100000 9000 500.00 1000
    300000 7000 1000.00 1000
    400000 9000 700.00 1000
    300000 9000 800.00 1000

    The result would be a combination of both the sheets pulling with all the combinations and data.

    Result
    Account SubAccount Actual Fcst Budget
    100000 9000 500.00 500.00 1000
    200000 5000 250.00
    300000 7000 1000.00 1000.00 1000
    400000 9000 700.00 1000
    300000 9000 800.00 1000

    I've been trying to solve this problem with vlookup and index formulas without much joy. So I can look up the account combinations with a sumif and compare, but can't figure out how to add the new ones. I can identify new combinations in both sheets with a vlookup, but struggle to combine into one list and bring the values into one sheet. As I need to do this regularly, this would be best suited to a macro. Any help?

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: How do I concatenate 2 lists and their data into a macro?

    One possible approach attached -- uses a 3rd tab to consolidate the Actuals & Forecast/Budget data, and would account for duplicate entries (presumed unlikely given nature of data, but catered for regardless). There will be much slicker ways of doing it, for sure.
    Attached Files Attached Files

+ 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] Macro Needed to Concatenate Data in separate ROWS
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-25-2015, 01:20 PM
  2. [SOLVED] Macro Needed to Concatenate Data in separate ROWS
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-21-2015, 11:35 AM
  3. [SOLVED] Macro to concatenate data on cells with matching value in A
    By s00shi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-20-2014, 11:30 AM
  4. Macro to concatenate cell contents from subsets of data
    By Rjhopkins1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2013, 01:34 PM
  5. I need a macro to enter data based on data validation lists.
    By DorothyFan1 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2012, 08:58 AM
  6. Concatenate validation lists to same sheet.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2011, 03:36 PM
  7. Trying to CONCATENATE /add some data in a macro and am lost
    By cpeters in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-21-2006, 06:49 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