Formula to Number Multi-Part Transactions and Re-Start Count Each Day

[turn7or5]

Can anyone provide a formula to do this? Your help is greatly appreciated.

Each transaction has multiple buy and sells and each needs the same count number to show it is all the same transaction. Example: Rows 21-24 are all one transaction so each row has same number of 8.

Each time Qnty (Clmn F) sums to zero a transaction is completed. Shown in helper column K.

When the date changes (Clmn B Row 52) the count needs to start at 1 again.

Column A has no formula, just a model to show what desired result looks like.

Basic idea is identify and assign a number to each transaction and start new at 1 the next day.

It seemed simple but I cant solve it.

 

[turn7or5]

https://i.postimg.cc/xjRFz3Zs/Excel-Prob-ME.png

Image should appear now

 

[Eric W]

Welcome to the MrExcel board!

Try:

Book1
	A	B	C
1	Index	Date	Qty
2		9/29/2022
3	1	9/29/2022	3
4	1	9/29/2022	-3
5	2	9/29/2022	3
6	2	9/29/2022	-3
7	3	9/29/2022	3
8	3	9/29/2022	-3
9	4	9/29/2022	3
10	4	9/29/2022	-3
11	5	9/29/2022	3
12	5	9/29/2022	-3
13	6	9/29/2022	3
14	6	9/29/2022	3
15	6	9/29/2022	-3
16	6	9/29/2022	-3
17	7	9/29/2022	3
18	7	9/29/2022	3
19	7	9/29/2022	-3
20	7	9/29/2022	-3
21	8	9/29/2022	3
22	8	9/29/2022	3
23	8	9/29/2022	-3
24	8	9/29/2022	-3
25	9	9/29/2022	1
26	9	9/29/2022	-1
27	10	9/29/2022	1
28	10	9/29/2022	-1
29	11	9/29/2022	2
30	11	9/29/2022	-2
31	12	9/29/2022	1
32	12	9/29/2022	1
33	12	9/29/2022	-1
34	12	9/29/2022	-1
35	13	9/29/2022	1
36	13	9/29/2022	-1
37	14	9/29/2022	1
38	14	9/29/2022	-1
39	15	9/29/2022	1
40	15	9/29/2022	-1
41	16	9/29/2022	1
42	16	9/29/2022	-1
43	17	9/29/2022	1
44	17	9/29/2022	1
45	17	9/29/2022	-1
46	17	9/29/2022	-1
47	18	9/29/2022	1
48	18	9/29/2022	-1
49	19	9/29/2022	1
50	19	9/29/2022	1
51	19	9/29/2022	-2
52	1	6/21/2023	1
53	1	6/21/2023	1
54	1	6/21/2023	1
55	1	6/21/2023	-1
56	1	6/21/2023	-1
57	1	6/21/2023	-1
Sheet1

Cell Formulas
Range	Formula
A3:A57	A3	=IF(B2<>B3,1,IF(SUM(C$2:C2)=0,A2+1,A2))

 

[turn7or5]

It worked Thank you, Eric W.

 

[turn7or5]

Just discovered a case in which formula in Column A returns unwanted results. The data used initially did not have this feature.
It happens when a new transaction starts before the current one is completed.
See rows 62-115 for example. (LEFT table is current and RIGHT table is desired result)
Six units of Onions are sold at F62-63 and not resolved until F113-F115. All nine transactions in between are counted as the same, with a number 4.
The formula I think looks for the Qnty Column to sum to zero to move onto the next count.

I tried to fix it by including the Item column so Qnty would exclude non-matching items when summing Qnty Column.
I also tried to make it count properly if a transaction was left unresolved until a later day. Attempted formulas did not work.

There is no formula in Column N. Its just a model to show desired result.

Im hoping the formula might be modified to solve this or some other solution.

The first solution will be re-checked once this is resolved because it was correct for initial data.

 

[Eric W]

This requires a much different approach. Try:

Book1
	A	B	C	D	E
