A little Excel assistance please.

Thread: A little Excel assistance please.

  1. DejaVu's Avatar

    DejaVu said:

    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: [View]
    =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. akimba's Avatar

    akimba said:

    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. DejaVu's Avatar

    DejaVu said:

    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. flipper321's Avatar

    flipper321 said:

    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. DejaVu's Avatar

    DejaVu said:

    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