Populate column A based on data in Column B

[monkeyharris]

I wasn't sure what the title should be so please bear with me. I have a system generated report which i need to change in Excel to something useful, but can't find how to do it.

When the report is first opened, the only data shown is what's in B to I. I add column A to manually get what i need.

As you can see, every job un the account column shows the users sales. I added column a and put the users code and the account code they sold products to. Is there a way of automating this? It will always be the same format and users range from 1 to 30. 1=KUL, 2=RAM, 6=Kev etc etc.

I just need to make a report that sums every line by user and can't think of another way to do it.

Test1.xlsx
	A	B	C	D	E	F	G	H	I
1	User	Account	Name	Invoice	Invoice Date	Invoice Total	Payment Amount	Currency Code	Rate
2		2 - Kul
3	2-CASHSALE	CASHSALE	CASH SALE	511337	10/12/2024	3,380.84	3,380.84	STG	1.00
4	2-PLAN	PLAN	P & S	510612	02/12/2024	159.52	159.52	STG	1.00
5	2-PLAN	PLAN	P & S	510811	04/12/2024	281.81	268.47	STG	1.00
6	2-SING	SING	S & I	511104	06/12/2024	105.60	0.00	STG	1.00
7		3 - Ram
8	3-C.O.D	C.O.D	CASH ON DELIVERY (C.O.D.)	510716	03/12/2024	1,878.84	0.00	STG	1.00
9	3-RAMJOHAL	RAMJOHAL	RAM	510943	05/12/2024	23.04	0.00	STG	1.00
10		6 - Kev
11	6-ANRS	ANRS	ANDREW C	512048	18/12/2024	306.00	0.00	STG	1.00
12	6-C.O.D	C.O.D	CASH ON DELIVERY (C.O.D.)	511434	11/12/2024	22.79	22.79	STG	1.00
13	6-CASHSALE	CASHSALE	CASH SALE	510617	02/12/2024	86.94	86.94	STG	1.00
14	6-CASHSALE	CASHSALE	CASH SALE	511312	10/12/2024	78.00	78.00	STG	1.00
15	6-CASHSALE	CASHSALE	CASH SALE	511314	10/12/2024	16.01	16.01	STG	1.00
16	6-CASHSALE	CASHSALE	CASH SALE	512042	18/12/2024	273.60	273.60	STG	1.00
17	6-CASHSALE	CASHSALE	CASH SALE	512046	18/12/2024	41.94	41.94	STG	1.00
18	6-CASHSALE	CASHSALE	CASH SALE	512055	18/12/2024	34.78	34.78	STG	1.00
19	6-CASHSALE	CASHSALE	CASH SALE	512062	18/12/2024	57.60	57.60	STG	1.00
20	6-CASHSALE	CASHSALE	CASH SALE	512112	19/12/2024	11.76	11.76	STG	1.00
21	6-CASHSALE	CASHSALE	CASH SALE	512117	19/12/2024	79.16	79.16	STG	1.00
22	6-CASHSALE	CASHSALE	CASH SALE	C512045	18/12/2024	-41.94	-41.94	STG	1.00
23	6-CCR029	CCR029	CRISTEL	510873	04/12/2024	273.60	0.00	STG	1.00
24	6-CCR113	CCR113	TANTON C	512051	18/12/2024	14.39	0.00	STG	1.00
25	6-CCR122	CCR122	COLYER B	510672	03/12/2024	121.64	121.64	STG	1.00
26	6-CCR122	CCR122	COLYER B	510725	03/12/2024	49.92	49.92	STG	1.00
27	6-CCR122	CCR122	COLYER B	511015	06/12/2024	31.18	31.18	STG	1.00
28	6-CCR122	CCR122	COLYER B	C510724	03/12/2024	-34.32	-34.32	STG	1.00
29	6-CCR122	CCR122	COLYER B	C511014	06/12/2024	-25.20	-25.20	STG	1.00
30	6-HACO	HACO	HATTON	511243	09/12/2024	2,388.97	0.00	STG	1.00
31	6-HACO	HACO	HATTON	511995	18/12/2024	173.40	0.00	STG	1.00
32	6-RAMJOHAL	RAMJOHAL	RAM	512104	19/12/2024	16.73	0.00	STG	1.00
33	6-RSTD	RSTD	RSP BUILD	C510751	03/12/2024	-46.20	0.00	STG	1.00
34	6-RSTD	RSTD	RSP BUILD	C510752	03/12/2024	-13.20	0.00	STG	1.00
Sheet1

 

[hagia_sofia]

Hello, please test the following if it fits the purpose or not:

=LET(
r,B2:B34,
b,--(IF(ISNUMBER(SEARCH("? - ",r)),TEXTBEFORE(r," - ",1))),
c,SCAN("",b,LAMBDA(x,y,IF(y<>0,y,x+y))),
IF(b=0,c&"-"&r,""))

 

[monkeyharris]

Hello, please test the following if it fits the purpose or not:

=LET(
r,B2:B34,
b,--(IF(ISNUMBER(SEARCH("? - ",r)),TEXTBEFORE(r," - ",1))),
c,SCAN("",b,LAMBDA(x,y,IF(y<>0,y,x+y))),
IF(b=0,c&"-"&r,""))

Hi Hagia,
That's amazing. Now i just need to go over and over it so i understood what you've just done

 

[hagia_sofia]

Many thanks for the feedback. It might even be shortened to:

=LET(
r,B2:B34,
b,--(TEXTBEFORE(r," - ",1,,,0)),
c,SCAN("",b,LAMBDA(x,y,IF(y<>0,y,x+y))),
IF(b=0,c&"-"&r,""))