Merge data for each range based on two words & dates

[abdo meghari]

Hello
I have about four ranges , every range could contain 5000 rows .
the ranges will be in columns A:D and I want merging ranges based on duplicates dates and the same words for duplicates date
so the duplicate date & NOT PAID should merge alone . also the duplicate date & PAID should merge alone . the merging ranges will be based on column B,C
the result should be in columns F:I and should delete ranges(F:I) when run the macro every time before merging .

before

MERGE RANGES.xlsm
	A	B	C	D
1		SR
2	ITEM	DATE	TYPE	TOTAL
3	1	2023/06/10	NOT PAID	7,720.00
4	2	2023/06/10	NOT PAID	2,000.00
5	3	2023/06/10	PAID	6,810.00
6	4	2023/06/10	PAID	4,000.00
7	5	2023/06/11	NOT PAID	7,000.00
8	SUM			27,530.00
9
10		SVR
11	ITEM	DATE	TYPE	TOTAL
12	1	2023/06/10	NOT PAID	5,720.00
13	2	2023/06/10	PAID	14,040.00
14	3	2023/06/11	NOT PAID	1,230.00
15	4	2023/06/11	NOT PAID	12,000.00
16	5	2023/06/12	PAID	14,000.00
17	6	2023/06/12	PAID	1,000.00
18	SUM			47,990.00
19
20
21		SDE
22	ITEM	DATE	TYPE	TOTAL
23	1	2023/06/10	PAID	4,000.00
24	2	2023/06/10	PAID	1,000.00
25	3	2023/06/11	PAID	2,000.00
26	4	2023/06/11	NOT PAID	2,200.00
27	5	2023/06/12	NOT PAID	3,000.00
28	SUM			12,200.00
29
30
31		FGR
32	ITEM	DATE	TYPE	TOTAL
33	1	2023/06/10	PAID	1,200.00
34	2	2023/06/10	PAID	1,300.00
35	3	2023/06/11	NOT PAID	1,100.00
36	4	2023/06/12	NOT PAID	2,200.00
37	5	2023/06/12	NOT PAID	7,000.00
38	SUM			12,800.00
REPORT

Cell Formulas
Range	Formula
D8,D38,D28	D8	=SUM(D3:D7)
D18	D18	=SUM(D12:D17)

after

MERGE RANGES.xlsm
	A	B	C	D	E	F	G	H	I
1		SR					SR
2	ITEM	DATE	TYPE	TOTAL		ITEM	DATE	NOT PAID	PAID
3	1	2023/06/10	NOT PAID	7,720.00		1	2023/06/10	9,720.00	10,810.00
4	2	2023/06/10	NOT PAID	2,000.00		2	2023/06/11	7000	0.00
5	3	2023/06/10	PAID	6,810.00		SUM		16,720.00	10,810.00
6	4	2023/06/10	PAID	4,000.00
7	5	2023/06/11	NOT PAID	7,000.00			SVR
8	SUM			27,530.00		ITEM	DATE	NOT PAID	PAID
9						1	2023/06/10	5,720.00	14,040.00
10		SVR				2	2023/06/11	13,230.00	0.00
11	ITEM	DATE	TYPE	TOTAL		3	2023/06/12	0.00	15,000.00
12	1	2023/06/10	NOT PAID	5,720.00		SUM		18,950.00	29,040.00
13	2	2023/06/10	PAID	14,040.00
14	3	2023/06/11	NOT PAID	1,230.00			SDE
15	4	2023/06/11	NOT PAID	12,000.00		ITEM	DATE	NOT PAID	PAID
16	5	2023/06/12	PAID	14,000.00		1	2023/06/10	0.00	5,000.00
17	6	2023/06/12	PAID	1,000.00		2	2023/06/11	0.00	2,000.00
18	SUM			47,990.00		3	2023/06/11	2,200.00	0.00
19						4	2023/06/12	3,000.00	0.00
20						SUM		5,200.00	7,000.00
21		SDE
22	ITEM	DATE	TYPE	TOTAL			FGR
23	1	2023/06/10	PAID	4,000.00		ITEM	DATE	NOT PAID	PAID
24	2	2023/06/10	PAID	1,000.00		1	2023/06/10	0.00	2,500.00
25	3	2023/06/11	PAID	2,000.00		2	2023/06/11	1,100.00	0.00
26	4	2023/06/11	NOT PAID	2,200.00		3	2023/06/12	9,200.00	0.00
27	5	2023/06/12	NOT PAID	3,000.00		SUM		10,300.00	2,500.00
28	SUM			12,200.00
29
30
31		FGR
32	ITEM	DATE	TYPE	TOTAL
33	1	2023/06/10	PAID	1,200.00
34	2	2023/06/10	PAID	1,300.00
35	3	2023/06/11	NOT PAID	1,100.00
36	4	2023/06/12	NOT PAID	2,200.00
37	5	2023/06/12	NOT PAID	7,000.00
38	SUM			12,800.00
REPORT

Cell Formulas
Range	Formula
H5:I5	H5	=SUM(H3:H4)
D8,D38,D28	D8	=SUM(D3:D7)
H27:I27,H12:I12	H12	=SUM(H9:H11)
D18	D18	=SUM(D12:D17)
H20:I20	H20	=SUM(H16:H19)

 

[number kruncher]

Hi abdo,

