-1

I have a google sheet where Column A has Buy Date and Column L has Sell Date. Column B to K has several other data. There are multiple buys on different dates but selling is on a single date.

What I want is the difference between the buy date as sell date. Buy date will be the 1st date it was purchased and rest other buy dates of the same product is ignored for that calculation. Here is a sample sheet to better understand.

https://docs.google.com/spreadsheets/d/1EC9mRXDV6WYqc0SOBn0tHeIpDm33jMForPgXVEphKKA/edit?usp=sharing

I do not want use scripts or have any circular reference errors for this purpose.

pppery
  • 3,731
  • 22
  • 33
  • 46
Bhavesh Pamecha
  • 27
  • 6

1 Answers1

0

You may try:

=map(B2:B,L2:L,A2:A,lambda(Σ,Δ,Ξ,if(Δ="",,index(let(
                    Γ,if((B2:B=Σ)*len(L2:L),row(B2:B),),
                    Λ,xlookup(row(Σ)-1,Γ,Γ,,-1)+1,
     days(Δ,xlookup(Σ,indirect("B"&Λ):Σ,indirect("A"&Λ):Ξ,)))))))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • Excellent. Once again you came to the rescue. Just wanted to know what are these (Γ and Λ) characters and can I change them? – Bhavesh Pamecha Apr 28 '23 at 13:09
  • those symbols are identifiers I use within [lambda()](https://support.google.com/docs/answer/12508718?hl=en) & [let()](https://support.google.com/docs/answer/13190535?hl=en) for my simplicity. You can just change them to whatever suits ya:) – rockinfreakshow Apr 28 '23 at 13:12
  • But what data represents them? I mean the name I keep should be meaningful. I understand these Σ,Δ,Ξ but I don't understand what these Γ and Λ are reffering to. – Bhavesh Pamecha Apr 28 '23 at 13:15
  • Γ is for this part `if((B2:B=Σ)*len(L2:L),row(B2:B),)` & Λ for `xlookup(row(Σ)-1,Γ,Γ,,-1)+1`. Please do checkout let() and it should be pretty clear – rockinfreakshow Apr 28 '23 at 13:18