Keep specific data for current year

Panoos64 · Dec 16, 2014

Hi all, I would like to write a VBA code so that when i run it to sort or sums the amounts in the col. "F" (credit side) for current year only combine with col. "B" and where there is transaction type "REC" or "CRN" or "COR". Any other rows should be deleted. In addition the debtor name should turn to one row below so that to be in the same line with amount. Below is an extract how are the data In F.1. when i download them and in F.2. is the expected result. I would be greatly appreciated any help. Thanking you in advance.



F.1.

	A	B	C	D	E	F
1
2						16/12/2014
3
4	Debtor Name	Dare	Reference	Description	Debit	Credit
5
6	Company ABCD
7		06/07/12	INV - 401254	INVOICE	965,00	0,00
8		11/10/13	INV - 401320	INVOICE	2.555,00	0,00
9		04/11/13	INV - 412011	INVOICE	1.420,00	0,00
10		20/12/13	CRN - 501114	CREDIT NOTE	0,00	245,00
11		12/05/14	COR - 301211	Fees	0,00	450,00
12		15/05/14	INV - 422051	INVOICE	2.870,00	0,00
13		23/05/14	CRN - 491012	CREDIT NOTE	0,00	88,00
14		26/09/14	REC - 110912	RECEIPT	0,00	1.150,00
15	Company EFGH
16		25/05/12	INV - 401102	INVOICE	250,00	0,00
17		05/03/14	REC - 101001	CASH RECEIPT	0,00	1.850,00
18		30/04/14	REC - 101220	CASH RECEIPT	0,00	2.100,00
19		13/10/14	CRN - 501012	CREDIT NOTE	0,00	355,00
20		27/06/14	INV - 511212	INVOICE	2.500,00	0,00

F.2.

	A	B	C	D	E	F
1
2						16/12/2014
3
4	Debtor Name	Dare	Reference	Description	Debit	Credit
5
6
7	Company ABCD	12/05/14	COR - 301211	CORRECTION	0,00	1.688,00
8
9	Company EFGH	05/03/14	REC - 101001	CASH RECEIPT	0,00	4.305,00

onlyadrafter · Dec 19, 2014

Hello (again), you should just 'bump' your original post, not start a new one

http://www.mrexcel.com/forum/excel-questions/823377-sum-rows-ageing.html

Have had another go, almost get the same answer (again) as your original post, but think your result should have included the original row 10, as the data is 20/12/13 which is less than a year from your date in F2 (16/12/14).

not very elegant again, as I amended the original post code, but if it is what you expect then can have a look at improving it.

Code:

Sub COMBINE()
    Application.ScreenUpdating = False
    MY_DATE = Range("F2").Value - 365
    For MY_ROWS = Range("B" & Rows.Count).End(xlUp).Row To 6 Step -1
            If Left(Range("C" & MY_ROWS).Value, 3) <> "COR" And _
                Left(Range("C" & MY_ROWS).Value, 3) <> "CRN" And _
                Left(Range("C" & MY_ROWS).Value, 3) <> "REC" And _
                Not (IsEmpty(Range("B" & MY_ROWS).Value)) Or _
                Range("B" & MY_ROWS).Value < MY_DATE And _
                Not (IsEmpty(Range("B" & MY_ROWS).Value)) Then
                    Rows(MY_ROWS).Delete
            End If
    Next MY_ROWS
    For MY_ROWS = Range("B" & Rows.Count).End(xlUp).Row To 6 Step -1
        If Range("B" & MY_ROWS).Value > MY_DATE And Not (IsEmpty(Range("B" & MY_ROWS - 1).Value)) Then
            If Left(Range("C" & MY_ROWS).Value, 3) = "COR" Or _
                Left(Range("C" & MY_ROWS).Value, 3) = "CRN" Or _
                Left(Range("C" & MY_ROWS).Value, 3) = "REC" Then
                    MY_TOTAL = MY_TOTAL + Range("F" & MY_ROWS).Value
                    Rows(MY_ROWS).Delete
            Else
                Rows(MY_ROWS).Delete
                GoTo CONT
            End If
        Else
            If IsEmpty(Range("B" & MY_ROWS - 1).Value) Then
                Range("B" & MY_ROWS & ":F" & MY_ROWS).Copy
                Range("B" & MY_ROWS - 1).PasteSpecial (xlPasteAll)
                MY_TOTAL = MY_TOTAL + Range("F" & MY_ROWS).Value
                Range("F" & MY_ROWS - 1).Value = MY_TOTAL
                Rows(MY_ROWS).Delete
                MY_ROWS = MY_ROWS - 1
                MY_TOTAL = 0
                GoTo CONT
            End If
        End If
CONT:
    Next MY_ROWS
    Application.ScreenUpdating = True
End Sub

Panoos64 · Dec 21, 2014

Hi onlyadrafter, the above code is right and perfect and it is issue my expected result. I am apologizing for my design which is not so elegant and any discomfort i caused to you. I was trying to correct it but i couldn't managed. However is great job by you. Thanks once again and i wish you all the best!

Panoos64 · Dec 25, 2014

Hi again, when i was testing for several times and trying to issue my reports, i found that is not correct. It sums all CRN, REC, and COR for all years. In order to be correct, should some only the amounts which are belong to the current year which is relating to the date in "F2" e.g. 2014. Any other transaction which is not belong to the current year should be deleted. Thank you again and i am apologizing for my above answer by which i wrote that was right. Thanks for your support.

Panoos64 · Dec 28, 2014

Hi all, i will appreciated any help to the above. Thanks

Keep specific data for current year

Panoos64

Well-known Member

Excel Facts

onlyadrafter

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Keep specific data for current year

Panoos64

Well-known Member

Excel Facts

onlyadrafter

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock