+ Reply to Thread
Results 1 to 2 of 2

Concatenate cells based on criteria/threshold

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    289

    Concatenate cells based on criteria/threshold

    Hello,

    I am hoping to get some assistance. I have attached a variance tool which consists of two sets of data. First, the file loops through over 120 different variance files and stacks the data onto the variance comments tab using VBA (I removed a large amount of it for file size purposes). The RET Recoveries tab is the second source of data which is a hyperion pull that grabs financial data from our accounting system. In columns AC and AD of the RET Recoveries tab is a field for variance comments. I am looking to concatenate the variance comments from the variance comments tab (Column D) in column AC of the RET Recoveries tab for each time it finds revenue comments (PCRETRev) for a particular business unit (Column CN on Variance Comment tab and Column A on RET Recoveries tab) and comments in column AD for expense comments (RETax). The trick is the variance comments has 12 months of data and the RET Recovery tab has one month of data (August). I want it to concatenate comments only when amounts in column AZ -August Variance column are not equal to blank or zero and dynamic enough so when I change the month in cell E9 of the RET Recoveries tab it knows to change the column to do the criteria search on (ex: Column AZ). Hopefully this makes sense.

    Thanks in Advance!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Concatenate cells based on criteria/threshold

    This is a considerable undertaking might I suggest a change in approach. Not sure which version of hyperion you are using but most have a HsSetText function. Presumably the business units or cost centre owners submit their forecasts in to Hyperion, could they not also submit their variance analysis in a similair fashion each month?. You then merely have to extend RET recoveries with a HSGetText function column and save the effort of a VBA macro. You also get all your data in one place in hyperion.
    Please consider adding a * if I helped.

+ 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. VBA to sort and filter based on criteria or threshold
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2014, 03:53 PM
  2. Calculating records based on a range threshold criteria
    By mramo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2013, 01:31 PM
  3. Concatenate a range based on Criteria
    By nuruedriss in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 10:32 AM
  4. How to concatenate a variable number of rows/cells based on multiple criteria?
    By jonharadon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2012, 09:22 PM
  5. Concatenate multiple cells based on specific criteria
    By satkadeb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2011, 08:50 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