Jun 25 2022 03:48 AM
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
Jun 25 2022 04:21 AM
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
Jun 25 2022 04:45 AM
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) )
Jun 25 2022 05:17 AM
=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.
Jun 26 2022 11:48 PM
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
Jun 27 2022 03:12 AM
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.
Jun 28 2022 12:08 AM
Respected Hans Vogelaar Sir, Thanking YOU for help. I have used your code but result shows #VALUE!
My EXCEL Version is 2016
| |||||||||||||||||||||||||||||||
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
Jun 28 2022 03:28 AM
Did you confirm the formula with Ctrl+Shift+Enter? That is essential if you have Excel 2016.
Jun 28 2022 05:07 AM
Jun 28 2022 05:48 AM - edited Jun 28 2022 05:49 AM
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?
Jun 28 2022 10:53 PM
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. No | Item Code | Item Name | |||||||||||||
AB1 | ABCD | {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} | |||||||||||||
AB2 | KLOS | {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} | |||||||||||||
AS2 | EFGH | ||||||||||||||
AS5 | MNOP | ||||||||||||||
AK3 | VXYZ | {=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))} | |||||||||||||
AS1 | SKDH | {=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
Jun 29 2022 12:05 AM
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
Jun 29 2022 02:18 AM
Did you look at the sample workbook attached to my previous reply?
Jun 29 2022 02:52 AM
Respected @Hans Vogelaar Sir,
of course Sir, and the picture as below after using Formula as per attachment
Sr. No | Item Code | Item Name | ||||||||||
AB1 | ABCD | {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} | ||||||||||
AB2 | KLOS | {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} | ||||||||||
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))} | ||||||||||
YOURS Sincerely
tarun@1964
Jun 30 2022 02:10 AM
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. No | Item Code | Item Name | Item Qty |
AB1 | ABCD | 22 | |
AB2 | KLOS | 58 | |
AS2 | EFGH | 68 | |
AS5 | MNOP | 43 | |
AK3 | VXYZ | 37 | |
AS1 | SKDH | 33 |
after using formula in my work book
PIC 2 ITEM LIST Sheet (my Workbook)
Sr. No | Item Code | Item Name | ||
AB1 | ABCD | {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} | ||
AB2 | KLOS | {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} | ||
AS2 | EFGH | |||
AS5 | MNOP | |||
AK3 | VXYZ | |||
AS1 | SKDH |
Hope YOUR Kind Assist will rest assured me
YOURS Sincerely
tarun@1964
Jun 30 2022 02:33 AM
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.
Jun 30 2022 05:12 AM
Jun 30 2022 05:17 AM
You didn't even bother to enter the formulas? <sigh>
Jun 30 2022 07:20 AM
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
Jun 30 2022 08:16 AM
SolutionYou 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'"
Jun 30 2022 08:16 AM
SolutionYou 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'"