Forum Discussion

TARUNKANTI1964

Copper Contributor

Jun 25, 2022

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 CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

AB1

ABCD

AS2

EFGH

AK3

VXYZ

AS1

SKDH

AB2

KLOS

AB2

KLOS

AS5

MNOP

AS2

EFGH

AS1

SKDH

AS2

EFGH

AB1

ABCD

AK3

VXYZ

AS5

MNOP

AB2

KLOS

As per above stated PIC-1.it is found that some ITEM CODE, ITEM NAME with their QTY are sorted as per ROW Wise.

I want an excel formula for as stated below PIC-2. as per new DATA insert in PIC-1 in to next rows same ITEM CODE &

ITEM NAME will not be repeat in PIC-2, except new ITEM CODE & ITEM NAME, But the QTY will continue to be added as per

ITEM CODE & ITEM NAME Wise

PIC - 2

ITEM CODE	ITEM NAME	QTY
AB1	ABCD	22
AB2	KLOS	58
AS2	EFGH	68
AS5	MNOP	43
AK3	VXYZ	37
AS1	SKDH	33

Hope i will get YOUR kind assistance in this regards

Yours Sincerely

TARUNKANTI@1964

excel

HansVogelaar
Jun 30, 2022
TARUNKANTI1964

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'"
Book1(2).xlsm31 KB

23 Replies

OliverScheurich

Gold Contributor

Jun 25, 2022

TARUNKANTI1964

=SUM(IF(B9&C9=$B$3:$P$5&$C$3:$Q$5,$D$3:$R$5))

A variant of SergeiBaklan solution could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

TARUNKANTI1964

Copper Contributor

Jun 27, 2022

OliverScheurich

I want to thank all of YOU Three Respected Mr. Hans Vogelaar Sir, Mr. Sergei Balkan Sir, and Mr. Quadruple_ Pawn Sir, It’s amazing assist for me, I know, time is of the essence for YOU, I mean YOU have to spend a lot of YOUR Valuable time to solve many Problems. But nevertheless all of YOUR Efforts and YOUR Precious time have failed in this matter due to my worthless approach. In my earlier POST posted on 25-06-2022 I have furnished as PIC – 1 and PIC - 2. but that will be as Sheet – 1 and Sheet – 2 instead of PIC – 1 and PIC - 2. For this reason, I am especially Sorry to all of YOU. Even after all this events if all of YOU decided to solve my PROBLEM than I will be forever INDEBTED to all of YOU.

PIC of Sheet1

ITEM CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

ITEM CODE

ITEM NAME

QTY

AB1

ABCD

AS2

EFGH

AK3

VXYZ

AS1

SKDH

AB2

KLOS

AS2

EFGH

AB2

KLOS

AS5

MNOP

AS2

EFGH

AS1

SKDH

AS2

EFGH

AB1

ABCD

AS2

EFGH

AB1

ABCD

AK3

VXYZ

AS5

MNOP

AB2

KLOS

As per above stated PIC-1.it is found that some ITEM CODE, ITEM NAME with their QTY are sorted as per ROW Wise. I want an excel formula (only EXCEL FOEMULA, as because DATA of Column A and Column B in Sheet 2 already populated from Sheet1 by using VBA Code, Courtesy of Respected Mr. Hans Vogelaar Sir). For only Item QTY in Column C of Sheet2 from Sheet1 as stated below PIC-2. as per new DATA insert in PIC-1 of Sheet1 in to next rows same ITEM CODE & ITEM NAME will not be Populate in Sheet2, if found 'ITEM CODE ' and 'ITEM NAME ' are same, except new ITEM CODE & ITEM NAME, But the QTY will continue to be added as per ITEM CODE & ITEM NAME Wise.

PIC of Sheet2

Item Code	Item Name	Item Qty
AB1	ABCD
AB2	KLOS
AS2	EFGH
AS5	MNOP
AK3	VXYZ
AS1	SKDH

YOURs Sincerely

Tarun@1964

HansVogelaar
MVP
Jun 27, 2022
TARUNKANTI1964

In C2 of Sheet2:

=SUM(IF((Sheet1!$A$2:$M$4=A2)*(Sheet1!$B$2:$N$4=B2),Sheet1!$C$2:$O$4))

Adjust the ranges if the data on Sheet1 extend below row 4.

If you don't have Microsoft 365 or Office 2021, confirm with Ctrl+Shift+Enter.

Then fill down.

SergeiBaklan
MVP
Jun 25, 2022
TARUNKANTI1964

Depends on your Excel version / platform, as variant that could be

=SUMPRODUCT( ($B$3:$P$5=$B9)*($C$3:$Q$5=$C9)*IF( ISNUMBER($D$3:$R$5), $D$3:$R$5, 0) )
sum qty.xlsx13 KB

HansVogelaar

MVP

Jun 25, 2022

TARUNKANTI1964

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