SOLVED

EXCEL FORMULA

Brass Contributor

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

5

  AS2

  EFGH

14

  AK3

  VXYZ

14

  AS1

  SKDH

19

  AB2

  KLOS

17

  AB2

  KLOS

18

  AS5

  MNOP

26

  AS2

  EFGH

37

  AS1

  SKDH

14

   

  AS2

  EFGH

17

  AB1

  ABCD

17

  AK3

  VXYZ

23

  AS5

  MNOP

17

  AB2

  KLOS

23

 

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

23 Replies

@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

@TARUNKANTI1964 

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

image.png

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

@TARUNKANTI1964 

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

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

item code name qty.JPG   

@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

5

AS2

EFGH

14

AK3

VXYZ

14

AS1

SKDH

19

AB2

KLOS

17

AS2

EFGH

42

AB2

KLOS

18

AS5

MNOP

26

AS2

EFGH

37

AS1

SKDH

14

AS2

EFGH

15

AB1

ABCD

44

AS2

EFGH

17

AB1

ABCD

17

AK3

VXYZ

23

AS5

MNOP

17

AB2

KLOS

23

   
                  
                  

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

@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.

@Hans Vogelaar 

 

Respected Hans Vogelaar Sir, Thanking YOU for help. I have used your code but result shows #VALUE!

My EXCEL Version is 2016

 

             TARUNKANTI1964_0-1656399696496.png

 

                  

DATE

INVOICE NO

SUPPLIER NAME

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

ITEM CODE

ITEM NAME

QTY

UNIT PRICE

SUB TOTAL

TAX

VALUE INCL TAX

DISC

INVOICE VALUE

01-06-2022

123

SUDAR

AB1

ABCD

5

11

AS2

EFGH

14

 

AK3

VXYZ

14

32

AS1

SKDH

19

 

AB2

KLOS

17

         

503

01-01-2020

103

BAGUIATI

AB2

KLOS

18

15

AS5

MNOP

26

11

AS2

EFGH

37

 

AS1

SKDH

14

23

            

609

01-01-1995

147

KOLKATA

AS2

EFGH

17

55

AB1

ABCD

17

 

AK3

VXYZ

23

32

AS5

MNOP

17

 

AB2

KLOS

23

15

        

1280

 

 

Sr. No

Item Code

Item Name

Item Qty

 

AB1

ABCD

#VALUE!

                      

AB2

KLOS

#VALUE!

 

AS2

EFGH

 
 

AS5

MNOP

 
 

AK3

VXYZ

 
 

AS1

SKDH

 
    

                                                             =SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))

Please Help in this regards

 

Thanking YOU

YOURS Sincerely

 

@TARUNKANTI1964 

Did you confirm the formula with Ctrl+Shift+Enter? That is essential if you have Excel 2016.

Respected Hans Vogelaar Sir,
Formula as below as i used
=SUM(IF((ITEM RECEIVED!$E$3:$X$5=B3)*(ITEM RECEIVED!$F$3:$Y$5=C3),ITEM RECEIVED!$G$3:$Z$5))
Which i have in D3 of Sheet2
Eagerly waiting With for YOUR kind help

YOURS Sincerely
TARUN@1964

@TARUNKANTI1964 

As you showed in your previous reply, you must enclose the sheet name ITEM RECEIVED in single quotes, since it contains a space.

See the attached workbook. Does the formula work when you open it?

Respected @Hans Vogelaar Sir,

Even though YOU are wasting  YOUR precious time for doing me, but in this case too YOUR advice did not work as i wanted. i am very sorry that i could not use YOUR advice. i have use (Ctrl+Shift+Enter) before using formula.

