Populate headers based on column and merge amount for multiple words

[Omar M]

Hello
I want to make macro in columns I:N based on cells I2,K2
so if I2,K2 are empty then should populate headers from I:M and insert column OUT in column M to sum column J,L,M based on items are existed in column D.
in column I will brings items from column E as to rest of columns will merge amounts in column F and put under headers for each item in column I
I want macro eal with 11000 rows from columns A:F.
example :

CVF ‫‬.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL			FROM DATE		TO DATE
2	1	01/11/2023	SSLL-001	PAID	SL	60,147.00
3	2	04/11/2023	SSLL-002	NOT PAID	SL	9,504.00
4	3	05/11/2023	SSLL-003	NOT PAID	SL	7,315.00
5	4	06/11/2023	SSLL-004	NOT PAID	SL	53,234.00
6	5	09/11/2023	SSLL-005	NOT PAID	SL	3,706.00
7	6	10/11/2023	SSLL-006	PAID	SL	2,904.00
8	7	12/11/2023	SSLL-007	CASH BY SAFE	SL	15,000.00
9	8	13/11/2023	SSLL-008	CASH BY BANK	SL	32,452.00
10	9	15/01/2023	MMLLL-001	PAID	ML	1,342.00
11	10	15/01/2023	MMLLL-002	PAID	ML	2,082.00
12	11	16/01/2023	MMLLL-003	NOT PAID	ML	3,798.00
13	12	06/11/2023	MMLLL-004	CASH BY BANK	ML	3,200.00
14	13	07/11/2023	MMLLL-005	CASH BY SAFE	ML	1,020.00
15	14	17/11/2023	TTLL-001	CASH BY BANK	TL	12,540.00
16	15	17/11/2023	TTLL-002	NOT PAID	TL	2,900.00
17	16	18/11/2023	TTLL-003	NOT PAID	TL	1,500.00
18	17	18/11/2023	TTLL-004	PAID	TL	2,816.00
19	18	18/11/2023	TTLL-005	PAID	TL	15,000.00
20	19	18/11/2023	TTLL-006	CASH BY SAFE	TL	9,450.00
OUT

result

CVF ‫‬.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL			FROM DATE		TO DATE
2	1	01/11/2023	SSLL-001	PAID	SL	60,147.00
3	2	04/11/2023	SSLL-002	NOT PAID	SL	9,504.00
4	3	05/11/2023	SSLL-003	NOT PAID	SL	7,315.00			INVOICE TYP	PAID	NOT PAID	CASH BY SAFE	CASH BY BANK	OUT
5	4	06/11/2023	SSLL-004	NOT PAID	SL	53,234.00			SL	63,501.00	73,759.00	15,000.00	32,452.00	110,953.00
6	5	09/11/2023	SSLL-005	NOT PAID	SL	3,706.00			ML	3,424.00	3,798.00	1,020.00	3,200.00	7,644.00
7	6	10/11/2023	SSLL-006	PAID	SL	2,904.00			TL	17,816.00	4,400.00	9,450.00	12,540.00	39,806.00
8	7	12/11/2023	SSLL-007	CASH BY SAFE	SL	15,000.00			TOTAL	42,261.00	81,957.00	4,530.00	16,712.00	63,503.00
9	8	13/11/2023	SSLL-008	CASH BY BANK	SL	32,452.00
10	9	15/01/2023	MMLLL-001	PAID	ML	1,342.00
11	10	15/01/2023	MMLLL-002	PAID	ML	2,082.00
12	11	16/01/2023	MMLLL-003	NOT PAID	ML	3,798.00
13	12	06/11/2023	MMLLL-004	CASH BY BANK	ML	3,200.00
14	13	07/11/2023	MMLLL-005	CASH BY SAFE	ML	1,020.00
15	14	17/11/2023	TTLL-001	CASH BY BANK	TL	12,540.00
16	15	17/11/2023	TTLL-002	NOT PAID	TL	2,900.00
17	16	18/11/2023	TTLL-003	NOT PAID	TL	1,500.00
18	17	18/11/2023	TTLL-004	PAID	TL	2,816.00
19	18	18/11/2023	TTLL-005	PAID	TL	15,000.00
20	19	18/11/2023	TTLL-006	CASH BY SAFE	TL	9,450.00
OUT

Cell Formulas
Range	Formula
N5:N8	N5	=J5+L5+M5
J8,L8:M8	J8	=J5-J6-J7
K8	K8	=SUM(K5:K7)

and when use dates then should be

