populate amounts for adjacent cell for each account name and add difference amount

[Omar M]

Hello
here is data in COLLECTION sheet

ACCOUNTS DAILY (1).xlsm
	A	B	C	D	E	F	G
1	ITEM	ACCOUNT NAME	DEBIT TOTAL	CREDIT TOTAL	FIRST BALANCES	DEBIT BALANCES	CREDIT BALANCES
2	1	BANK			10,000.00	8,500.00
3	2	SAFE			100,000.00	16,000.00
4	3	PURCHASE				50,000.00
5	4	SELLING					18,000.00
6	5	CAPITAL			1,000,000.00		14,000.00
7	6	SELLING RETURNING				1,000.00
8	7	PURCHASE RETURNING					30,000.00
9	7	CREDITOR			500,000.00		10,000.00
10	8	INVENTORY OF FIRST DURETION			200,000.00	100,000.00
11	9	LAND			120,000.00
12	10	BUILDING			13,000.00
13	11	DEBTOR			20,000.00
14	12	Discount allowed
15	13	Earned discount
16	14	INVENTORY OF ENDINDG DURETION					150,000.00
17	15	ELECT EXPENSES				50,000.00
18	16	STATIONA EXPENSES				30,000.00
19	17	AD EXPENSES				15,000.00
20	18	INSURE EXPENSES				30,000.00
COLLECTION

here should show result in this sheet

ACCOUNTS DAILY (1).xlsm
	A	B	C	D
3	DEBIT	ACCOUNT NAME	CREDIT	ACCOUNT NAME
4		PURCHASE		SELLING
5		SELLING RETURNING		PURCHASE RETURNING
6		INVENTORY OF FIRST DURETION		INVENTORY OF ENDINDG DURETION
7
8
9
SHOULD IN COLLECTION SHEET

then should match ACCOUNT NAME in column B with account name in column B for COLLECTION sheet and populate amount in column A for adjacent cell contains account name in column B by brings amount from COLLECTION in column F, and populate amount in column C for adjacent cell contains account name in column D by brings amount from COLLECTION in column G. and add difference amount (PROFIT) in column B if the TOTAL column C bigger than TOTAL column A after that add TOTAL word to sum columns A,C and copy amount for adjacent PROFIT cell to column C for adjacent cell (BALANCE) ,but add difference amount (LOSS ) in column C if the TOTAL column C smaller than TOTAL column A after that add TOTAL word to sum columns A,C and copy amount for adjacent LOSS cell to column A for adjacent cell (BALANCE)
the result should be

ACCOUNTS DAILY (1).xlsm
	A	B	C	D
3	DEBIT	ACCOUNT NAME	CREDIT	ACCOUNT NAME
4	50,000.00	PURCHASE	18,000.00	SELLING
5	1,000.00	SELLING RETURNING	30,000.00	PURCHASE RETURNING
6	100,000.00	INVENTORY OF FIRST DURETION	150,000.00	INVENTORY OF ENDINDG DURETION
7	47,000.00	PROFIT
8	198,000.00	TOTAL	198,000.00
9		BALANCE	47,000.00
SHOULD IN COLLECTION SHEET

Cell Formulas
Range	Formula
C8	C8	=SUM(C4:C7)

I hope this clear and I whish DanteAmore help me again.

 

[DanteAmor]

Is this what you need:

varios 16ago2024.xlsm
	A	B	C	D
1
2
3	DEBIT	ACCOUNT NAME	CREDIT	ACCOUNT NAME
4	50,000.00	PURCHASE	18,000.00	SELLING
5	1,000.00	SELLING RETURNING	30,000.00	PURCHASE RETURNING
6	100,000.00	INVENTORY OF FIRST DURETION	150,000.00	INVENTORY OF ENDINDG DURETION
7	47,000.00	PROFIT
8	198,000.00	TOTAL	198,000.00
9		BALANCE	47,000.00
10
SHOULD

Cell Formulas
Range	Formula
B7	B7	=IF(SUM(C4:C6)>SUM(A4:A6),"PROFIT","LOSS")
A4:A6	A4	=VLOOKUP(B4,COLLECTION!B:F,5,0)
A7	A7	=IF(B7="PROFIT",SUM(C4:C6)-SUM(A4:A6),"")
A8,C8	A8	=SUM(A4:A7)
A9	A9	=C7
C4:C6	C4	=VLOOKUP(D4,COLLECTION!B:G,6,0)
C7	C7	=IF(B7="LOSS",SUM(A4:A6)-SUM(C4:C6),"")
C9	C9	=A7

 

[Omar M]

thank again
I would add words(PROFIT,LOSS,BALANCE) automatically because sometimes adds new accounts names in columns B,D then will change words(PROFIT,LOSS,BALANCE) in location (other meaning not always in the same location for rows).
note: when there is LOSS word then will be in A9=C7 , not C9(because TOTAL amounts for DEBIT bigger than TOTAL amounts for CREDIT and in this case will show amount difference in credit column and then will copy to column A for adjacent cell(BALANCE).

 

[DanteAmor]

I don't quite understand what you need, but if in your example you increase the debits so that they are greater than the credits, you will see that the word changes to "LOSS".
Try...

 

[Omar M]

sorry I was rushing !
this is exactly what I want it.
thank you so much.