macro to match partial item in list across sheet and populate amounts.

[Abdo]

Hi experts,
maybe this way see some members is strange or not logic about calculation , but I need this way .
my idea will match partial item in list in SUMMARY sheet with the same partial item across sheets , if the same partial of the item is existed in columns DEBIT , CREDIT . first of all will merge amount for DEBIT or CREDIT column in the same sheet , second should sum DEBIT,CREDIT columns together
example : in summary sheet will match PUR PURCHASE across sheets .
in CSMN PURCHASE will be after sum DEBIT=112,800 and in CSMT PURCHASE sheet will be after sum CREDIT =66000 so the final result as show in summary sheet will be = 112800+66000=178800 and so on for the others items also I will add new sheets before summary sheet with the same structure and the same idea .
every time I will add new items in column B for summary sheet .

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	PUR PURCHASE INV 000011	20,000.00
3	02/05/2023	PUR PURCHASE INV 000012	20,000.00
4	03/05/2023	PUR PURCHASE INV 000013	20,000.00
5	04/05/2023	PUR PURCHASE INV 000014	20,000.00
6	05/05/2023	PUR PURCHASE INV 000015	20,000.00
7	06/05/2023	PA PAYING 789		1,000.00
8	07/05/2023	PA PAYING 790		1,000.00
9	08/05/2023	PUR PURCHASE INV 000016	2,000.00
10	09/05/2023	PUR PURCHASE INV 000017	1,200.00
11	10/05/2023	PUR PURCHASE INV 000018	1,300.00
12	11/05/2023	PUR PURCHASE INV 000019	1,400.00
13	12/05/2023	PUR PURCHASE INV 000020	1,000.00
14	13/05/2023	PUR PURCHASE INV 000021	1,200.00
15	14/05/2023	PUR PURCHASE INV 000022	1,200.00
16	15/05/2023	PUR PURCHASE INV 000023	1,200.00
17	16/05/2023	PUR PURCHASE INV 000024	1,300.00
18	17/05/2023	PUR PURCHASE INV 000025	1,000.00
CSMN PURCHASE

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	PUR PURCHASE INV 0000111		2,000.00
3	02/05/2023	PUR PURCHASE INV 0000112		3,000.00
4	03/05/2023	PUR PURCHASE INV 0000113		4,000.00
5	04/05/2023	PUR PURCHASE INV 0000114		5,000.00
6	05/05/2023	PUR PURCHASE INV 0000115		6,000.00
7	06/05/2023	PUR PURCHASE INV 0000116		7,000.00
8	07/05/2023	PUR PURCHASE INV 0000117		8,000.00
9	08/05/2023	PUR PURCHASE INV 0000118		9,000.00
10	09/05/2023	PA PAYING 791	300.00
11	10/05/2023	PA PAYING 792	400.00
12	11/05/2023	PA PAYING 793	500.00
13	12/05/2023	PA PAYING 794	340.00
14	13/05/2023	PUR PURCHASE INV 0000119		3,000.00
15	14/05/2023	PUR PURCHASE INV 0000120		4,000.00
16	15/05/2023	PUR PURCHASE INV 0000121		5,000.00
17	16/05/2023	PUR PURCHASE INV 0000122		6,000.00
18	17/05/2023	PUR PURCHASE INV 0000123		4,000.00
CSMT PURCHASE

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/05/2023	VC VOUCHER PAY 10001		1,200.00
3	02/05/2023	VC VOUCHER PAY 10002		1,800.00
4	03/05/2023	VC VOUCHER PAY 10003		1,200.00
5	04/05/2023	VC VOUCHER PAY 10004		1,300.00
6	05/05/2023	VC VOUCHER PAY 10005		1,400.00
7	06/05/2023	VC VOUCHER PAY 10006		1,500.00
8	07/05/2023	VC VOUCHER PAY 10007		1,700.00
9	08/05/2023	VC VOUCHER PAY 10008
10	09/05/2023	VC VOUCHER RECEIVED 10009	2,000.00
11	10/05/2023	VC VOUCHER RECEIVED 10010	3,000.00
12	11/05/2023	VC VOUCHER PAY 10011		600.00
13	12/05/2023	VC VOUCHER PAY 10012		700.00
14	13/05/2023	VC VOUCHER PAY 10013		600.00
CSMN CASH

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/06/2023	VC VOUCHER RECEIVED V 1000		2,000.00
3	02/06/2023	VC VOUCHER RECEIVED V 1001		3,000.00
4	03/06/2023	VC VOUCHER RECEIVED V 1002		4,000.00
5	04/06/2023	VC VOUCHER RECEIVED V 1003		3,000.00
6	05/06/2023	VC VOUCHER PAY 10014	2,000.00
7	06/06/2023	VC VOUCHER PAY 10015	3,000.00
8	07/06/2023	VC VOUCHER PAY 10016	1,200.00
9	08/06/2023	VC VOUCHER PAY 10017	1,000.00
10	09/06/2023	VC VOUCHER PAY 10018	1,200.00
11	10/06/2023	VC VOUCHER PAY 10019	1,300.00
12	11/06/2023	VC VOUCHER RECEIVED V 1004		200.00
13	12/06/2023	VC VOUCHER RECEIVED V 1005		225.00
14	13/06/2023	VC VOUCHER RECEIVED V 1006		300.00
15	14/06/2023	VC VOUCHER RECEIVED V 1007		400.00
16	15/06/2023	VC VOUCHER RECEIVED V 1008		500.00
17	16/06/2023	VC VOUCHER RECEIVED V 1009		600.00
18	17/06/2023	VC VOUCHER RECEIVED V 1010		700.00
SRTRYUH CASH