CVF ‫‬.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL			FROM DATE		TO DATE
2	1	01/11/2023	SSLL-001	PAID	SL	60,147.00			01/11/2023		17/11/2023
3	2	04/11/2023	SSLL-002	NOT PAID	SL	9,504.00
4	3	05/11/2023	SSLL-003	NOT PAID	SL	7,315.00			INVOICE TYP	PAID	NOT PAID	CASH BY SAFE	CASH BY BANK	OUT
5	4	06/11/2023	SSLL-004	NOT PAID	SL	53,234.00			SL	63,501.00	73,759.00	15,000.00	32,452.00	110,953.00
6	5	09/11/2023	SSLL-005	NOT PAID	SL	3,706.00			ML	3,424.00	3,798.00	1,020.00	3,200.00	7,644.00
7	6	10/11/2023	SSLL-006	PAID	SL	2,904.00			TL	-	2,900.00	-	12,540.00	12,540.00
8	7	12/11/2023	SSLL-007	CASH BY SAFE	SL	15,000.00			TOTAL	60,077.00	80,457.00	13,980.00	16,712.00	90,769.00
9	8	13/11/2023	SSLL-008	CASH BY BANK	SL	32,452.00
10	9	15/01/2023	MMLLL-001	PAID	ML	1,342.00
11	10	15/01/2023	MMLLL-002	PAID	ML	2,082.00
12	11	16/01/2023	MMLLL-003	NOT PAID	ML	3,798.00
13	12	06/11/2023	MMLLL-004	CASH BY BANK	ML	3,200.00
14	13	07/11/2023	MMLLL-005	CASH BY SAFE	ML	1,020.00
15	14	17/11/2023	TTLL-001	CASH BY BANK	TL	12,540.00
16	15	17/11/2023	TTLL-002	NOT PAID	TL	2,900.00
17	16	18/11/2023	TTLL-003	NOT PAID	TL	1,500.00
18	17	18/11/2023	TTLL-004	PAID	TL	2,816.00
19	18	18/11/2023	TTLL-005	PAID	TL	15,000.00
20	19	18/11/2023	TTLL-006	CASH BY SAFE	TL	9,450.00
OUT

Cell Formulas
Range	Formula
N5:N8	N5	=J5+L5+M5
J8,L8:M8	J8	=J5-J6-J7
K8	K8	=SUM(K5:K7)

NOTE: I would solution by vba . I don't want power query at all.

 

[DanteAmor]

I have some doubts:

1. They are always the same INVOICE TYP, they always go in this order: SL, ML, TL

2. If an INVOICE TYP has no values, do you want it to appear with 0 in all columns?

3. The formulas in row 8 (TOTAL) are always the same, so they won't increase the number of INVOICE TYP?

4. They are always the same CONDITION, they always go in this order: PAID, NOT PAID, CASH BY SAFE, CASH BY BANK, OUT

5. If an CONDITION has no values, do you want it to appear with 0 in all rows?

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Omar M]

1. They are always the same INVOICE TYP, they always go in this order: SL, ML, TL

well, there are others INVOICE TYPE , it's not necessary to be the same orders.

2. If an INVOICE TYP has no values, do you want it to appear with 0 in all columns?

I'm not sure what you mean in all columns!
any INVOICE TYP has no values for specific column then should show zero as hyphen.

3. The formulas in row 8 (TOTAL) are always the same, so they won't increase the number of INVOICE TYP?

