0

I'm loosing my mind with problem in Google Sheets. We are using Appsheet to insert data for daily revenue for different services. So rows are days and columns are services with revenue. I have several problems:

  • each line represents one entry, I would like one line to represent one day
  • there are now several lines for one day, I would like to combine them into one line
  • for one day, there can be several entries for the same service (updated by the employee)

In short, I would like to somehow build a formula that would combine the same days into one row for me, if there are two entries for the same day for the same service, it would consider the last one added and add the missing days with a blank row or with 0.00 in the service columns.

I was playing with VLOOKUP but looks like this is not correct approach. I would like to make these changes in new table (on new sheet or in the same, left to the existing entries, because I need these entries for tracking records).

Date Service1 Service2 Service3 Service4 Service5
1. 1. 2024 € 5,00 € 10,00 € 5,00
1. 1. 2024 € 15,00 € 10,00
3. 1. 2024 € 10,00 € 15,00 € 10,00 € 10,00 € 10,00
4. 1. 2024 € 10,00 € 15,00
4. 1. 2024 € 15,00
4. 1. 2024 € 15,00
6. 1. 2024 € 10,00 € 15,00 € 10,00

Thanks in advance for any help, I'm really lost how to build this, but I need until 1.1.2024 :)

Table

On left structure of entries, on right wanted table

Link to example sheet: Google Sheet Example

P.S. dates will be added for a whole year. It's for tracking daily sales revenue.

2
  • If you post a link to your sample spreadsheet (which will provide live data and a place to directly test), you may get more engagement with this.
    – Erik Tyler
    Dec 27, 2023 at 17:18
  • @ErikTyler thanks, I added a link.
    – Karnak
    Dec 27, 2023 at 17:24

1 Answer 1

1

Use query() and vlookup(), like this:

=let( 
  data; query( 
    List30!A1:F; 
    "select Col1, sum(Col2), sum(Col3), sum(Col4), sum(Col5), sum(Col6) 
     where Col1 is not null 
     group by Col1"; 
    1 
  ); 
  dates; choosecols(data; 1); 
  dateSeries; vstack( 
    List30!A1; 
    sequence(max(dates) - min(dates) + 1; 1; min(dates)) 
  ); 
  hstack( 
    dateSeries; 
    arrayformula(ifna(vlookup( 
      dateSeries; 
      data; 
      sequence(1; columns(data) - 1; 2); 
      false 
    ))) 
  ) 
)

See let(), query(), choosecols(), vstack(), hstack(), vlookup(), sequence(), and arrayformula().

The formula is in cell Solution!A1 of your sample spreadsheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.