You could use a mixture of PowerQuery and formulas to get your result.

let
Source = Excel.CurrentWorkbook(){[Name="SR"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Date"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Item", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Item", "Date"})
in
#"Reordered Columns"

Based on your first Source Table called 'SR', find all unique dates and number them. Then place them in their own table to the right.

Then use a SUMIFS(SR[Total],SR[Date],[@Date],SR[Type],SR_1[[#Headers],[Not Paid]]) to fill in the Not Paid and,
SUMIFS(SR[Total],SR[Date],[@Date],SR[Type],SR_1[[#Headers],[Paid]]) to fill in the Paid values.

As its a Table, add a Total Row.

Rinse and repeat for other Source Tables..

Hope this helps

nkruncher

 

[abdo meghari]

does give the result as I posted?

 

[abdo meghari]

it gives error about SR name by PQ !

 

[Cubist]

Would it be easier just to use Pivot Table?

 

[abdo meghari]

Would it be easier just to use Pivot Table?

actually I don't used to use PT
PQ or vba is acceptable.

 

[Cubist]

actually I don't used to use PT
PQ or vba is acceptable.

It's drag and drop that takes 2 seconds, but ok.

 

[number kruncher]

it gives error about SR name by PQ !

If you want to use Power Query, you can use Ranges (which can be flexible) or Cell Addresses, which may need manually updating to suit.

Change
Source = Excel.CurrentWorkbook(){[Name="SR"]}[Content],

To
Source = Excel.CurrentWorkbook(){[Name="A2:D8"]}[Content],

For you example.

Hope this helps

nk

 

[DanteAmor]

Hi Abdo:

Some details to consider.

1. In your example, for "SDE" you repeated the date.

2. The data must start in row 1.
3. The result formats will be taken from cells A to D formats.
4. The last cell with data in column "A" should say "SUM."

Try this:

Sub Merge_Data()
  Dim ar As Range
  Dim i As Long, j As Long, n As Long, lr As Long
  Dim itm As String
  Dim a As Variant, b As Variant
  Dim dic As Object
  Dim sum_notp As Double, sum_paid As Double
 
  Application.ScreenUpdating = False
 
  Set dic = CreateObject("Scripting.Dictionary")
  Range("F:I").Clear
  lr = Range("A" & Rows.Count).End(3).Row
  a = Range("A1:D" & lr).Value
  ReDim b(1 To UBound(a, 1), 1 To 4)
 
  For i = 2 To UBound(a, 1)
    If a(i, 1) <> "" Then
      If a(i, 2) = "DATE" Then
        itm = a(i - 1, 2)
        j = j + 1
        b(j, 2) = itm
        j = j + 1
        b(j, 1) = "ITEM"
        b(j, 2) = "DATE"
        b(j, 3) = "NOT PAID"
        b(j, 4) = "PAID"
        dic.RemoveAll
        n = 0
        sum_notp = 0
        sum_paid = 0
      Else
        If a(i, 1) = "SUM" Then
          j = j + 1
          b(j, 1) = "SUM"
          b(j, 3) = sum_notp
          b(j, 4) = sum_paid
          j = j + 1
        ElseIf a(i, 2) <> "" Then
          If Not dic.exists(a(i, 2)) Then
            j = j + 1
            dic(a(i, 2)) = j
            n = n + 1
            b(j, 1) = n
            b(j, 2) = a(i, 2)
            b(j, 3) = 0
            b(j, 4) = 0
          End If
          If a(i, 3) = "NOT PAID" Then
            b(j, 3) = b(j, 3) + a(i, 4)
            sum_notp = sum_notp + a(i, 4)
          Else
            b(j, 4) = b(j, 4) + a(i, 4)
            sum_paid = sum_paid + a(i, 4)
          End If
        End If
      End If
    End If
  Next
 
  Range("F1").Resize(UBound(b, 1), UBound(b, 2)).Value = b

  'Format cells
  With Range("F:I")
    .Font.Name = Range("B1").Font.Name
    .Font.Size = Range("B1").Font.Size
    .EntireColumn.AutoFit
    .HorizontalAlignment = xlCenter
  End With
  Range("H:I").NumberFormat = Range("D3").NumberFormat
  Range("G:G").NumberFormat = Range("B3").NumberFormat
 
  For Each ar In Range("G1:G" & lr).SpecialCells(xlCellTypeConstants).Areas
   
    With ar.Cells(1)
      With .Borders
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = -0.499984740745262
      End With
      .Font.Color = Range("B1").Font.Color
      .Interior.Color = Range("B1").Interior.Color
    End With
   
    With ar.Offset(1, -1).Resize(1, 4)
      .Font.Color = Range("B1").Font.Color
      .Interior.Color = Range("B1").Interior.Color
    End With
   
    With ar.Offset(1, -1).Resize(ar.Rows.Count, 4).Borders
      .LineStyle = xlContinuous
      .ThemeColor = 1
      .TintAndShade = -0.499984740745262
    End With
    ar.Offset(ar.Rows.Count, -1).Resize(1).Interior.Color = Range("A" & lr).Interior.Color
  Next
 
  Application.ScreenUpdating = True
End Sub

Regards
Dante Amor

 

[abdo meghari]

@number kruncher
thanks gain , but not really sure why show me error about DATE column and inform me is not found despite of I make sure the same word is existed !!!