Multiple search criteria for copy and paste value in new sheet

[CapricornDec20]

Hello Guys
Please I am new to excel programming and would like to get started. Need help.
Can anyone help me with it, my boss gave an assignment I need to fill the final data sheet from the raw data sheet, if it have one search criteria for search and match i can use vlookup, but the problem is that its has 3 search criteria, City, store and day of the week. Can any one help me out for creating the excel macro, if data is short i can do it manually, but its a very huge data of 2000+ entries. data file is attached.

 

[hajiali]

try:
cell E20 =INDEX($D$5:$J$16,MATCH(1,INDEX(($A$5:$A$16=A20)*($B$5:$B$16=B20)*($C$5:$C$16=C20),0,1),0),MATCH(D20,$D$4:$J$4,0))

 

[Zot]

Is this the right answer?

Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Month	Cities	Stores	Monday (Sale)	Tuesday (Sale)	Wednesday (Sale)	Thursday (Sale)	Friday (Sale)	Saturday (Sale)	Sunday (Sale)
2	March'21	KHI	1	15	11	20	16	25	21	30
3	March'21	KHI	2	20	16	25	21	30	26	35
4	March'21	KHI	3	25	21	30	26	35	31	40
5	March'21	KHI	4	30	26	35	31	40	36	45
6	March'21	KHI	5	35	31	40	36	45	41	50
7	March'21	LHR	1	40	36	45	41	50	46	55
8	March'21	LHR	2	45	41	50	46	55	51	60
9	March'21	LHR	3	50	46	55	51	60	56	65
10	March'21	ISL	1	55	51	60	56	65	61	70
11	March'21	ISL	2	60	56	65	61	70	66	75
12
13	Month	Channel	Store	Day Sale	Units
14	March'21	KHI	1	Tuesday	11
15	March'21	LHR	2	Wednesday	50
16	March'21	ISL	1	Sunday	70
17	March'21	KHI	4	Monday	30
18	March'21	LHR	3	Tuesday	46
Sheet1

Cell Formulas
Range	Formula
E14:E18	E14	=INDEX(INDEX($D$2:$J$11,1,MATCH(D14&"*",$D$1:$J$1,0)):INDEX($D$2:$J$11,10,MATCH(D14&"*",$D$1:$J$1,0)),MATCH(1,($B$2:$B$11=B14)*($C$2:$C$11=C14),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

=SUMPRODUCT(($B$2:$B$11=B14)*($C$2:$C$11=C14)*(LEFT($D$1:$J$1,LEN(D14))=D14)*($D$2:$J$11))

 

[CapricornDec20]

Another option

=SUMPRODUCT(($B$2:$B$11=B14)*($C$2:$C$11=C14)*(LEFT($D$1:$J$1,LEN(D14))=D14)*($D$2:$J$11))

Thanx Fluff, its work perfectly fine, one thing more how can i write it in vba code for future purposes.

 

[CapricornDec20]

Thanx Fluff, its work perfectly fine, one thing more how can i write it in vba code for future purposes.

it will b a great help, thanx in advance

 

[Fluff]

How about

Sub Capricorn()
   Dim Ary As Variant
   Dim Dic As Object
   Dim r As Long, c As Long
   Dim Txt As String
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Ary = Range("A1").CurrentRegion.Value2
   
   For r = 2 To UBound(Ary)
      Txt = Ary(r, 2) & "|" & Ary(r, 3)
      If Not Dic.exists(Txt) Then Dic.Add Txt, CreateObject("scripting.dictionary")
      For c = 4 To UBound(Ary, 2)
         Dic(Txt)(Ary(1, c)) = Dic(Txt)(Ary(1, c)) + Ary(r, c)
      Next c
   Next r
   
   For Each Cl In Range("E14:E" & Range("B" & Rows.Count).End(xlUp).Row)
      Cl.Value = Dic(Cl.Offset(, -3).Value & "|" & Cl.Offset(, -2).Value)(Cl.Offset(, -1).Value & " (Sale)")
   Next Cl
End Sub

 

[CapricornDec20]

How about

Sub Capricorn()
   Dim Ary As Variant
   Dim Dic As Object
   Dim r As Long, c As Long
   Dim Txt As String
   Dim Cl As Range
  
   Set Dic = CreateObject("scripting.dictionary")
   Ary = Range("A1").CurrentRegion.Value2
  
   For r = 2 To UBound(Ary)
      Txt = Ary(r, 2) & "|" & Ary(r, 3)
      If Not Dic.exists(Txt) Then Dic.Add Txt, CreateObject("scripting.dictionary")
      For c = 4 To UBound(Ary, 2)
         Dic(Txt)(Ary(1, c)) = Dic(Txt)(Ary(1, c)) + Ary(r, c)
      Next c
   Next r
  
   For Each Cl In Range("E14:E" & Range("B" & Rows.Count).End(xlUp).Row)
      Cl.Value = Dic(Cl.Offset(, -3).Value & "|" & Cl.Offset(, -2).Value)(Cl.Offset(, -1).Value & " (Sale)")
   Next Cl
End Sub

You are great man, Thanx a lot, God bless you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CapricornDec20]

You are great man, Thanx a lot, God bless you

Dear I m facing a little issue in it, actually I have two files one file is "Stores Raw Data" and other is "Final Sales Data Row-wise", I want those sales unit in Final Sales Data Row-wise sheet1's last available column, would it b possible, thanx a lot for help