REF.xlsm
	A	B	C	D
1	DATE	ACCOUNT REF	DEBIT	CREDIT
2	01/06/2023	SAL SALES SR0001		4,000.00
3	02/06/2023	SAL SALES SR0002		8,000.00
4	03/06/2023	SAL SALES SR0003		3,000.00
5	04/06/2023	SAL SALES SR0004		2,000.00
6	05/06/2023	SAL SALES SR0005		1,000.00
7	06/06/2023	SAL SALES SR0006		1,200.00
8	07/06/2023	SAL SALES SR0007		5,000.00
9	08/06/2023	RE SALES SR0008	2,000.00
10	09/06/2023	RE SALES SR0009	1,200.00
11	10/06/2023	RE SALES SR0010	1,300.00
12	11/06/2023	SAL SALES SR00011		4,000.00
13	12/06/2023	SAL SALES SR00012		1,200.00
14	13/06/2023	SAL SALES SR00013		2,000.00
15	14/06/2023	SAL SALES SR00014		2,000.00
16	15/06/2023	SAL SALES SR00015		2,000.00
17	16/06/2023	SAL SALES SR00016		2,000.00
18	17/06/2023	SAL SALES SR00017		2,000.00
SRTRYUH SR SALE

REF.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE
3	2	PA PAYING
4	3	VC VOUCHER PAY
5	4	VC VOUCHER RECEIVED
6	5	SAL SALES
7	6	RE SALES
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

OUTPUT should be

REF.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE	178,800.00
3	2	PA PAYING	3,540.00
4	3	VC VOUCHER PAY	21,700.00
5	4	VC VOUCHER RECEIVED	19,925.00
6	5	SAL SALES	39,400.00
7	6	RE SALES	4,500.00
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

thanks

 

[kevin9999]

You could achieve your objective with some simple sumif formulas. Using the sample you posted at Excel Forum: Populate amounts for separated range based on matching headers across sheets for partial for demonstration purposes:

REF1.xlsm
	B	C
1		ACCOUNT REF
2		PUR PURCHASE
3	ITEM	CREDIT
4	1	178,800.00
5	TOTAL	178,800.00
summary

Cell Formulas
Range	Formula
C4	C4	=(SUMIF('CSMN PURCHASE'!B:B,C2&"*",'CSMN PURCHASE'!D:D)+SUMIF('CSMT PURCHASE'!B:B,C2&"*",'CSMT PURCHASE'!D:D))-(SUMIF('CSMN PURCHASE'!B:B,C2&"*",'CSMN PURCHASE'!C:C)+SUMIF('CSMT PURCHASE'!B:B,C2&"*",'CSMT PURCHASE'!C:C))
C5	C5	=C4

 

[Abdo]

you misread ,two threads and different requests.

in other forum there is separated ranges and will link separated range each of other .

in here I want the list will link in another list and another sheet.

so why answer Q in other forum and Q here is different?!!

two both threads I have different goals , I don't have to explain the whole and real my project, just I ask part of my request .
by the way I ask here macro because I have big data , just I gave sample .

 

[kevin9999]

OK, using your XL2BB posts on this thread, I still wouldn't be using macros when simple formulas will give you the same result. It doesn't matter how big the data is, the formulas reference entire columns. Just reference the relevant sheets within the formulas.
If you're set on doing this via VBA, I'll leave it to someone else.

Book1
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE	178,800.00
3	2	PA PAYING	3,540.00
4	3	VC VOUCHER PAY	21,700.00
5	4	VC VOUCHER RECEIVED	19,925.00
6	5	SAL SALES	39,400.00
7	6	RE SALES	4,500.00
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

Cell Formulas
Range	Formula
C2:C3	C2	=(SUMIF('CSMN PURCHASE'!B:B,B2&"*",'CSMN PURCHASE'!D:D)+SUMIF('CSMT PURCHASE'!B:B,B2&"*",'CSMT PURCHASE'!D:D))+(SUMIF('CSMN PURCHASE'!B:B,B2&"*",'CSMN PURCHASE'!C:C)+SUMIF('CSMT PURCHASE'!B:B,B2&"*",'CSMT PURCHASE'!C:C))
C4:C5	C4	=(SUMIF('CSMN CASH'!B:B,B4&"*",'CSMN CASH'!D:D)+SUMIF('SRTRYUH CASH'!B:B,B4&"*",'SRTRYUH CASH'!D:D))+(SUMIF('CSMN CASH'!B:B,B4&"*",'CSMN CASH'!C:C)+SUMIF('SRTRYUH CASH'!B:B,B4&"*",'SRTRYUH CASH'!C:C))
C6:C7	C6	=(SUMIF('SRTRTYUH SR SALE'!B:B,B6&"*",'SRTRTYUH SR SALE'!C:C)+SUMIF('SRTRTYUH SR SALE'!B:B,B6&"*",'SRTRTYUH SR SALE'!D:D))

 

