Macros to consolidate cells with duplicate values/texts and sumif values that match with consolidated cells.

[Rant]

Hi guys,

My Excel workbook consists of 4 worksheets;
Sheet 1 has date, item, location/address and number of deliveries,
Sheet 2 has date, item, total cost of the item.
Ref sheet consists of lookup values

Can someone please help me with the macros for the 4th worksheet (Sheet 3)
I want to assign macros to a command button in sheet 3; which will extract values from sheet 1 and 2, consolidate the cells with duplicate values from sheet 1(date, item, loc/address), sumif (number of items delivered) and provide me Invoice value for those units based on sheet2 values.

Thank you

Invoice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Sheet 1									Sheet 2							Ref sheet
2	Date	Item	Location	Street	Unit	No. of items delivered				Date	Item	xx	Total price
3	1/01/2022	AA	Loc1	17	25	34				1/01/2022	AA	ww	100				AA
4	1/01/2022	AA	Loc1	17	25	34				1/01/2022	BB	xx	101				BB
5	1/01/2022	AA	Loc1	17	25	34				1/01/2022	CC	yy	102				CC
6	1/01/2022	AA	Loc1	16	26	61				1/01/2022	DD	zz	50
7	1/01/2022	AA	Loc1	16	26	61				2/01/2022	AA	ww	113
8	1/01/2022	CC	Loc1	16	26	92				2/01/2022	BB	xx	114
9	1/01/2022	CC	Loc1	16	26	37				2/01/2022	CC	yy	115
10	1/01/2022	BB	Loc1	16	26	52				2/01/2022	DD	zz	50
11	1/01/2022	BB	Loc1	16	26	86
12	1/01/2022	BB	Loc1	16	26	95
13	2/01/2022	BB	Loc1	16	26	61				Sheet 3
14	2/01/2022	CC	Loc1	16	26	67
15	2/01/2022	CC	Loc1	16	26	65				Date	Item	Location	Street	Unit	Total no. of items delivered	Invoice per unit
16	2/01/2022	CC	Loc2	16	26	69				1/01/2022	AA	Loc1	17	25	101	52.48
17	2/01/2022	CC	Loc2	16	26	65				1/01/2022	AA	Loc1	16	26	123	63.45
18										1/01/2022	BB	Loc1	16	26	233	120.52
19										1/01/2022	CC	Loc1	16	26	129	66.55
20										2/01/2022	BB	Loc1	16	26	61	64.15
21										2/01/2022	CC	Loc1	16	26	132	137.71
22										2/01/2022	CC	Loc2	16	26	134	140.15
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
O16:O22	O16	=SUMIFS($F$3:$F$24,$A$3:$A$24,$J16,$B$3:$B$24,$K16,$C$3:$C$24,$L16,$D$3:$D$24,$M16,$E$3:$E$24,$N16)
P16:P22	P16	=(SUM(SUMIFS($M$3:$M$10,$J$3:$J$10,$J16,$K$3:$K$10,$Q$3:$Q$5)))*($O16/SUMIF($A$3:$A$17,$J16,$F$3:$F$17))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K7:L10	Cell Value	<0	text	NO
M7:M10	Cell Value	<0	text	NO
K3:L6	Cell Value	<0	text	NO
M3:M6	Cell Value	<0	text	NO

 

[DanteAmor]

Assuming data starts in cell A2 on all sheets.
Check the name of the sheets in the macro.


Try this:

Sub ConsolidateCells()
  Dim dic1 As Object, dic2 As Object, dicR As Object, dicD As Object
  Dim a As Variant, b As Variant, c As Variant, d As Variant
  Dim i As Long, j As Long, k As Long
  Dim cad As String
  
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dicR = CreateObject("Scripting.Dictionary")
  Set dicD = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet 1").Range("A2", Sheets("Sheet 1").Range("F" & Rows.Count).End(3)).Value
  b = Sheets("Sheet 2").Range("A2", Sheets("Sheet 2").Range("D" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1), 1 To 7)
  d = Sheets("Ref").Range("A2", Sheets("Ref").Range("A" & Rows.Count).End(3)).Value
  
  For i = 1 To UBound(a, 1)
    dicD(a(i, 1)) = dicD(a(i, 1)) + a(i, 6)   'sum by date
    
    cad = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4) & "|" & a(i, 5)
    If Not dic1.exists(cad) Then
      k = k + 1
      dic1(cad) = k
      For j = 1 To 5
        c(k, j) = a(i, j)
      Next
    End If
    k = dic1(cad)
    c(k, 6) = c(k, 6) + a(i, 6)   'sum by date, item, location, street, unit
  Next
  
  For i = 1 To UBound(d, 1)     'item from ref sheet
    dicR(d(i, 1)) = Empty
  Next
  
  For i = 1 To UBound(b, 1)     'sum by date from sheet 2
    If dicR.exists(b(i, 2)) Then dic2(b(i, 1)) = dic2(b(i, 1)) + b(i, 4)
  Next
  
  For i = 1 To k
    c(i, 7) = dic2(c(i, 1)) * (c(i, 6) / dicD(c(i, 1)))
  Next
  
  Sheets("Sheet 3").Range("A2").Resize(k, UBound(c, 2)).Value = c
End Sub

 

[Rant]

Legend! Awesome! Thank you Dante.

 

[Rant]

The values in the last column seems incorrect, the code return zeros under invoice per unit column.

 

[DanteAmor]

You can put the data with which you are testing and the correct result.