1
2	Transaction Number	Date	Time	Item	Qty
3	1	3/1/2023		Banana	2
4	1	3/1/2023		Banana	-2
5	2	3/1/2023		Onion	-3
6	2	3/1/2023		Onion	-3
7	3	3/1/2023		Leek	20
8	3	3/1/2023		Leek	10
9	3	3/1/2023		Leek	-10
10	3	3/1/2023		Leek	-10
11	3	3/1/2023		Leek	-10
12	4	3/1/2023		Leek	8
13	4	3/1/2023		Leek	-8
14	5	3/1/2023		Vinegar	4
15	5	3/1/2023		Vinegar	4
16	5	3/1/2023		Vinegar	4
17	5	3/1/2023		Vinegar	4
18	5	3/1/2023		Vinegar	8
19	5	3/1/2023		Vinegar	8
20	5	3/1/2023		Vinegar	-8
21	5	3/1/2023		Vinegar	-8
22	5	3/1/2023		Vinegar	-8
23	5	3/1/2023		Vinegar	-8
24	6	3/1/2023		Melon	5
25	6	3/1/2023		Melon	5
26	6	3/1/2023		Melon	-5
27	6	3/1/2023		Melon	-5
28	7	3/1/2023		Melon	5
29	7	3/1/2023		Melon	-5
30	8	3/1/2023		Melon	5
31	8	3/1/2023		Melon	5
32	8	3/1/2023		Melon	5
33	8	3/1/2023		Melon	-5
34	8	3/1/2023		Melon	-5
35	8	3/1/2023		Melon	-5
36	9	3/1/2023		Bread	20
37	9	3/1/2023		Bread	20
38	9	3/1/2023		Bread	20
39	9	3/1/2023		Bread	20
40	9	3/1/2023		Bread	-20
41	9	3/1/2023		Bread	-20
42	9	3/1/2023		Bread	-20
43	9	3/1/2023		Bread	-20
44	10	3/1/2023		Olive	5
45	10	3/1/2023		Olive	5
46	10	3/1/2023		Olive	-5
47	10	3/1/2023		Olive	-5
48	11	3/1/2023		Peach	10
49	11	3/1/2023		Peach	10
50	11	3/1/2023		Peach	10
51	11	3/1/2023		Peach	10
52	11	3/1/2023		Peach	-10
53	11	3/1/2023		Peach	-10
54	11	3/1/2023		Peach	-10
55	11	3/1/2023		Peach	-10
56	2	3/1/2023		Onion	2
57	2	3/1/2023		Onion	2
58	2	3/1/2023		Onion	2
59	12	3/1/2023		Onion	2
60	12	3/1/2023		Onion	-2
61	13	3/1/2023		Banana	1
62	13	3/1/2023		Banana	-1
63	1	3/2/2023		Grapes	-2
64	1	3/2/2023		Grapes	2
65	2	3/2/2023		Olives	3
66	2	3/2/2023		Olives	-3
67	1
68	1
Sheet1

Cell Formulas
Range	Formula
A3:A68	A3	=IF(B2<>B3,1,IF(SUMIFS($E$2:$E2,$D$2:$D2,D3)=0,MAXIFS($A$2:$A2,$B$2:$B2,">="&B3)+1,XLOOKUP(D3,$D$2:$D2,$A$2:$A2,1/0,0,-1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:E66	Expression	=MOD(SUM(--($A$2:$A2<>$A$3:$A3)),2)	text	NO

I noticed that you also mentioned the possibility that a transaction might not be concluded until the next day. This would be problematic. Consider if transaction 1, onions, did not finish on 3/1, then you started 3/2 with another transaction, olives, which is assigned 1, then finished onions. You can't reuse 1, things would get very messy. I've got some ideas, but let me know what you think.

Also, please look into getting the xl2bb add-in. It's what I used to display my suggestions. It makes it MUCH easier to work on your problem, since I can copy the data directly without having to manually retype everything. See the link in my signature or the reply box.

 

[turn7or5]

Thank you for your quick response. The formula works

I set aside the issue of transactions open beyond same day. It appears to be a very rare occurance and one of little consequence. A manual adjustment of the stray bit's date/time to the day of initiation will probably be okay. Should conditions change I would be glad to hear your ideas.

I will look at the tool you recommend although there are limitations with adding software to this machine. I'll certainly do what i can as this spreadsheet may be more complex than anticipated.