[Abdo]

It doesn't matter how big the data is, the formulas reference entire columns

but could effect when search for entire column and most of cells are empty .

theses formulas need modifying every time when add new sheets before summary sheet as I said

also I will add new sheets before summary sheet with the same structure and the same idea .

that's why I want vba
ok you can't help with vba even if that thank you.

 

[Abdo]

by the way there is problem in your last formula it will open dialog to select file and select sheet and then will shows error #value!.

 

[Akuini]

@Abdo
Try:

Sub abdo_1()
Dim i As Long, j As Long, n As Long
Dim c As Range
Dim va, vb, vs, wsn, ary

ary = Split("CSMN PURCHASE/CSMT PURCHASE/CSMN CASH/SRTRYUH CASH/SRTRYUH SR SALE", "/")
Sheets("Summary").Activate

va = Range("B2", Cells(Rows.Count, "B").End(xlUp))
ReDim vs(1 To UBound(va), 1 To 1)

For i = 1 To UBound(va, 1)
    For Each wsn In ary
        With Sheets(wsn)
        Set c = .Range("B:B").Find(What:=x, LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
                n = .Range("B" & Rows.Count).End(xlUp).Row
                
                vb = .Range("B2:B" & n).Resize(, 3)
                
                For j = 1 To UBound(vb, 1)
                    If InStr(1, vb(j, 1), va(i, 1), vbTextCompare) > 0 Then
                    
                        vs(i, 1) = vs(i, 1) + vb(j, 2) + vb(j, 3)
                        'Debug.Print vb(j, 1) & " : " & i & " : " & vs(i, 1)
                    End If
                Next
        End If
        End With
    
    Next

Next

Range("C2").Resize(UBound(vs, 1), 1) = vs
End Sub

but I got different result in C2:C3, not sure why:

abdo - macro to match partial item `.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE	132000
3	2	PA PAYING	3080
4	3	VC VOUCHER PAY	21700
5	4	VC VOUCHER RECEIVED	19925
6	5	SAL SALES	39400
7	6	RE SALES	4500
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
Summary

you can uncomment this line:

'Debug.Print vb(j, 1) & " : " & i & " : " & vs(i, 1)

to see each iteration result in Immediate Window.

 

[Abdo]

@Akuini
well done !

but I got different result in C2:C3, not sure why:

really strange!
I have got the same result as in OP.

REF.xlsm
	A	B	C
1	ITEM	ACCOUNT REF	BALANCE
2	1	PUR PURCHASE	178,800.00
3	2	PA PAYING	3,540.00
4	3	VC VOUCHER PAY	21,700.00
5	4	VC VOUCHER RECEIVED	19,925.00
6	5	SAL SALES	39,400.00
7	6	RE SALES	4,500.00
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	CALCULATION
summary

I have a question about array for sheets in this line

ary = Split("CSMN PURCHASE/CSMT PURCHASE/CSMN CASH/SRTRYUH CASH/SRTRYUH SR SALE", "/")

is there better way than specify sheets names ?
as I said will add new sheets before summary sheet , then if I'm not wrong will I have to write new sheets in array line every time add new sheet.
thank you for your time .

 

[Akuini]

is there better way than specify sheets names ?
as I said will add new sheets before summary sheet , then if I'm not wrong will I have to write new sheets in array line every time add new sheet.

Do you mean you want to execute the code in all sheets except sheet Summary?
Try:

Sub abdo_2()
Dim i As Long, j As Long, n As Long
Dim c As Range
Dim va, vb, vs
Dim wsn As Worksheet


Sheets("Summary").Activate

va = Range("B2", Cells(Rows.Count, "B").End(xlUp))
ReDim vs(1 To UBound(va), 1 To 1)

For i = 1 To UBound(va, 1)
    For Each wsn In Worksheets
        If wsn.Name <> "Summary" Then
            With wsn
            Set c = .Range("B:B").Find(What:=x, LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                If Not c Is Nothing Then
                        n = .Range("B" & Rows.Count).End(xlUp).Row
                        
                        vb = .Range("B2:B" & n).Resize(, 3)
                        
                        For j = 1 To UBound(vb, 1)
                            If InStr(1, vb(j, 1), va(i, 1), vbTextCompare) > 0 Then
                            
                                vs(i, 1) = vs(i, 1) + vb(j, 2) + vb(j, 3)
                                'Debug.Print vb(j, 1) & " : " & i & " : " & vs(i, 1)
                            End If
                        Next
                End If
            End With
        End If
    
    Next

Next

Range("C2").Resize(UBound(vs, 1), 1) = vs
End Sub

really strange!
I have got the same result as in OP

I probably copy-paste wrong data.

 

[Abdo]

yes , Akuini this is what I want it.
thank you so much for your help .