Sr. NoItem CodeItem Name             
 AB1ABCD{=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))}    
 AB2KLOS{=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))}    
 AS2EFGH             
 AS5MNOP             
 AK3VXYZ{=SUM(IF('ITEM LIST'!$B$3&$C$3=('ITEM RECEIVED'!$D$3:$X$5)&('ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:$Z$5))}  
 AS1SKDH{=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))}   
   {=SUMME(WENN(('ITEM LIST'!$B$3='ITEM RECEIVED'!$D$3:$X$5)*('ITEM LIST'!$C$3='ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:4Z$5))}
   {=SUMME(WENN(IF('ITEM LIST'!B3&C3=('ITEM RECEIVED'!$D$3:$X$5)&('ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:$Z$5)))} 
   {=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))}    
                

 

Hope YOUR kind assitance wil help me

 

YOURS Sincerely

tarun@1964

Reaspected @Hans Vogelaar

 

i have  VBA Code as below given by YOU

 

Sub SAVE_DATA()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim m As Long
Dim c As Long
Dim t As Long

Application.ScreenUpdating = False

' Source sheet
Set ws = Worksheets("ITEM RECEIVED")
' Find last used row
m = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

' Copy new items to ITEM LIST sheet
Set wt = Worksheets("ITEM LIST")
' Find last used row in column B on ITEM LIST Sheet
t = wt.Cells(wt.Rows.Count, 2).End(xlUp).Row
' Loop through columns D, H, L, P, T and X on ITEM RECEIVED sheet
For c = 1 To 6
' Loop through the rows
For s = 3 To m
' Item Code filled in?
If ws.Cells(s, 4 * c).Value <> "" Then
' Is it a new one?
If wt.Range("B2:B" & t).Find(What:=ws.Cells(s, 4 * c).Value, LookAt:=xlWhole) Is Nothing Then
' Copy Item Code and Item name to new row
t = t + 1
wt.Cells(t, 2).Resize(1, 2).Value = ws.Cells(s, 4 * c).Resize(1, 2).Value
End If
End If
Next s
Next c

' Copy transactions to SUPPLIER LIST sheet
Set wt = Worksheets("SUPPLIER LIST")
' Find last used row in column C on SUPPLIER LIST Sheet
t = wt.Cells(wt.Rows.Count, 3).End(xlUp).Row
' Loop through rows on ITEM RECEIVED sheet
For s = 3 To m
' New invoice number?
If wt.Range("C2:C" & t).Find(What:=ws.Cells(s, 2).Value, LookAt:=xlWhole) Is Nothing Then
' Copy data
t = t + 1
' Date, invoice no, supplier name
wt.Cells(t, 2).Resize(1, 3).Value = ws.Cells(s, 1).Resize(1, 3).Value
' Item name and quantity
For c = 1 To 6
wt.Cells(t, 2 * c + 3).Resize(1, 2).Value = ws.Cells(s, 4 * c + 1).Resize(1, 2).Value
Next c
wt.Cells(t, 17).Value = ws.Cells(s, 32).Value
End If
Next s

Application.ScreenUpdating = True

End Sub

 

@TARUNKANTI1964 

Did you look at the sample workbook attached to my previous reply?

Respected @Hans Vogelaar Sir,

of course Sir, and the picture as below after using Formula as per attachment

 

Sr. NoItem CodeItem Name          
 AB1ABCD{=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} 
 AB2KLOS{=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} 
 AS2EFGH          
 AS5MNOP          
 AK3VXYZ          
 AS1SKDH{=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))}
             
             

 

 

YOURS Sincerely

tarun@1964

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Respected @Hans Vogelaar Sir,

in reply to YOUR previous enquiry i used the FORMULA in cell 'D3' of 'ITEM LIST' Sheet in my Workbook as described in attached sample Workbook submitted by YOU on 28-06-2022 as PIC 1, but it shows no neumerical result as PIC 2.

 

PIC 1  Sheet2 ( Sample Workbook)

 

Sr. NoItem CodeItem NameItem Qty
 AB1ABCD22
                      AB2KLOS58
 AS2EFGH68
 AS5MNOP43
 AK3VXYZ37
 AS1SKDH33

 

after using formula in my work book

PIC 2  ITEM LIST Sheet (my Workbook)

     
Sr. NoItem CodeItem Name  
 AB1ABCD{=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} 
 AB2KLOS{=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} 
 AS2EFGH  
 AS5MNOP  
 AK3VXYZ  
 AS1SKDH  

Hope YOUR Kind Assist will rest assured me

YOURS Sincerely

tarun@1964

@TARUNKANTI1964 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

Respected @Hans Vogelaar 

 

Here i am attaching the Workbook

 

YOURS Sincerely

tarun@1964

@TARUNKANTI1964 

You didn't even bother to enter the formulas? <sigh>

Respected Mr. Hans Vogelaar Sir, i understand that YOU are extermly upset having to give the same answer over and over again on the same subject and i apologize to YOU for this.in YOUR early reply to me on 28-06-2022 "Did you confirm the formula with Ctrl+Shift+Enter? That is essential if you have Excel 2016".
in this regard i would like to inform YOU that of course i have followed your instruction before applying the FORMULA , which given by YOU. now i am sure of one thing that there is some problem in my EXCEL Version.
because to day i have downloaded YOUR attachment and it's very fine. but in ITEM RECEIVED Sheet when i will increase Range of ROW DATA,then i should to rewrite the FORMULA.Respected Sir, don't take offense,i want say one thing that i already know the FORMULA "{=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))}"and i tried it many times and could manage/materialised the matter in any way before posting in 'TECH COMMUNITY' to thinking that there is some thing wrong with my method to fixes it. again one thing in my Workbook which i uploaded today(30-06-2022) there in Column 'D' of Sheet 'AVIL ITEM' above stated FORMULA will be used. please see what happend in attached Workbook.

YOURS Sincerely
tarun@1964

best response confirmed by TARUNKANTI1964 (Brass Contributor)
Solution

@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'"

1 best response

Accepted Solutions
best response confirmed by TARUNKANTI1964 (Brass Contributor)
Solution

@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'"

View solution in original post