Forum Discussion
Solved
EXCEL FORMULA
Hi, hope Members of 'MICROSOFT TECH COMMUNITY' are well. i have a problem as below PIC-1 ITEM CODE ITEM NAME QTY ITEM CODE ITEM NAME QTY ITEM CODE ITEM NAME QTY ITEM ...
You can, of course, expand the ranges in the formula:
=SUM(IF(('ITEM RECEIVED'!$D$3:$X$100000=B2)*('ITEM RECEIVED'!$E$3:$Y$100000=C2),'ITEM RECEIVED'!$F$3:$Z$100000))
You can then enter data in up to 100000 rows in the ITEM RECEIVED sheet and the formula will still work.
Remark: you must use Ctrl+Shift+Enter when you enter or edit the formula, not before you enter the formula, as you seem to suggest when you write "This is the PICTURE after Using Ctrl+Shift+Enter before applying FORMULA in Cell 'O10'"
Using a macro:
Sub Transform()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim m As Long
Dim c As Long
Dim n As Long
Dim dc1 As Object
Dim dc2 As Object
Dim itm As String
Application.ScreenUpdating = False
Set ws = ActiveSheet
m = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
n = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set dc1 = CreateObject("Scripting.Dictionary")
Set dc2 = CreateObject("Scripting.Dictionary")
For c = 1 To n Step 3
For s = 2 To m
itm = ws.Cells(s, c).Value
If itm <> "" Then
If dc1.Exists(itm) Then
dc2(itm) = dc2(itm) + ws.Cells(s, c + 2).Value
Else
dc1.Add Key:=itm, Item:=ws.Cells(s, c + 1).Value
dc2.Add Key:=itm, Item:=ws.Cells(s, c + 2).Value
End If
End If
Next s
Next c
m = dc1.Count
Set wt = Worksheets.Add(After:=ws)
wt.Range("A1:C1").Value = Array("ITEM CODE", "ITEM NAME", "QTY")
wt.Range("A2").Resize(m) = Application.Transpose(dc1.Keys)
wt.Range("B2").Resize(m) = Application.Transpose(dc1.Items)
wt.Range("C2").Resize(m) = Application.Transpose(dc2.Items)
Application.ScreenUpdating = True
End Sub