Close

Results 1 to 5 of 5
  1. #1
    DF Super Moderator
    DejaVu's Avatar
    Join Date
    Nov 2005
    Location
    Essex
    Posts
    9,100
    Thanks
    1,834
    Thanked:        3,998
    Karma Level
    957

    Default A little Excel assistance please.

    I'm using Appsheet.com to create my own Android App and it utilises Excel/Google Sheets for charts and information.

    I'm trying to create a formula for a certain scenario. I'll try and explain it as best I can.

    In Column A I've got addresses.
    In Column G I've got the Date.
    Column K has an amount in monetary value.

    I'd like to consolidate all the addresses within that month into a new cell separated by commas - or better still, by a new line within the same cell. Make sense?
    I've managed to sort out adding the amounts into one cell via month using something like this which works out the amount for the month of May 2017.
    Code:
    =SUMIFS(K:K,G:G,">="&DATE(2017,5,1),G:G,"<"&DATE(2017,6,1))
    What would I need to use to do this?
    I'm guessing IFS and TEXT along with CONCENTATE perhaps, but after trying to work it out for the day and plenty of googling, I'm getting nowhere.

    I reckon it's probably pretty straight forward, but I cannot bend my head around it - or is there an easier way for either that I've missed without the need for the serious programming side of things?




  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,817
    Thanks
    1,020
    Thanked:        770
    Karma Level
    297

    Default Re: A little Excel assistance please.

    Have you looked at SUMPRODUCT for the totals.

    I still not sure what you mean with the addresses?

  3. #3
    DF Super Moderator
    DejaVu's Avatar
    Join Date
    Nov 2005
    Location
    Essex
    Posts
    9,100
    Thanks
    1,834
    Thanked:        3,998
    Karma Level
    957

    Default Re: A little Excel assistance please.

    Quote Originally Posted by akimba View Post
    Have you looked at SUMPRODUCT for the totals.

    I still not sure what you mean with the addresses?
    Addresses are customers address.

    If something is equal to yes during each month, list those addresses separated by commas by month in a single cell.

    Sent from my SM-G935F using Tapatalk


  4. #4
    DF Jedi flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,694
    Thanks
    11
    Thanked:        131
    Karma Level
    408

    Default Re: A little Excel assistance please.

    This isn't possible in a single cell without writing a UDF (user defined function).

    The other option is to add an additional column to do the concatenation and a lookup to find the last entry.

  5. #5
    DF Super Moderator
    DejaVu's Avatar
    Join Date
    Nov 2005
    Location
    Essex
    Posts
    9,100
    Thanks
    1,834
    Thanked:        3,998
    Karma Level
    957

    Default Re: A little Excel assistance please.

    Using further fields/worksheets to grab the data is not a problem if required.

    Only reason I don't think UDFs are not for me is I have no idea about them and likely to boil my box!

    Sent from my SM-G935F using Tapatalk


Social Networking 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
  •