yes even there are others numbers of INVOICE TYP( it just will be for SL,ML,TL .

4. They are always the same CONDITION, they always go in this order: PAID, NOT PAID, CASH BY SAFE, CASH BY BANK, OUT

will be others conditions and , orders will be who shows first based on original data .

5. If an CONDITION has no values, do you want it to appear with 0 in all rows?

will be zero under header doesn't existed in original data.

 

[alansidman]

Using Power Query, I created a Parameter Query

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL			FROM DATE		TO DATE
2	1	11/1/2023	SSLL-001	PAID	SL	60147			11/1/2023		11/17/2023
3	2	11/4/2023	SSLL-002	NOT PAID	SL	9504
4	3	11/5/2023	SSLL-003	NOT PAID	SL	7315
5	4	11/6/2023	SSLL-004	NOT PAID	SL	53234			INVOICE TYP	PAID	NOT PAID	CASH BY SAFE	CASH BY BANK
6	5	11/9/2023	SSLL-005	NOT PAID	SL	3706			ML			1020	3200
7	6	11/10/2023	SSLL-006	PAID	SL	2904			SL	63051	73759	15000	32452
8	7	11/12/2023	SSLL-007	CASH BY SAFE	SL	15000			TL		2900		12540
9	8	11/13/2023	SSLL-008	CASH BY BANK	SL	32452
10	9	1/15/2023	MMLLL-001	PAID	ML	1342
11	10	1/15/2023	MMLLL-002	PAID	ML	2082
12	11	1/16/2023	MMLLL-003	NOT PAID	ML	3798
13	12	11/6/2023	MMLLL-004	CASH BY BANK	ML	3200
14	13	11/7/2023	MMLLL-005	CASH BY SAFE	ML	1020
15	14	11/17/2023	TTLL-001	CASH BY BANK	TL	12540
16	15	11/17/2023	TTLL-002	NOT PAID	TL	2900
17	16	11/18/2023	TTLL-003	NOT PAID	TL	1500
18	17	11/18/2023	TTLL-004	PAID	TL	2816
19	18	11/18/2023	TTLL-005	PAID	TL	15000
20	19	11/18/2023	TTLL-006	CASH BY SAFE	TL	9450
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"TOTAL", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [DATE] >= Table2 and [DATE] <= Table3),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"CONDITION", "INVOICE TYP"}, {{"totals", each List.Sum([TOTAL]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[CONDITION]), "CONDITION", "totals")
in
    #"Pivoted Column"

Table2 and Table3 are your look up dates

 

[Omar M]

@alansidman
first thank you
second you don't have TOTAL row and OUT column
finally I said

NOTE: I would solution by vba . I don't want power query at all.

I don't like Power Query , I don't want it at all !

 

[DanteAmor]

well, there are others INVOICE TYPE , it's not necessary to be the same orders.

This formula causes me a lot of noise:

=J5-J6-J7

And no matter the order, this is not the same:

63501 - 3424 - 17816 = 42261

Than this:

3424 - 63501 - 17816 = -77893

I hope to explain what my concern is. If there are more INVOICE TYPs, you must explain how they should look and what the formula should be like.
I have no problems with formulas that add (the order of the addends does not alter the sum).

--------------------------------------------------
I still can't help you, since you didn't adequately answer my questions.

1. I need you to clarify how the remaining formulas will look if there are more INVOICE TYPs. (=J5-J6-J7 , =L5-L6-L7, =M5-M6-M7)

2. Are you going to put the headings in columns I to N? or is the macro going to put them?

 

[DanteAmor]

Try the following macro. Put the invoices and the columns.

I warn you: it does not put totals nor does it put the values in the OUT column, that is because you have not defined what the formula is like in case the number of columns increases or the number of rows increases.

Also, what happens if the columns and rows appear arranged in a different order, since they are currently placed as they appear.

Sub Invoice_Typ()
  Dim a As Variant, b As Variant
  Dim dicR As Object, dicC As Object
  Dim i As Long, j As Long, nRow As Long, nCol As Long
  Dim froDate As Long, to_Date As Long
  Dim iniDate As Long, finDate As Long
    
  Set dicR = CreateObject("Scripting.Dictionary")   'Invoice
  Set dicC = CreateObject("Scripting.Dictionary")   'Condition
  Range("I4", Cells(Rows.Count, Columns.Count)).Clear
  
  a = Range("A1", Range("F" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a) + 2, 1 To UBound(a) + 2)
  froDate = Range("I2").Value
  to_Date = Range("K2").Value
  
  dicC("INVOICE TYP") = Empty
  For i = 2 To UBound(a)
  
    If froDate = 0 Then iniDate = a(i, 2) Else iniDate = froDate
    If to_Date = 0 Then finDate = a(i, 2) Else finDate = to_Date
  
    If a(i, 2) >= iniDate And a(i, 2) <= finDate Then
      If Not dicR.exists(a(i, 5)) Then dicR(a(i, 5)) = dicR.Count + 1 'Invoice
      If Not dicC.exists(a(i, 4)) Then dicC(a(i, 4)) = dicC.Count     'Condition
  
      nRow = dicR(a(i, 5))
      nCol = dicC(a(i, 4))
      b(nRow, nCol) = b(nRow, nCol) + a(i, 6)
    End If
  Next
  
  For i = 1 To dicR.Count + 1
    For j = 1 To dicC.Count
      If b(i, j) = "" Then b(i, j) = 0
    Next
  Next
  
  'Headers
  dicC("OUT") = Empty
  dicR("TOTAL") = Empty
  With Range("I4").Resize(1, dicC.Count)
    .Value = dicC.keys
    .Borders.Color = Range("A1").Borders.Color
    .Interior.Color = Range("A1").Interior.Color
    .HorizontalAlignment = xlCenter
    .Font.Bold = True
  End With
  
  'Invoice Typ
  Range("I5").Resize(dicR.Count).Value = Application.Transpose(dicR.keys)
  
  'Values
  Range("J5").Resize(dicR.Count, dicC.Count).Value = b
  With Range("I5").Resize(dicR.Count, dicC.Count)
    .Select
    .Borders.Color = Range("A1").Borders.Color
    .HorizontalAlignment = xlCenter
    .NumberFormat = "#,##0.00;-#,##0.00;-"
    With .Offset(dicR.Count - 1).Cells(1)
      .Font.Bold = True
      .Interior.Color = Range("A1").Interior.Color
    End With
  End With
  
  'Totals
  
End Sub

 

[Omar M]

now I understood my big mistake it will not be the same formula when add new INVOICE TYP ,CONDITION whether in OUT column or TOTAL row, sorry for this confusion. I will create new thread to treat this problem ,I will think idea how should show data.
your code works perfectly .
thank you so much.